Have you heard this term: "MS Access upsizing"? This means transfer all tables (with data) from MS Access to central database server as front-end forms keeps working. this action is applied when application from old days become too big, but it is too expensive to rewrite and as solution database back-end is changed but UI is kept.
I heard about this two weeks ago when I got a Access solution to upsize. so far so good :) I learned it is typical to have two Access files - one for database and one with UI. UI file use linked tables to database file to access data. This sounds good as it separates layers.
All I had to do is transfer data from database file to SQL Server (the company has brand new server with SQL 2005 installed). Sound easy, doesn't it? From my experience I could tell you Nothing is easy! Especially when you want to make it bug free! To convince you in that I was told that author of this Access solution could not upsize it's own solution for a year. Scary huh!
My first step was to use MS Access Upsizing wizard to transfer data. Beleive or not but it transferred only half tables (I had 116 tables and about 55 was transfered). Why? I simply do not know... I suppose because very few tables have PK.
Second step: After fighting a bit with Upsizing wizard I decided to use newer products. I jumped directly to SQL Server 2005 tools (SQL Server Management Studio), I created new database and used SQL Server Import and Export Data Wizard. Guess what! I wasn't able to transfer all tables at once. Luckily it was Ok when I did it in two steps. Looks like I made half of the task :)
Today I found new newsgroups microsoft.public.access.sqlupsizing that contained link to Migrate to SQL Server page. Microsoft decided that MS Access Upsizing wizard is not good enough and started working on SQL Server Migration Assistants (there are assistants for Oracle ;), Access, and SyBase). they are still on CTP2 but it worked much better than SQL Server Import and Export Data Wizard. I was able to do same much easier and wihtout a single error (with a bunch of warrnings because missing default values). It will be much easier to explain whole procedure to our office
Milestone 1 Reached.
The second one is easy. Especially if you have experice with developing for MS Office. I executed the script below to relink linked tables to SQL server location (in UI access file)
Const TABLE_CONNECT As String = "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;APP=Microsoft Office 2003;WSID=GILIEVNEW;DATABASE=AlliantDataSQL;UID=hehe;PWD=XXXXX;"
Dim tdf As TableDef, db As Database, tbfSource As TableDef
Set db = CurrentDb
On Error Resume Next
For Each tdf In db.TableDefs
'*** check if this is not system table
If InStr(1, tdf.name, "MSys", vbTextCompare) = 0 Then
'*** check if table exists in AlliantData.mdb
tdf.Connect = TABLE_CONNECT
'*** print error if any
If Err.Number <> 0 Then
Debug.Print "Error Arrised at table: " & tdf.name & " . Error is: "; Err.Description
Set tbfSource = Nothing
I this is it. I will write again when I talk with clients and we change their files :):). Whish me good luck
I tried some more solutions I come across article How to create a DSN-less connection to SQL Server for linked tables in Access 2003 and in Access 2002. It helps when you want to create linked tables in MS Access by script.