Moving Access to a SQL 2005 Backend (Access 2003)

Topic

New Reply

Hi,

Just out of curiosity, is there a huge amount of work involved in moving an Access database out of Access onto SQL 2005, but keeping the Access front end??

Is there anything that I should watch out for in particular?

The front end contains a number of different screens.

The reason being that at the moment I have an Access application which is supporting around 12-14 users and has become too unstable to ignore anymore.

Many thanks for your help.

Regards,
Lee

Reply | Quote

Replies

There is an upsizing wizard in Access, but it doesn’t work with SQL Server 2005, only with older versions. So you’d have to import the Access tables into an SQL Server 2005 database manually. You can then re-link the tables in your Access frontend to SQL Server 2005. Forms and reports should still work, in general.

Reply | Quote

Is there a significant speed advantage to be gained from migrating to SQL server?

Reply | Quote

If you have very large tables – yes.
If you have many simultaneous users – yes.

Reply | Quote

There are substantial speed advantages to SQL Server if you are returning small datasets from large tables, but they aren’t automatically accrued by simply converting or upsizing. Never the less, we do use SQL Server as the the backend to nearly all of our client projects. It is much more robust – I’ve seen one corruption in a SQL Server database in nearly 15 years of working with it, and that was the result of RAID drives being being installed in the wrong sequence after removing them to do some Y2K testing. And I wouldn’t be too concerned about using bound forms that are based on either linked tables or queries on linked tables. 95% of what we do uses bound forms – some with several levels of subform nesting. As noted in another response, if you are doing lots of stuff in code and trying to capture the value of an autonumber field, there can be issues since Access gets the autonumber when you start an insert operation, and SQL Server doesn’t get the value until the record is actually saved. There are some fairly simple solutions to that issue – if you have it, post back and we can lay them out for you. As to the performance gains, they accrue mostly when you are getting a small dataset, such as you typically do with a single record form. Good luck with the upgrade if you decide to do it, and do get the book Francois suggested.

Reply | Quote

We have an access database front and backend at present. Remote sites connect via Terminal Server to use the Database. Would migrsting the backend enable us to do away with the terminal server, or would the speed be too slow?

Reply | Quote

Sorry – SQL Server won’t help with the speed issue when trying to run remotely. Terminal Services is still your best bet. That or one of the Remote PC services are the only options that really work well with Access – there’s just too much data going back and forth between the front-end and the back end. But you should be able to have your users share a single SQL Server backend and get good response. We do this with several of our clients and we simply use their Terminal Services login as their login for SQL Server and it works just fine.

Reply | Quote

Hi,

Sorry for the late reply, Ive just returned from 2 weeks leave.

Thanks for the response, I’ll bear your comments in mind.

Cheers,
Lee

Reply | Quote

Thanks Hans,

I do have the option of moving to SQL 2000, which would have the advantage of being able to use the upsizing wizard, but the downside of not have the 2005 refinements.

Regards,
Lee

Reply | Quote

Hi Lee,
You might want to check out SQL Server Migration Assistant for Access

Reply | Quote

Ok will do.

Many thanks.
Lee

Reply | Quote

I have been working for the last year converting all of our organizations databases to a SQL Server 2005 back end Access front end. We have users in connected offices by T1 lines as far away as 300 miles away and the performance degrades slightly at that distance but is well within an acceptable range. We also have users with laptops and air cards that are using a VPN connection that have acceptable preformance. To replatform the back ends we are using the upsize wizard in Access 2002 to convert to SQL Server 2005. Here is a list of a few things we learned. There are books, but some of these things were not in the books I read:

