You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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.
Comments: (6) Collapse
Interesting read...
With regard to Pitfall #1, it's worth perhaps mentioning that it's not just custom functions that can cause problems - native Access functions such as IIF and NZ will also give headaches because they're not understood by SQL, and so result in the complete dataset being returned to Access for it to process at the front end...
Those are great tips Juan,in my book Access 2010 VBA Programming Inside Out I have three chapters focusing on upsizing and getting to grips with SQL Server.
For me one of the greatest features in SQL Server is schemas where I can partition my Access application data into chunks, synonyms mean that once a table is on a schema you can still use the table name without a schema prefix in the SQL to refer to the table.
Pitfall #6: Continuing to think like an MS Access developer when building SQL Server queries/Views/Stored Procedures et. al.
By this I mean that there are different considerations to using and building queries of different types (CRUD) between MS Access/Jet/Ace file-based data stores as opposed to an actual Server-base database like SQL Server, Oracle, or even mySQL (among others). I'm not even discussing the particulars/peculiarities of any of those databses either. It is just that the nature of a server-based databse is different form the neature of a file-based database, and things that were done one way in Access with a file-based back-end data store, many time need to be re-thought out when going to a Server-based data store - just because they are different animals and becuase different concepts apply. The formost of these differences is Security. Server-based database systems actually have some (in contrast to how desktop/file-based databases are often configured), and it must be given serious consideration if it is to be a help, rather than a hinderance - and if it is to do the jobs it was intended for, namely, to protect your database.
Thanks for your personal marvelous posting! I actually enjoyed reading it, you will be great author. I will always bookmark your blog and will often come back in the future.for more information visit my website www.easyxlead.com
Your site is surely definitely one of the very best . As a whole conception of the site is just fabulous..thnx..
file hosting
I don't agree with your "Pitfall #4: Leaving your bit fields nullable" statement. By default in Access the Yes/No field is a Tristate field. When it is Null, it means that a response was not entered. Updating the field to False is wrong...you cannot assume it should be False.
Comments: (loading) Collapse