Post reply

Update database structure from Access database to SQL server

  • 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

     

     

  • October 20, 2005 at 7:52 am

    #598713

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 20, 2005 at 11:31 pm

    #599012

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

  • October 21, 2005 at 2:18 am

    #599031

    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

  • October 21, 2005 at 6:51 am

    #599089

    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.

  • October 21, 2005 at 7:00 am

    #599093

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 21, 2005 at 8:12 am

    #599131

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

    1. Make sure all your tables have Primary Keys
    2. Watch out for duplicate keys -- Access allows them, SQL doesn't
    3. Make sure all your table rows don't exceed 8k in total length of all fields - Access allows it, SQL doesn't.
    4. 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.
    5. 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. 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.
    7. 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. 

     

     

  • October 21, 2005 at 9:02 pm

    #599343

    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

  • October 23, 2005 at 12:18 pm

    #599394

    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.

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

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