| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 28 April 2007 : 11:32:45
|
I could swear I have done this in the past but I am now faced with a situation where I had to convert a SQL database to MS Access and failed to maintain the identities so that the Access tables would have their autonumbers preserved. The problem I am presently faced with is that I don't have the SQL database immediately available to do the export again so is there any way whatsoever to convert a number column in a table that already has data to an autonumber? I've tried some ALTER queries but failed every time.
I think the reason I thought I was able to do this before was because I created another column, made it into an autonumber and then removed the old column, but in this case I can not do that because some rows were deleted. |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 April 2007 : 18:58:00
|
What you could probably try would be a Access to Access transfer, using DTS from SQL Server 2000. If you do this, DTS won't allow you to enable identity insert, until you choose the option to specify the transformation yourself and then return to the screen where you can specify the identity insert, which should then be accessible. This option is not available with SQL Server 2005 Management Studio.
Another option, that you'd need to use for each table, would be to create a copy of the tables, and delete all the records from the copies. Change each of the desired columns to Autonumber and then, for each of the tables, create an append query, to append records to the table copies. Each of update queries should have one of the original tables as the source of the records and the respective table copy as the destination table. Then just execute the update queries. This will copy all records from the original tables, while preserving the values for the identity fiels! |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 28 April 2007 : 19:33:40
|
The append query would probably not work because of the relationships between the tables. The new column would renumber the parent id's which would not match the child id's in the other table.
I will see what I can do with a DTS. I can't believe I made such a newbie move. I just haven't had the need for migrating SQL tables to Access in a long time and I remember the process being much easier in the past. |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 29 April 2007 : 01:00:34
|
quote:
Originally posted by ruirib
The append query preserves all your existing IDs, even the primary key id's. I actually tested this before posting and it worked like a charm.
I didn't know that... I'll give that a whirl. I only have 4 tables I need to do this with. |
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 29 April 2007 : 11:18:17
|
Worked like a champ, thanks again. Very simple yet I do not remember doing it this way last time. Probably because I exported the data correctly in the first place!  |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 29 April 2007 : 11:23:32
|
Glad all went well  .
Yep, it helps when the export is correctly done... but I guess you'd have it difficult to do it with DTS for the Import / export tools Server Server 2005, cause the identity insert choice is no longer possible. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
| |
Topic |
|
Topic Locked
