Copy Data from Access to SQL Express in Code?

I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine. I can use, compact and copy etc in MS Access without problem.

With the upsize, I get No errors, just does not reliably move ALL data. I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.

========================================

So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.

I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables.

1 questions and one "bug" report need solutions to both.

Question: How can I copy the value from the MS access autonumber field to the identity field. (Some of the autonumber rows have been deleted so not always sequentiall) (I can do this moving data in code between to MS access databases that have autonumber fields)

=========================

I tied an elaborate write around to addNEW and then read the data in the newly created row. Reading the Identity field, I look up that number in the MS access database. Then I copy the ACCESS data to the Newly created table row in SQL Exress and update.

It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value. Makes it impossible to make sure all of my Access rows get copied. Tried transactions but not supported with the ADODB configuration, but doubt that would help.

Example data returned in the SQL Express recordset, when I read the Identify field back I get

1,2,3 etc 31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.

The only info I found was that it could be a cursorlocation problem, what do you recommend?

?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?

As you are just loading the data I would set the field in the SQL Express system to just an int then load the data back in. Once complete change the filed back to an identity field and start the numbers off on the next availible number.

|||

I tried that, but it will NOT let me set the identity field. It generates it.

It works with Access but NOT SQL EXPRESS, if I hit the ADDNEW and then set the identify field and update it errors.

|||

Hey TurboDoc,

You should be able to insert data into your Identity field by setting the IDENTITY_INSERT option. Check out SQL BOL for more information on that. I think that will be more straight forward than trying to create blank records in SQL and then matching up the Identity values.

Hope this helps,

Mike