loading data from txt files [Archive]

The_Doc_Man

01-25-2005, 07:40 AM

How automated is automated?

I.e. push one button on a form? Do it as long as the computer is on at a specific time of day? Do it when someone first logs in? Do it when a specific person first logs in?

In all cases:

Start by building a query to erase each table affected.

Next, prepare the import operation (if possible) by doing the import manually once so you can save the import specification - a type of import format, if you've never used one - as a named object. If you have more than one import, save more than one specification. One for each file. TransferText will allow you to select an import specification as one of its arguments.

Prepare a table with the proper data types. Do the import to APPEND to an existing table rather than to MAKE a new table. (That's the only way to guarantee that the fields are always of the right type.) The Erase queries will wipe out the records but not the table design, so this should be safe.

Now, the next step depends on the answer to "how automated..."

The SIMPLEST way is to build a macro that calls each Erase query or each TransferText operation in the proper sequence. Then you can just run the macro when the spirit moves you. HOWEVER, macros have little or no error handling capability.

If you want this sequence to run if your system happens to be up at a given time of day, keep the macro as-is. Look up "command line" options in the Help files for the way to make a command line activate your database and run a specific macro. Create an ICON that launches your DB. Edit the icon's command line to include the macro option. Don't quote me and don't trust me on this, but I think it is /X and the name of the macro.

If you do the thing through an ICON, the macro has to end with a step that exits the application. Then put the icon in your task scheduler to run at the desired frequency.

But maybe you wanted this to run when someone logs in or perhaps at the touch of a button on a particular form. So the next step is to build the macro anyway, but then convert it to VBA. Copy that code to run under the button you create on your selected form. You can edit the code as you need to trap errors at any stage and issue appropriate reports or trigger various remediation steps. You would definitely prefer to not leave things as a macro in this case.

Did you want this to run when you/someone else/anyone opens the DB? Put the VBA code as converted from a macro so that it runs as part of the OnLoad event of a startup form. That will trigger the code when the DB starts. Did you want it to be selective as to which person was logging on? Put the test for that person in your OnLoad event for the startup form and don't call the sequence unless it is the right person.

Did you want it for EVERYONE who opens the DB? Beware of multiple people running it at the same time if everyone starts their work day at the same time. I would not personally advise this option.