Post reply

how to convert SQL Server 7 to MS ACCESS ??

  • June 28, 2004 at 3:59 pm

    #360167

    I have seen a lot of articles going from ACCESS to SQL SERVER, but how to go in reverse?  What is the easiest way to convert an SQL SERVER DB with over 300 tables/views into MS ACCESS automatically?  I'm not so interested in setting up indexes, keys, etc.  I just want the data in ACCESS MDB format so I can give it to an end user and they can query the MDB to their heart's content.  THANKS!!

  • June 28, 2004 at 5:08 pm

    #512304

    Using the Export Wizard. Right click on a particular DB and under All Tasks you will find it.

  • June 28, 2004 at 7:11 pm

    #512322

    another way to do this would be to:

    From Access goto File > Get External Data > Import.

    Select the ODBC datasource, all the tables, and sit back and watch.  1 caveat to this is that IF the table doesn't have a PK you will be prompted to select something or skip.

    Good Luck,

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 29, 2004 at 12:46 pm

    #512515

    That's the  easiest manual way.  It will make a COPY of the data.  If you pick "LINK" it will (duh) link to the data, so if you changed it in access, it will be changed on the main server.

    Programmatically, you can create DTS package.  Though I haven't done one where I go from sql server to access.

    This is actual code (You set it up through a wizard, but you can see the code it creates and modify it).

     

    ..

  • June 29, 2004 at 10:19 pm

    #512565

    I want to do this programmatically, and I understand that DTS is the way to go... but how to go about this in an automated fashion?  Given SQL SERVER DB 'X', how to automatically create the DTS package to transform this into an MS ACCESS MDB ??  Are there 3rd party software packages to help or some other handy utility?  Thanks!!!

  • June 30, 2004 at 2:02 am

    #512586

    I've not tried it, but have you investigated using Hetrogenious Replication, either for a Snapshot or for merge (not sure which, if either, is possible !)

  • June 30, 2004 at 4:53 am

    #512606

    I've always found that the best method is nearly always to PULL the data to the detination rather than PUSH it from the source.

    with that in mind the Access import/link table method is easy.

    just create a file DSN and import using the DSN peice of cake.

    MVDBA

  • June 30, 2004 at 7:56 am

    #512664

    Check out this site:  http://www.sqldts.com/

    They have an article specifically about creating an Access database:  http://www.sqldts.com/default.aspx?288

Viewing 8 posts - 1 through 7 (of 7 total)

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