Memo fields will not upsize. We add the field to the SQL table set the data type to allow for enough characters then use an append query in Access to add the data
When tables are upsized SQL appends the name of the owner to the table name. This is typically .dbo When we relink the tables we just rename them in the Access database window to exclude the dbo. this allows all the old code to work
All code that uses the Open Recordset method will have to have the dbSeeChanges argument added to the line of code. We just search all of the code using the search tool for Open Recordset
A lot of our forms did not work correctly with Access yes/no check boxes. Prior to upsizeing we converted them to text fields with combo boxes on the forms
The SQL words like SELECT, WHEN etc. are not allowed as field names. Someone in our group was naming fields to track who made changes to forms When. This produced an error in every form in that database.
Changes made to linked SQL table design do not dynamically update in Access you must relink the tables in the Access front end.
SQL security can be a new challenge. We are using schemas to manage permissions. This is a new feature in SQL 2005
Realtionships in SQL 2005 are created and controlled in separate diagrams
The upsize wizard requires all tables have a primary key
If you are using Switchboard forms users are not prompted for a SQL login until the first time they try to access data. We have a table called tblStart with one field in it. We put a hidden field on the startup Switchboard to force the login to occur when the database is opened.
When you install SQL 2005 you do not get the management studio unless you select to install workstation features
WE have out Server in the downstairs Server room so I have installed the management studio on my workstation PC so I can manage the SQL from my desk. I started by using remote access to the server, but that is not a good solution.

Carla

Reply | Quote

Hi,

Sorry for the late reply, Ive just returned fr 2 weeks leave.

Thanks for the pointers.

Cheers,
Lee

Reply | Quote

A very nice summary of some of the things that the books don’t really tell you about upsizing to SQL Server, whether 2000 or 2005.

Reply | Quote

What about security?
Is it handled by SQL Server or Access?

Reply | Quote

We are not continuing to use Access Security. We have have a Novell network so we are using SQL mixed mode authentication and managing permissions with database roles and schemas. We are also using single login for all databases. For Web applications there is another layer where the user has to authenticate with a network login and password through VPN LDAP. We have both Nortel and Cisco VPN available and for whatever reasons the Cisco is faster, but the Nortel is more reliable. This could be a technical problem not a product problem. I don’t have a lot involvement in the LAN group.

Carla

Reply | Quote

I found that the easiest path was to use the upsize wizard to move the data to SQL Server and then relink to it using ODBC.

You will need to delete the old links (a Procedure to do this is in the attached file)
When you reattach the tables they will have a dbo prefix so you need to rename them (a Procedure to do this is in the attached file – you will need a reference to DAO)

Everything should work just as it did using the attached tables but now you can start writing ADO code to access the SQL database directly.

Sorry I couldn’t attach the MDB – it was 160kb

Reply | Quote

And if you want a very good book :
Microsoft Access 2000 Developer’s Guide to MS SQL Server

Reply | Quote

Many Thanks for that, I’ll see if I can track it down in the bookshop.

Regards,
Lee

Reply | Quote

Francois, is this relevant to Access 2003?

Reply | Quote

The differences between Access 2000 and 2003 aren’t huge, so most of the book will apply to Access 2003 too.

Reply | Quote

Edited by HansV to replace Word document with the picture it contained – saves the reader a few clicks

Hi Hans,

Ive recently been informed by my management that the conversion of Access is now to be to SQL 2000 as opposed to SQL2005. In the circumstances, Ive tried your suggestion of using the upsizing wizard, but I keep getting the attached error message, despite entering the correct SQL server to be used and password.

Can you think of anything else I need to check or clarify ??

Regards,
Lee

Reply | Quote

See Potential causes of the “SQL Server does not exist or access denied” error message and You receive a “SQL Server does not exist or access denied” error message when you try to connect to a SQL Server named instance in a cluster by using TCP/IP sockets for possible causes.

Reply | Quote

Will do.

Many Thanks for your help Hans.

Cheers.
Lee

Reply | Quote

I have converted two databases from Access to SQL Server. One had bound forms and one had unbound forms. The one with unbound forms was very easy to convert. The unbound form captured data to be edited, or records to be added and then added them using VBA.

The one with bound forms was a nightmare.

One big huge difference for me was that Access and SQL Server assign primary keys at different times, and that created some challenges as well. If you are not using linked tables this will not impact you.

I hope this helps.

Ken

Reply | Quote

Hi,

Thanks for the response.

We have forms bound to the linked tables and the queries in the front end database, the back end has no objects other than the tables. With this in mind, would the forms, bound to linked tables create a nightmare, seeing it is only the back end that needs to be moved??

Regards,
Lee

Reply | Quote

I’ve been involved in a couple of Access/SQL situations that rely heavily on bound forms. This has never caused a problem, so I don’t know why someone categorized it as “a nightmare”.

Reply | Quote