September 22, 2009 at 11:52 am
I am currently working with an Access database with multiple tables. Relationships between the tables are not defined. SQL is being used to append data to the tables from entries on the forms.
Many of these tables utilize the AUTONUM data type as their primary key. These AUTONUM values are then used as foreign keys in other tables to associate data between the tables.
The data needing to be imported is in Excel and requires some serious scrubbing prior to import. What I want to do is specify the values for the fields in Excel prior to import. Including fields which will be AUTONUM fields in Access.
I need to specify these values to ensure that when used as foreign keys on the other tables, the correct record is being associated on the table with the AUTONUM field.
My best answer to-date is to import a table, make note of which AUTONUM field is used for each record and then copy those into Excel as the foreign key prior to import of the related table. Unfortunately I have several thousand records on each table and that process would take just as long as manual data entry through the database front end forms.
Is there a way around this?
Any help would be greatly appreciated.
September 22, 2009 at 1:24 pm
Christopher, first off, this is a SQL Server site, not Access. I would suggest you try http://www.accessmonster.com. Now, the first problem I see is this....
christopherk (9/22/2009)
What I want to do is specify the values for the fields in Excel prior to import. Including fields which will be AUTONUM fields in Access.
I can't speak to Access 2007, but for 2003 and prior, you can't insert a value into an autonumber field. And if I remember correctly, Access will not even let you change a number field to autonumber once there are records in it. Like I said, accessmonster.com would be a good starting place for you. Good luck.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply