Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    New Jersey
    Posts
    4

    Post 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.

    Thanx

    CF

  2. #2
    Join Date
    Mar 2002
    Location
    Westfield, NJ, USA
    Posts
    23
    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. )

  3. #3
    Join Date
    Jun 2002
    Location
    New Jersey
    Posts
    4
    Thanks for your quick reply.

    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?

    Thanks,

    CF

  4. #4
    Join Date
    Mar 2002
    Location
    Westfield, NJ, USA
    Posts
    23
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •