DTS, MSDE and an upsized access database. 1

[leobaby]

Hey there, I thought this would be the easy part.

I have an upsized access database that I would like to distribute to other remote locations; but I cannot find an automated way to create the database in MSDE. I cannot expect the users to upsize it themselves, but this so far is the only way I can get it to work properly.

I have tried creating a dts script that uses the access database as the data source, but it does not transfer properly. When I connect to it, it will not let me add any records to it. I'm not even sure if this is the method I want to use; I like the fact that it will create a single file .dts package that can be run from a command line.

I have tried and tried with no avail and searched and searched only to find such short answers as "use dts" and that is all.

In short I have a working msde database that I would like to transfer without too much complication. I do have sql server, so using the wizards is a possibility; but the target systems are msde.

Any help greatly appreciated.
Matthew

 

[RiverGuy]

Well first of all, you would have to change some settings in the db regardless, right? Because the server name would change for each desktop possibly.

I've worked with MSDE at home for personal use, and SQL Server at work--so I have no experience distributing an MSDE DB.

The only thing I can suggest is to copy the .mdf and .ldf files from your machine to the other and see if by chance they will go in.

If that does not work, you could have the data residing in an Access DB for your source.

You could then write a VB program with text boxes or something for parameters for server name, and use recordsets and SQL within the VB code to create and populate the databases.

More than likely, the VB way would be the way to go, because with MSDE, you basically have no tools to import or make databases.

 

[tlbroadbent]

MSDE comes with the OSQL command line utility. You can run SQL scripts with OSQL. Thus you can script the databases used in MSDE and use OSQL to execute the creation script at the user site.

Loading the MDF and LDF files on a client workstation and the using OSQL to execute an attach script is also a viable solution, particularly if you have already populated some tables with data. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.