how to convert SQL Server 7 to MS ACCESS ??

Question

Post reply

how to convert SQL Server 7 to MS ACCESS ??

Eben Yong

Newbie

Points: 2
More actions
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!!

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Antares686

SSC Guru

Points: 125444

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.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

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

sholliday SSCrazy Points: 2100 More actions · Answer 3

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

..

Eben Yong Newbie Points: 2 More actions · Answer 4

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!!!

EdH334 SSCrazy Points: 2857 More actions · Answer 5

EdH334

SSCrazy

Points: 2857

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 !)