Post reply

Migration from access 2003 to SQL server 2005

  • March 25, 2010 at 12:25 am

    #74598

    Hi..

    I have an app in access 2003. It has tables, queries, reports,forms ,macros etc...

    Now i want my DB to migrate to SQL Server by keeping the front end as access it self...

    after the migration, my expectation is...if i insert / update anything in acceess Front end, the changes should be effected in SQL server 2005 db tables, but not in access tables..

    I have used upsizing tool to migrate once, tables along with data migrated to sql server 2005..but when i am modifying the data from froent end, changes are getting effected only in access db but not in sql server 2005 db..

    let me know if am over looking something...

    TIA..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • April 17, 2010 at 8:45 am

    #1153339

    Hi...

    You should Link or relink the tables to the new datasource, which is the SQL Database.

    You should be able to achieve this using Link Tables in MS Access.

    Thank you... Hope this helps.

    Koncentrix

    Koncentrix

  • December 23, 2010 at 6:22 am

    #1265885

    You need to check your queries also, they suppose to be Pass-Through now, because you run them against tables on SQL server

  • December 23, 2010 at 10:58 am

    #1266026

    If you have the latest version of the Upsizing Wizard for Access, it should have renamed your Access tables with a "_local" suffix, and created and ODBC link to the SQL Server tables. However, if you don't have the autorename feature in Access turned off, it may change all of your references on forms and reports to the renamed local tables. As a rule of thumb, always turn that feature off.

    On the issue of queries, you can continue to use linked tables in Access queries, but you always want to make sure the SQL Server tables have a primary key defined. And you may want to change some of them to pass-through queries (in T-SQL syntax) if you encounter performance issues.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • January 1, 2011 at 12:08 pm

    #1268247

    once you done the uspsizing, you should backup and remove MS Access tables and leave in only their SQL Server equivalents. Than everything that fails you'll fix bug by bug. If you keep both sets of tables inside, you are just doubling up the complexity.

    green smoke reviews

  • February 9, 2011 at 7:02 am

    #1284428

    Consider using MS SQL Server Migration Assistant. It's free (just google it....)

    It seems to work better than the Upgrade Wizard in Access.

    Tell it to "link" your tables (of course, do this as a test first....) and it renames your old links/tables and creates passthrough queries to the SQL Server tables (it has just created...) with the old names. Works beautifully.

    There are still issues, and lots of them. Most are related to queries (don't forget those "hidden" queries inside form control rowsources, etc.) If you retain your Access queries (the Assistant can and will upgrade your explicit Access queries if you want), you can upgrade them one at a time. Until you upgrade your queries (turn 'em into either passthrough queries or views), you will encounter issues you can only find with regression testing.

    Still, use the SSMA. It's a good tool and quite sophisticated.

    Jim

  • February 9, 2011 at 4:47 pm

    #1284898

    Check out 2SQL on http://www.convertu2.com.

    2SQL does exactly what you wnat. Migrates/Converts Access to SQL Server, converts all the Jet and queries to stored procedures so all the grunt work is done on the server, but leaves the Access front end so that the user can just carry on.

    PG

Viewing 7 posts - 1 through 6 (of 6 total)

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