Upsizing (1 Viewer)

I am learning to push tables from Access 2010 up to SQL Server (2005). When I "upsize" a table, Access shows the table linked. This means I can no longer run ALTER TABLE queries.

I have read some threads which say "Upsizing is a one-time deal, you should not need to alter...." In my case, it is not a one-time deal. What is the best practice for altering tables which have already been pushed to SQL Server?

I localized the table, altered the table, then tried upsizing again. But the wizard will not allow me to overwrite a table which exists in SQL Server. Should I just delete the table in SQL Server every time?

gry086 said:

I am learning to push tables from Access 2010 up to SQL Server (2005). When I "upsize" a table, Access shows the table linked. This means I can no longer run ALTER TABLE queries.

I have read some threads which say "Upsizing is a one-time deal, you should not need to alter...." In my case, it is not a one-time deal. What is the best practice for altering tables which have already been pushed to SQL Server?

I localized the table, altered the table, then tried upsizing again. But the wizard will not allow me to overwrite a table which exists in SQL Server. Should I just delete the table in SQL Server every time?

Click to expand...

Use SQL Server Management Studio to modify the tables once you have upsized them. It will give you more control over SQL Server features which Access doesn't support directly. Once you've modified the table, use Linked Table Manager to refresh the links.

I try not to upsize until I'm pretty sure the schema is complete. Once I upsize, I use SSMS to manage the modifications. I find it much less user friendly than Access but it is understandable with a little study. And as Button said, don't forget to refresh the links if you change the schema in SQL Server.

My favorite free tool is SQL Server Migration Assistant for MS Access.

Then, make a local Access table. Column 1 the name of the tables to link in SQL Server, column 2 a true/false Linked, column 3 a description.
Write code to disconnect and re-link the table names with a true.

You will need to use SQL (SSMS) to change any tables from that point, then re-link (or run your script again)

In SQL Server: set up a 2nd DB with the same name + a T at the end (MyDB and MyDBT) T is the Test DB. With your linking table script, connect to either live or test.
In SSMS, you can restore the Test DB from the live DB.
I typically test everything first before moving it to a live dB.

As Pat says, SSMS is not as Friendly. However, using the SQL Server Migration Assistant for MS Access - it is a snap to design and test a few new tables on Access locally, then just move the new table designs over to SQL Server.

SQL Server does have its overhead. But, it is very worth it in most cases.