VBA to Import data into Access 03

**pdauction** · 10-14-2010, 07:08 AM

I currently have a process where I import around 10 .txt files into a database each week replacing exisitng ones. Each has it's own specification and 2 have primary keys. I decided to automatically do this using a macro. Code I am using is below...

DoCmd.DeleteObject acTable, "Agency"
DoCmd.TransferText acImportDelim, "Agency Import Specification", _
"Agency", "U:\AS\MIdata\Database\agency.txt"

DoCmd.OpenQuery ("Agency_Prim_Key")
The SQL for the above is below
ALTER TABLE Agency
ADD PRIMARY KEY (uan);

Problems Encountered...

When doing this process manually I just import and it over-writes the existing table but when I use the VBA method it seems to append for some reason. I have therefore added code to delete the original.
The second and main problem is that after importing it comes up with creating and changing indexes which not only takes time but is not something I do not want to happen and doesn't happen when doing manually presumably because I am choosing a primary key (or not in other circumstances) in the import wizard.

Does anyone know a way to import without Access adding its own Indexes (I am not sure the knock on effect of indexes on a table if I am ignorantly honest) but I know it adds time to the macro. Similarlly does anyone know why it appends rather than replacing? As always your help is appreciated.
Thanks

**split_atom18** · 10-20-2010, 10:59 AM

At this point I am gonna ask the WHY? question.

What are your advantages of importing the text file weekly vs linking it with ADO activeconnection?

What are you doing with the data once it is in the database?

These are some questions I hope will help me give you a direction to head.

Thanks,

Dan

**pdauction** · 10-21-2010, 10:53 AM

Thanks for responding,

The text files are downloaded each week after running a SAS program. From there I transfer them into a Database and from there a multitude of differrent queries are run to produce reports (usually through excel) all based on the latest tables imported.

I currently import the tables as I was unaware of any other way to effectively do this (I didn't use "Link Tables") for a number of reasons.

I was not aware of using ADO connections and after using the relevant help files and Google I'm still not 100% of the benefits of these or indeed the exact coding I would need to use this connection type - Access is not my forte.

Cheers
Paul

**split_atom18** · 10-21-2010, 03:07 PM

So are the tables you download and import new each time like Sales?

A bit about Indexing: Click here

If this is going to be a continually growing database as data is added once a week. It would be beneficial to take the little extra time to setup indexing.

For me personally if I had something like this:
I would use a Linked File and run my downloads(ie update the linked file)
Write VBA to copy the Linked File into a Table(with Indexes already setup) with an extra field for the date I imported it.
Create my reports based on that table. Have a button that I would use annually to clean up that file. Ie. Create a history table for each year - this would allow me to re-create any report I had ever made in a few clicks.

Hope this helps,

Dan