MS Access Upsizing to SQL Server Issues & Troubleshooting

Upsizing Access Databases to SQL Server?

If you are like me you have had one or two very small Access databases that have grown and need SQL Server’s power. Naturally you run the upsizing wizard and move over all your tables. This allows you to keep the Access fron-end and have a rip-roaring SQL Server in the back-end. Here are a couple things to look out for:

Careful with the Switchboard

Migrating the switchboard table over to SQL Server is a good idea if you want have many people using the Access database front-end.  If you can figure out the switchboard table, you can modify your switchboard using this table.

If you migrate the switchboard table to SQL Server, you can no longer use the Switchboard Manager

To get around this issue, you have have to modify the table directly.

Timestamps: Problems Updating Data

Be sure that you include a timestamp on your tables, Access needs these if it is going to make some DML changes to your data. I believe although couldn’t find verification that it uses timestamps on the tables as a locking mechanism and reduces contention.

New Problem, Indexes

Primary keys do not come over appropriately. They come over as unique non-clustered indexes, not clustered. This means your data can be stored out of order on disk, which is very inefficient for larger tables. Delete these indexes and create primary keys for them!

Lastly, any changes made to indexes (creating, altering, dropping) will need the Access front end to be updated. Be sure to refresh your linked tables in the linked table manager.

Enjoy!

Advertisements

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s