Update database structure from Access database to SQL server

Question

Update database structure from Access database to SQL server

Oliver Bradonjic

Valued Member

Points: 62
More actions
October 20, 2005 at 6:35 am

#67916

My compani develop a busines aplication in Access . We try to transfer Access database structure to SQL server database.
Please help
Thanks a lot for all of your help.

Oliver, Serbia

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Depending on the version of Access you are running and whether you performed a 'Complete' installation, you could try running the upsizing wizard (Tools/Database Utilities/Upsizing Wizard) - that should get things rolling for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ionel Ciobanu-132742 Grasshopper Points: 22 More actions · Answer 2

If you want to transfer the database only (only tables and queries) you can use "Import and Export Data" from "Microsoft SQL Server".

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 3

Having seen the Upsizing Wizard in action, I'd recommend you do some reading first. Here are a couple of links to start:

http://support.microsoft.com/?kbid=237980

http://techrepublic.com.com/5100-22_11-5035130.html#

http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

Oliver Bradonjic Valued Member Points: 62 More actions · Answer 4

After the database has be upgrade to SQL server some changes can be made in Access database. We want to updata SQL server database onli with this changes.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

This is a manual job I think. Suggest you retire the Access db as soon as possible!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 6

The upsizing wizard has done a fair job for us with the following constraints in Access:

Make sure all your tables have Primary Keys
Watch out for duplicate keys -- Access allows them, SQL doesn't
Make sure all your table rows don't exceed 8k in total length of all fields - Access allows it, SQL doesn't.
Watch out for keys generated in Access using Triggers that generate random values. I don't know who came up with this one, but I've seen it published as an alternative to an Autonumber column. It's worse than Autonumber, not only is it not related logically to the data, but it's totally random and requires a lot of overhead to ensure PK<->FK relationships on insert/update/delete to child tables.
If you are replicating your Access DB make sure you get rid of the replication columns in your SQL tables. I don't remember the names exactly, but they're something like S_GUID, etc. They're the last 3 or 4 columns in every table and they have the same name in every table. You also will want to turn off replication for your Access DB. If you Google the subject you can find methods and code to do so.
6. Upsizing requires establishment of an ODBC connection. That can create new problems for you in that everyone who uses the database will require the ODBC connection. There is code around (again check Google) to change the connections to "DSNless" connections that don't require ODBC. You just insert the code into a Module, adapt it to your database, and run it after upsizing.
Regardless of whether you use ODBC or DSNLess you may experience problems on each user's desktop with the version of MDAC. SQL Server requires that each user connection via ODBC or OLE DB have a version of MDAC that is the same or newer than that running on the SQL Server. You may have to install the latest MDAC all around just to be safe.

Once you get your data up into SQL you need to review your code and replace as much of the native Access DAO code with ADO code as you can. Remember that Access/DAO retrieves ALL the records from a datasource, then applies any filters. If you have a table with thousands of records and you only need the 10 that have, for example, State='NY' then DAO will return the entire table from SQL to Access then filter on "State='NY'" whereas ADO will only retrieve the records where State='NY'. Your performance will improve significantly the more DAO code you can replace with ADO.

Sound like a lot of work? It is. Hence the "concise" recommendation to "retire the Access db as soon as possible". We've upsized a couple of applications and now we're considering just rewriting them as Web Apps due to all the problems encountered. Of course then you have to consider the development cost, timeframes, limitations of the Web vs. a "thick" client like Access, and how you will do reporting (Crystal-$$$$, SQL Server Reporting - Good if you've got it set up and running already, otherwise a bear to get started).

You have some big decisions to make.

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 7

Make certain that any logical fields (Yes/No in Access) that convert to Bit fields in SQL have a default value of (0). Otherwise you may run into updating issues where the error from Access suggests that two users have both changed values in the record.

Bill

Chattanooga, TN

Oliver Bradonjic Valued Member Points: 62 More actions · Answer 8

What about C# app. Did ADO has mechanism to read database schema and that information store in DataTable in DataSet. This DataTable must have all information needs to create database in SQL server.