Re: Automatic Scheduled Import

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.


On Tue, 12 Apr 2005 09:31:01 -0700, "Mark Senibaldi"
<MarkSenibaldi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Hi,
>I have a database on a shared server. Is there any way so that every day at
>1:00pm the database will automatically run a macro which so happens to import
>data into the database?
>
>Thanks,
>Mark

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MailMerge hangs and crashes with Access on Server
    ... restructuring a complex query. ... discards 99% of them), or on the server side, incurring no network ... the database is locked and Word will not open the data source. ... have kept the Word templates and the Access frontend locally on my machine, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MailMerge hangs and crashes with Access on Server
    ... I have mapped out the path to the final query that is used to get to the ... the database is locked and Word will not open the data source. ... have kept the Word templates and the Access frontend locally on my machine, ... and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: db repair
    ... Server. ... VB Script looks amazingly like VBA, since they're both based upon the same ... See http://www.mvps.org/access/general/gen0041.htm for the VBA code to ... this won't compact the database ...
    (microsoft.public.access.setupconfig)
  • Re: Single-Threading / Performance issues
    ... SQL Server MVP ... > Early this year my database was migrated from a single CPU, ... (when a long select query is running, ... server guy also sez it's not his problem. ...
    (microsoft.public.sqlserver.server)
Loading