Upsizing MS Access 97 to MS SQL Server 7
-
I'm upsizing existing Access97 databases on Visual Basic 6 application to SQL Server 7 databases, and I realized that the queries and linked-tables were unable to be upsized.
Are there other alternatives that can be done to convert all queries and linked-tables into the SQL database in a simple and fastest way?
-
As far as I know, you can only import (and export) tables to SQL Server ... without key fields. You can't do it with MS Access Query objects nor will linked tables keep their relationships in tact.
After you export all tables to SQL Server, you'll need to go into design view on each (in Enterprise Mgr) to re-assign the primary (identity) key fields.
-
Microsoft provide an Access/JET to SQL Server upsizing tool - Not sure if the Access 97 one is still on their web site but take a look.
However I am not a fan of the upsizing wizard; it works well for simple designs but not so well for more complex, real-world scenarios. We usually do it manually - that way you are forced to check over the data types, etc.
Another option is to use DTS to pull the data into SQL Server from Access/JET. Then just re-link the tables from the Access front end in a similar way that you would for linking to a back end .mdb database.
There are also loads of "gotcha's" with Acces sto SQL upsizing - again a simple design might not hit any - but the more complex/bigger your Access database is the more likely you are to hit issues (none insolvable but they can add hugely to the timescale).
Regards,
David Saville
Aldex Software Ltd.
-
I had pretty good luck upsizing the queries after I removed all vb code.,
-
You mean u did upsize all your queries? into stored-procedures?
-
David,
I've read about the re-link Access database with SQL database. But I have no idea about DTS solutions. Could you please elaborate more or show me the referencing URL relating to that topic?
Besides, I've also found some write-ups saying only Jet databases allows linked-tables and not SQL. If then, do I have to re-design my database structure and amend all the tables again?
-
Ryan,
Vb6 (?) is perfectly fine linking to either JET (ie Access) or SQL Server - lots of articles out there to show you how to do this. However you have to migrate your data to SQL Server first!
SQL Server (the full version, not MSDE) has a tool included with it called DTS (Data Transformation Services). You can either run this directly or (easier in this situation) create a new (ie empty) database in SQL Server (via Enterprise Manager) then right click it and select 'All Tasks' then 'Import Data'. Now follow the wizard. This will pull in all JET tables across into SQL Server. Then go need to go through them and check all the additional properties to make sure they are correct ( Primary Keys/Indexes/defaults/relationships/etc.).
As far as queries go the equivalent of a JET 'Select' query is a SQL Server Vew - but for action queries or if you have used certain functions in the queries then you mayto use Stored Procedures instead/as well.
Access (ie full Access, not just JET) has a tool to migrate the database (tables and queries) up to SQL Server. Details (somewhere) on Microsoft's web site.
You can use SQL Server in a simplistic way - similar to JET (ie essentially just as a data store with all processing happening on the client). Alternatively you can move a significant amount of processing onto the server using Stored Procedures & Triggers. The latter being usually faster and with better scalability.
Regards,
David Saville
Aldex Software Ltd.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply