MSAccess to MSAccess Import Issue. Can't import Autonumber tables

[rockinhorse]

I need to import data from another MSAccess DB using VBA and ADO. The trouble I am having is keeping the autonumber ID field from erroring when trying to bring the same thing from the external db. Any ideas? Thanks

 

[jadams0173]

ADO and autonumbers don't get along very well. Maybe try DAO.

 

[JerryKlmns]

rockinhorse,

You cann 't touch an autonumber ID field. Even when adding a new record programmaticaly, you don't define the value for that field.

So, use the same table structure except for the field which is an autonumber ID. That field of the table to receive data should be numeric (Long). If you need an autonumber ID use a new one.

But sinc you are to use VBA you could import the whole table from the other database!

 

[rockinhorse]

Thanks Jerry. I am trying to import data from a similiar database and just append the records that I don't already have. The problem is the freinds database refers to records using the same autonumber index field, but the data could be totally different. The database was designed by my friend who did not know much about dbs and released a product using that design which has some serious flaws. I have written code to do field comparisons before importing, then reassign all the related tables old index values to the rew record index values. I guess there is no easy way. Thanks anyway.

 

[TheAceMan1]

How are ya rockinhorse . . .

If you don't mind [blue]newly assigned autonumbers[/blue] appended to the data, [blue]try an append query![/blue] You append all desired fields except the autonumber. The SQL would look something like:

[blue]INSERT INTO YourTableName (FieldName1, FieldName2)
SELECT SourceFieldName1, SourceFieldName2
FROM SourceTableName IN 'Drv:\Path\FileName';[/blue]

Just be sure to match the fields 1 to 1 from left to right:
[blue][tt]FieldName1, FieldName2, FieldName3, FieldName4
SourceFieldName1, SourceFieldName2, SourceFieldName3, SourceFieldName4[/tt][/blue]

I've used this quite a few times in the past and it saves writing all the code.

See Ya! . . . . . .

 

[rockinhorse]

Thanks ACEMAN...however I still have to retrieve linked records from associated tables by the old ID and then insert those into the target DB with the new corresponding ID. There is no shortcut...it is still code intensive. Thanks anyway.

 

[rubbernilly]

Eh, you could probably do it with...

1 modification to your main table
1 modification to your main import table
and
2 action queries

Add a field (temporarily) to your Main table and call it "OldID." Then, in the main import table (the table housing the records you want to import into this main table), change the autonumber field to be a Number field of the Long type, and name it "OldID." Move it to the end of the field list to match the position of the field you added to your main table.

Your first action query would append these records from the Main Import table to the Main table. The records created would now have both the new AutoNumber ID field but also the data from the old field.

Your second action query would be to update the tables related to the Main Import table... joining where they have the foreign key of the Main Import table's Autonumber to the Main table's "OldID" field. Update the foreign key to be the new autonumber value associated with the record and... wha-la. You have kept your data solvent.

Hope this is clear enough.

 

[TheAceMan1]

Roger That rockinhorse . . .

[blue]I still have to retrieve [purple]linked records[/purple] from associated tables by the old ID and then insert those into the target DB with the new corresponding ID.[/blue]

To make things clear for the purpose of this thread:

Are you importing form an unlinked DB, linked DB thru tables, or both?

Either way, and append query (maybe two or more), with the approriate criteria could do it all! . . .

Your thoughts!

See Ya! . . . . . .

 

[rockinhorse]

The DBs can't be linked. The code does the association and replication. The DBs are different versions of the same things. Cheap client won't upgrade. It will cost him more to have me do it this way...but oh well.

 

[TheAceMan1]

rockinhorse . . .

Ok . . . are these DB's spread across the same network or what?

See Ya! . . . . . .

 

[rockinhorse]

These two dbs are local to my computer and are nearly identical, which is where the problem arises. Out of nearly 1 million records, there may be 50,000 that contain variances. We are dealing with animal breeders, one trying to merge data from another to form a central DB as a repository. It's an odd task at best.

 

[TheAceMan1]

[blue]I am trying to import data from a similiar database and just append the records that I don't already have.[/blue]

So . . . how about an [blue]append query[/blue] that gets its source data from an [blue]UnMatched Query[/blue] (setup properly, the unmatched would only return those records you don't have)?

See Ya! . . . . . .

 

[rockinhorse]

That's what I have done. I built two tables, one for common table names (with a binary for whether or not it is importable) and the other is a linked table for fields (with a compare binary for checking field values) and a transport field (for whether or not the data is to be transferred). All your suggestions are very well thought out AceMan. I was trying to avoid the amount of code to be written but alas it is done and that's all the client cares about. It just cost them a fortune. We'll see if they pay the bill.

Thanks again AceMan.