AppId is over the quota
Guest blogger Juan Soto, an Access MVP and founder of IT Impact, Inc., shares five tips on upgrading Access to SQL. You can read more about Juan and his experiences and knowledge of Access knowledge in our Q&A with him.
Access works great with SQL Server but upgrading your data is not a slam dunk. There are all kinds of things that can go wrong if you're not careful, avoid these pitfalls when you do your first or next Access migration.
Pitfall #1: Relying on the SQL Server Migration Assistant for everything
I'll be the first one to endorse the SQL Server Migration Assistant. It does away with a lot of manual work, but it's not perfect. For example, if you have queries that use custom functions, the Assistant will not even try to create a SQL View, leaving it up to you to make it happen. You can download the Assistant here.
Pitfall #2: Not adding necessary foreign keys and indexing them
Access automatically adds a secondary index when you add a new field that ends in "ID", as in "CustomerID". When you create a relationship in Access, it also automatically creates an index with the relationship. Not so with SQL Server. You must manually add foreign keys to your SQL Server database and index them.
Pitfall #3: Not adding a rowversion field to all of your tables
In order to avoid problems with Access committing record changes to SQL Server, always add a rowversion field. Normally, the Assistant will add it only when it determines it's necessary; for example, a column on the table that uses floating numbers might be a good candidate for needing a rowversion, but I recommend that all of your tables have a rowversion column.
Note: In older SQL Server versions, rowversion data type is confusingly labeled as timestamp data type. It has nothing to do with timestamping but indicates relative order of when a row was updated in a table's life. If your copy of SQL Server shows only timestamp, please use it instead.
Pitfall #4: Leaving your bit fields nullable
Think of bit fields as Yes/No fields in Access. In order for them to work in Access, you need to make sure every bit field cannot be nullable, and so you need to set the default value to zero, e.g. false, in your SQL Server table design.
Pitfall #5: Not taking advantage of what SQL Server has to offer
Getting your Access tables into SQL Server is a great first step, but if you just stop there you're leaving a lot on the table: using Views, stored procedures and functions are a great way to turbo charge your Access app. Start with Views and work your way up to stored procedures. For more great tips on using these tools, I suggest browsing through all of my blog posts on SQL Server with Access at AccessExperts.net/blog/SQL-Server.
No comments:
Post a Comment