Fields/rows in SQL Server

Hey everyone. I have a really weird problem that I haven't been able to understand. After I upsize my DB in Access to SQL Server, I notice rows in one particular table are rearranged. The first column in this table is the ID and was autonumber in Access. It begins from 1 and goes all the way to 2000. But after upsizing, some rows in this column are rearranged. They're still there but not in order anymore. The weird thing is this very first time I upsized, this never happened. Over time though the data just rearranged for some reason. Anyone know why this happens? I thought I could use the sort button up top to sort a certain column but it's always disabled and I can't use it.

Regarding sorting the table, when you open the table (return all rows) on the SQL Server there is a little button that says SQL. when you click on this you get a window above the results with:

"SELECT *
FROM (tablename)"

You can now add

"ORDER BY (column name)" and click the red ! button.

That will sort your resuts while you have the table open. If you need your results permanantly sorted, create a view on that table and sort the view.


And by the way, SQL Server's "autonum" is a numeric datatype such as int, with property "identity" set to "yes" in table design, and set your seed value as well as your increment value. If your table does not have this, the autonum property of your table did not convert from MS Access which might explain why your data is suddenly out of whack.

Leslie, you're right about the identity option. Mine is set to no. So how do I upsize my Access DB with autonumber on? Or can I just open the table in design view in SQL server and change the identity to yes?

I believe you can sort your table on your autonum column and look at the last used number, change the identity to yes and force the seed number to start with the next unused number. Give it a try and let me know. If autonum converted as non-numeric SQL Server will not let you change the data type to number though.

If that doesn't work there are lots of data manipulation techniques you can use ... for instance, you can rename your table as TABLENAME2 and create the table structure for TABLENAME without any data. Set your identity column, then INSERT INTO TABLENAME by selecting from TABLENAME2 but be sure you ORDER BY AUTONUM in the select portion of the statement so the records insert in order. Otherwise it will automatically reassign the primary key as it inserts the random data. Hope that helps!

Your keys should be preserved if you use the upsizing wizard.

If you are using another method to upsize, you can SET IDENTITY INSERT ON to allow inserts into an identity column.

Doug, how do you preserve your keys when upsizing?

How are you upsizing? iirc there is a choice in the Access upsize wizard.

Well I choose validation rules, defaults, table relationships, plus use DRI and No timestamp fields or tables. I'm starting to think that I should upsize with the indexes?? I only didn't because I remember reading somewhere that it wasn't good or necessary to include them.

I haven't upsized a DB for a couple years, unfortunately I don't recall all the details. Maybe DTS would work better, if you have access to DTS (Data Transfer) which is normally installed with SQL Server. I'd suggest an empty database on the SQL Server to experiment and test your upsizing, then just see what happens with different settings.