January 20, 2006 at 2:36 pm
How big of a job would it be to move the data form a access Database to a SQL server. I need to keep a references link between the SQL Tables, and the Report, Forms on the Access Front End while moving away from the Access back end, ? Has any1 ever done this before I was asked to do it as a projects and need to find out a bit more about how to link the reports forms, queries etc up with the new SQL database?
I know how to movethe data to the new datbase via DTS but its the links between the Froms report etc on access to the new SQL DB i donot know how that works
Thanks in advance,
January 22, 2006 at 8:45 am
After you moved you tables do sql server, you'll want to relink the linked tables to the ones on sql server. If you know some vba you can manage it without using ODBC DSN's.
In a second phase you can work on your query objects to make use of the sql server tables/views directly instead on relying them on linked tables for an additional speed increase. Have a look in the books online of sql server what small differences are in syntax (Access sql<->T-SQL)
The internet seems to be full of articles on the upsizing.
http://www.granite.ab.ca/access/sqlserverupsizing.htm
http://www.aldex.co.uk/sqlupsizing.html
http://www.aspfaq.com/show.asp?id=2182
an article on sqlservercentral on moving from access to sql server:
http://www.sqlservercentral.com/columnists/dasanka/upsizingtheaccessdatabaseintothesqlserver.asp
and the following discussion
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=144&messageid=160892
*
At work we haven't used the access upsizing wizard because of history reasons (migrating small parts at a time).
We've developed a small vba routine that relinked the tables with a dnsless-connectionstring as input parameter. (http://www.connectionstrings.com, http://www.mvps.org)
Since the queries, forms relied on linked tables there wasn't much to do to get a version up and running. Second part was to optimize queries (making them passthrough), forms (making use of passthrough queries, addtional vba code) to use sql server data directly to reduce locking, network traffic.
The major issue we've had is that true is sometimes represented as -1 (access, sql server 7) and another time as 1 (sql server 2000)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply