TransferText VBA Import Issue (1 Viewer)

[llkhoutx]

I'm trying to a VBA docmd.TransferText statement (properly formatted) to import and append "csv" data to an existing table, e.g.

DoCmd.TransferText acImportDelim, "Import Specification", "tbImportedData", strPathAndFile, vbNo

.

The VBA code creates a new table with an identical name, while a manual import properly appends the existing table.

Can VBA be used to append an existing table with a TransferText statement?

What am I missing.

 

[vbaInet]

I believe your import spec is causing the import to create a new table.

Create a new import spec that will append to the specified table

OR

Remove the import spec argument in the code and indicate the table you would like to import into

 

[llkhoutx]

Thanks! I'll give it a try tomorrow and advise.

 

[llkhoutx]

I deleted the specification via the import wizard and in the line of code, then repaired and compacted, then imported manually, then programmatically with the new line of code, i.e.

DoCmd.TransferText acImportDelim, , "tbImportedData", strPathAndFile, vbNo

Data was properly appended when manually imported.

With the new line of code, a new table with the same name is created with the programmatically imported data, plus I get import parsing errors which I don't get when importing manually.

I must import programmatically because I have hundreds of import tables, the names of which are in a another (descriptor) table.

By the way, I'm using A2003 at work. I'll try it in A2007 and A2010 at home tonight.

Could this be an undisclosed feature of A2003?

Any further help would be of assistance.

 

[vbaInet]

With the new line of code, a new table with the same name is created with the programmatically imported data, plus I get import parsing errors which I don't get when importing manually.

This explains why you can't import and I would imagine the error you're getting has something to do with Key Violations. If that's the case then your primary keys, referential integrity and field properties (for validation) is what's causing the error. E.g. you can't import into the table if the ID already exist. Plus it's impossible for Access to create two tables with the same name.

Could this be an undisclosed feature of A2003?

I doubt that's the case.

 

[llkhoutx]

Soution: Removing AutoNumber primary key in target table (being imported to). "csv" data is then imported correctly.

Caveat: Importing data into a extisting table having a primary key, does cause Access 2003 to create a new table with the same name as the tartget table. Hence, 2 Access 2003 tables can exist with the same name!

 

[vbaInet]

Glad to know you got it working!

I hardly use the wizard and it's been too long since I touched 2003 so you're probably right about that. However, are you sure it doesn't append a "1" to the name?

 

[llkhoutx]

Table with the original target name is created. No "1" suffix. Both tables open and display records.

I'm appending using VBA and only used Wizard to check initial import. Wizard generage import errors.

 

[vbaInet]

Are you turning off warnings? I'm just wandering why Access is creating a table with the same name. Could it be something wrong with your copy?

 

[llkhoutx]

I'm using a Fortune 25 corporation PC. Warnings are and have been turned off. MS is known for undisclosed features in its software. I have no idea why it happened. I'm moving on and cannot dwell on the issue.

Thank you for your assistance. It helped me cure the problem.

 

[vbaInet]

Not a very good practice to permanently turn off warnings. What most people do is use vba code to turn it on and off. An example would be:

Docmd.SetWarnings [COLOR=Blue][B]False[/B][/COLOR]
Docmd.RunSQL "INSERT INTO ..."
Docmd.SetWarnings [COLOR=Blue][B]True[/B][/COLOR]

Anyway, that's fyi.

 

[gemma-the-husky]

generally, I think this is the case.

if you import to a new table, it comes in without indexes etc etc. - although this may be OK in some cases

so i tend to have a pre-existing table maybe with extra fields, to recieve the input. I clear this out first

del * from temptable

then import the fressh data.

I can now examine and test this data, and if it passes my validation tests, only then do i run a series of queries to bring the new data into the dbs proper.

(all in code i mean)