Database Transfer Option

[merciless32]

I am constructing a program that allows a user to append and review records prior to uploading them to the server. The only problem is that the records come to the user under many different filenames. How do I set up the DatabaseTransfer coding to allow the user to choose the specific file to import and append?

 

[ZanyJanie]

First, go to the following web site, copy the
vba code, and paste it into a module in your database:
http://www.mvps.org/access/api/api0001.htm


Next, create and save an import specification for the file you want to import.

Last of all, create a command button that calls the open dialog box and then does the file transfer. (Below is an example!)

Sub ImportFile()

'Browse and select the file to import

Dim strFilter As String
Dim lngFlags As Long
Dim SelectedFile As String

SelectedFile = ""

strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

SelectedFile = ahtCommonFileOpenSave(InitialDir:="C:\", Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags)

'Now, do the file transfer

DoCmd.TransferText acImportDelim, "Your Import Specification file name goes here", _
"your table name goes here", SelectedFile, True, ""

End Sub


Before actually transferring the file, you'll probably want to use a message box to confirm the user's file selection. (It would make for cleaner programming!)

Hope this helps!

 

[merciless32]

I'll give it a whirl and update you on how it goes.

 

[merciless32]

Ok...you mentioned creating an import specification. What do you mean?

 

[ZanyJanie]

An import specification is used to define the layout and structure of the file you’ll be importing. (e.g. field name, data type, size, etc.)

To create an import specification, act as if you were going to import your file manually.

From the File menu, choose Get External Data, then choose Import. (Or just right-click on a blank spot in your tables window and then choose Import).

Access will prompt you to select the file you want to import. Just choose any file that has the same data format as your users will be importing in the future.

Next, you should see the “Import File Wizard” box. Near the bottom you will see an “ADVANCED” button. Click it!

Next, you should see the Import Specification dialog box. In it you can specify the field name, data type and size, etc. (Note: If you have fields in the original file that you don’t want to import, you can click the Skip box for those fields.)

After you’ve defined the name/type/size for each of the fields, click the Save As button an give your import sepecification a name. (Note: After saving your import specification, you can click the cancel button so that you don’t actually import this file.)

Later, in your code’s docmd.transfertext statement, specify this saved import specification name in order to define the layout & structure of the data you’re importing.

 

[merciless32]

Excellent! Wow...talk about expert advice. I'll let you know how it goes. Thanks for your patience.