March 25, 2010 at 12:25 am
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..
April 17, 2010 at 8:45 am
December 23, 2010 at 6:22 am
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
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
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.
February 9, 2011 at 7:02 am
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
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