Exporting access data to .txt, then importing a .txt back to acces

[Guest]

I am newbie to Access 2003 and I need a automated process to export a table
to a .txt file and then check to see when a new .txt file is available to
import to access. I would like the automated process to check for a .txt file
in access about every minute or so. The exported/imported .txt files will be
kept in the same folder but with different prefix names.

Thanks for any help in advance

GWG

 

[John Nurick]

You can use a form's OnTimer event procedure to run code at regular
intervals (providing that Access is running and the form is open).

The Dir() function lets you identify files that match a name or
wildcard, and FileDateTime() gives you a file's timestamp so you can
tell whether it's new (if you need to).

DoCmd.TransferText handles the importing and exporting.

However: if what you're planning is a system for unattended processing
of text files, Access may not be the right tool.

 

[Guest]

Thank you John, that is eactly what I was looking for. I agree with you that
normally I would not use Access for this procedure, but they already have an
Access product up and running and wanted a way to see if new data (.txt file)
is available and import/export it immediatly. In reality, this should only
happen about 20 time a day.

Thanks again.
GWG

 

[John Nurick]

Two small comments:

1) The Jet database engine (i.e. the one that manages data in .mdb
files) doesn't automatically recover space used by deleted objects, so
if you're routinely creating and deleting temporary tables the .mdb file
will get steadily larger and will periodically need to be compacted,
which means taking the database offline. Sometimes it's best to have
your code create a temporary mdb file in which to create the temp
tables.

2) It's possible to do a lot of things with Jet databases from Windows
scripts without involving Access (or any other Office application). In
the long run you may prefer to set up a scheduled event on a server to
run a script in your favourite OLE-aware language that checks the
watched folder and then uses the DAO library to execute queries that do
the importing and exporting.