Linked Tables

[spock1971]

Help please.

I have a text file containing approx. 45000 rows of data. I've created a linked table to upload the file into access keeping the seperator's in place. The file has date and time recorded as e.g. 20050123 for 23/01/2005 and 91527 for 09:15:27.

Therefore I'm trying to run a macro on the linked table to delete all records from an existing backup table, append new records to it, convert dates, convert times.

However, every time I change the text file to another set of data, the append query falls over. If I go into design view of the query, delete the fields and recreate the fields the query will then run.

It appears as though because the data being linked has changed, the query doesn't recognise the linked table anymore.

Any ideas?

Cheers

D

 

[antomack]

Another way of doing this would be to
1) Import the text file each time to a specific table, set up an import specification to suit and use TransferText
2) Empty the backup table
3) Append from imported table to backup
4) Convert Dates
5) Convert Times

 

[Pat Hartman]

antomack wasn't specific but the import specification will give you the ability to convert the date/time values if you use the advanced button to go to that part of the wizard. Once you have manually imported the data ONCE correctly and saved the import spec, you then automate the import by using the TransferText Method/Action.