auto seceduling import to Access table (1 Viewer)

[crispino]

As needed, a text file will be FTP'd to an NT Server in our computer center. This file needs to be imported into an MS/Access table. Currenly, importing the file is a manual process.

Is there a way to use a Macro (or some other method) to automate importing the delimited text file into a table? Can this be a scheduled procedure, somehow trigered to run whenever the text file is present. ( some kind of timer initiated function that would run on a regular basis to check for the presence of the text file, for example).

Thanks

 

[jatfill]

The easiest way to do this would be a function (code)
IF the file name will always be the same, you can write it like this:

First you need to manually import the delimited file once... once you fixed all of the fields/data types, click on Advanced and save the inport specs as IMPORT_FILE or whatever you wish.

To automatically import the file, put in something like the following:
*****************************************
Private Sub import_file

On Error GoTo Err_import_file

Dim stDocName As String

stDocName = "\\servername\path\filename.txt"

DoCmd.TransferText acImportDelim, "IMPORT_SPECIFICATION", "TABLENAME", stDocName, False, ""

import_file_exit:
Exit Sub

Err_import_file:
MsgBox Err.Description
Resume import_file_exit

End Sub
********************************************

the "False" tag at the end tells Access whether or not the text file has field names in the first row. If your file does, change that part to True. Otherwise, simply change the tablename, file path, and import specification name to match your needs & this should work the way you need it to.

As far as automating goes, you could run this from a macro, name the macro "autoexec" and it will run every time someone opens the database...

 

[The_Doc_Man]

It is possible to run MS Access in command line mode from a batch file, which can be run from the Windows scheduler.

Look in the help files about command lines. You can define a macro to do what you want. The last step in the macro has to be QUIT (meaning quit Access).

Then your macro could be as simple as a runcode action of your function followed by a quit action.

I think jatfill is right, but you could also do something in your bat file to run access, then rename the file. you can also do an ifexists in the file, I think.

This bear is skinnable, you just have to be patient because it operates on four levels:

Windows Scheduler triggers....

.AT batch file which runs...

MS Access command to activate a macro which...

runs function code doing what you really wanted to do in the first place.