Post reply

Access database to sql2k

  • March 15, 2004 at 4:34 pm

    #60083

    We have an Access database 350MB (no misprint) in size. I need to import it into Sql 2k. While I can easily capture the data via DTS I suspect it won't capture the relationships between the tables.

    What's the best way to do this?

    TIA,

    Bill

  • March 15, 2004 at 6:43 pm

    #498878

    Bill,

    Have you just tried using the Upsizing Wizard built into Access 2k?  It creates a nice report of how it converts Access Types into SQL Types and reports problems.

    Peter Evans (__PETER Peter_)

  • March 15, 2004 at 7:31 pm

    #498880

    Peter,

    Thanks. I am not that familar with Access. I ran the upsizing wizard as you suggested and it seemed to go well but the Sql dataabse is only 25MB in size, Given the orignal Access database was 350MB I don't know if this is cause to celebrate or to wonder where all the data went.

    Thanks,

    Bill

  • March 15, 2004 at 8:20 pm

    #498884

    Regarding Access file size -- it can "blote" real big in a short period of time depending on activity against it.

    Try loading the Access Database and them via the Access Menu run the "Tools" - "Database Utilities" - "Compact and Repair Database" menu item. I bet the size of the Access database will shrink dramatically.

    I have had 5mb Access files compact down to 0.5mb if they had been in use a while with lots of activity.



    Arden

  • March 15, 2004 at 9:55 pm

    #498894

    Bill,

    Arden is right on the money, I've seen Access database shrink by this factor.  So try the built-in tools.  The cause as IRC is that really never internally deletes records just marks them as deleted with a flag.  This keeps its performance fast.  (Note: the last time I looked at an inside the Access format Knowledge base article was back in the Access 2.0 days.)  Consequently a routine tasks to do with access(.MDB) files is to use the Database utilities to compact them.  I think if you search around there is probably a Visual Basic Script for doing this programmatically.

    Peter Evans (__PETER Peter_)

  • March 16, 2004 at 12:00 am

    #498897

    You may also want to do a random check (row counts) on a few tables to make sure that all your data came across. I've used the MS Access Upsizing Wizard a few of times and it does tend to be a bit finicky if you don't pick the right options for your database. Double-check any RI you may have had in the Access DB - it will be implemented as either constraints or triggers in SQL - depending on which options you picked.

    Also, take a scan thru the pages and pages of report the wizard churned out - you will be able to pick up any errors or warnings where the wizard couldn't complete something.

    WRT compacting Access DB programmatically - you can run the Compact and Repair from the command line or set up a shortcut that does it - I would assume that you can also schedule it to run on a regular basis. The following are the parameters:

    {path to MS Access.exe} {Path to database} /compact

    "C:\program files\MSAccess.exe" "C:\MyDBs\db1.mdb" /compact

    Check STARTUP COMMAND LINE OPTIONS in MS Access help - there are other options you can use as well

    Cheers

     


    The Aethyr Dragon

    Cape Town
    RSA

  • March 16, 2004 at 1:21 am

    #498905

    Another option I have used several times (this is after being caught out by upsize wizard loosing data!)

    Use Upsize wizard to export the desin only to SQL

    Then use SQL Import data wizard to import data after.

    I have found this to be far more reliable than doing everthing from access.

    Karl

  • March 16, 2004 at 6:36 am

    #498932

    I have a follow-up question to what's been said here.  I work a lot with Access (and the suggestions above are correct for correcting the bloat).

    I'm about to start working with SQL Server and will use the upsizing wizard.

    Which option is best in SQL Server for preserving the relationships, via triggers or constraints?  I expect its one of those "it depends" situations.  So, maybe a better question is, briefly what are the main factors in deciding for one or the other?

    Kevin

  • March 16, 2004 at 12:25 pm

    #499021

    Actually, the upsizing wizard is not a very good tool to use.  It is the best one if you do not have quality tools, like ERWin Data Modeler.  But, After upsizing, you need to go in and clean up the naming of all the relationship, indexes, and primary keys.  Also, you should never upsize the data, only the structure.  ERWin has the capability of reverse engineering the Access DB and the selecting a different target, SQL Server, Oracle, etc, for the destination db.

    ERWin is not a cheap tool, running $3,500 retail.  It is available as older versions from EBay and can be had for about $700 that way.  The current release 4.1.4 seems to be more stable that the past versions.

    You should use the import data wizard or DTS for transfering the data after cleaning up the database.

    You should use DRI (I think it is called) and not triggers for referential integrity on the SQL Server side.

  • March 16, 2004 at 12:30 pm

    #499024

    Are those capabilities mentioned for ERWin the same type of features that I believe exist in Visio?  If so, that's more affordable for me (since I already have that).

    And thanks for the other tips, too.

  • March 16, 2004 at 12:42 pm

    #499026

    I am not sure about Visio.  I would think that the Visio for Enterprise Architect would do some of these things.  You might also check out Quest Software for their version of Toad for SQL Server (vs. Toad for Oracle).  That would give you some kind of administration tool that you could use if you do not have Enterprise Manager for SQL Server.

    You might also look at Visual Modeler from MS.  It is designed to work only with SQL Server like Toad, but it will allow you to do a good model of your db.

    What I do not think (and someone correct me if I am wong) is that Visio will reverse one type, Access, and create another type, SQL Server.  ERWin is compatible with about 15 or so different databases.  And, to comment on the My SQL earlier in this thread, it is not one of the ones supported by ERWin. 

    The cheapest way of doing it is to upsise, then clean it up using one of the administration tools.  Visio can not do the administration.

  • March 16, 2004 at 10:19 pm

    #499067

    Thank you for all the great replies. Obviously, I thought the original problem was simply a "use DTS" one so I put it in the forum. I didn't realize that there were that many aspects to the problem!

    Bill

  • March 17, 2004 at 3:00 am

    #499083

    One issue to watch for when using DTS to import MS Access data is that Access date/time fields support 'older' dates (100 AD through to 9999 AD) compared to the datetime fields in SQL Server (1753 AD to 9999 AD).  This can be a problem if you are storing historic (e.g. archaeological) data, or if the year has been mistyped (e.g. 201 for 2001).

    (tip: You can quickly identify stray dates by opening the table in access and sorting the date/time column in ascending order and filtering out any null values)

     

  • March 18, 2004 at 2:49 am

    #499272

    I transfer my Access DB to SQL Server 2000

    the best way is to use Access 2002 (XP) upsizing wizard .... it trasfer data , RELATIONSHIPS, INDEXES, defaults and many other things

    Use RDI in Relationships

    of course check all checkbox (to trasfer indexes)

    Ok ... ?

    try .. and If you need any help .. I am here


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply