Hi Mark,
There are several ways. If it's a matter of running a simple append
query to import the data I'd write a script using the DAO library to
create and execute the query without using Access itself; this avoids
all the potential complications of using Access for unattended server
operation which it's not designed for (and also saves the cost and
trouble of installing Access on the server). Here's an example procedure
which can be used in a VBScript:
Sub TextImport(MDBFile, TableName, FolderPath, _
TextFileName, Extension)
'VBS to import data from a CSV file into a table in an MDB file
'Folderpath must end with \ (e.g. "C:\Folder\Sub folder\")
'TextFileName is just the name (e.g. "MyFile")
'Extension must not include the . (e.g. "txt")
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile)
strSQL = "INSERT INTO " & TableName _
& " SELECT * FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL, 128 'dbFailOnError
oDB.Close
End Sub
Having got the VBScript working in a test setup, install it on the
server and set up a Scheduled task to run it whenever needed. If you
want to log the the operation of the script, you can get the number of
records appended by using
oDB.RecordsAffected
after the .Execute and send it to the server's event log or somewhere.
