Unanswered: please help access/sql server conversion
I am new to the db world. I am trying to convert an Access 2000 database to SQL Server 2000. f/e has forms, reports, macros, modules, b/e has tables which the f/e file has linked to via link manager. The b/e is on a shared network drive. A dozen users, about 150 tables. The b/e file is appoaching 1Gb. The plan is to keep the Access f/e.
I have read the microsoft Q;s on Upsizing, DTS, Access Project vs Access database, Access sv SQL, etc. I am having diffculty understanding the best approach for my situation.
Looking for some expert guidance before I jump in.
Why do you need to migrate your data hauling operation from pickup trucks to 18 wheelers in the first place? Load? Speed? Safety?
How about - just for starters - a simple/non-radical/initial approach:
Keep everything the way it is except for actual data in Access tables that could go into SQL tables instead with Access tables linked to it. This will for instance overcome one of the biggest Access limits: 2 GB per "mdb".
(As you probably know, once over 2 GB - in 2000 & 2002/XP - Access could crash at any moment with data from it never to be seen again. )
Speed is the main thing, and there have been multiple user contention problems leading to lockup. The site is upgrading everything for speed.
When you say "move the actual data only" What's the proper method - simplest, to move the data only, i.e., how do i create the SQL tables automatically/safely (150 tables), and then weeks/days later move the Access data to the new tables?
The real Client-Server solution would be "by the book" conversion of Access "mdb" into Access Project (mdp) as a front end and storing data with data processing in SQL S.
This of course is easier said than done, especially on live and often used db.
What is really easy (although not a real solution) is to first setup tables in SQL S., work out minor differences of data types between Access an SQL S., run SQL S. import wizard to populate the tables and then link to them from Access. For one thing it is a quick workaround for JET limit of 2 GB per mdb since linked tables do not take lot of room, for another is getting data into SQL S. to find out stress-free any conversion issues, like for instance often with date/time fields.
I had Access run like hell this primitive way on well indexed datasets of over 50 GB.