>> building a sql database from another sql database

[Jonathan]

Hi, using Access 2003 and sql 2005, I want to programmatically build a sql
database using another database as the template. That is, the new database
needs to contain copies of tables (in some cases also data), views, stored
procedures, functions, triggers and so forth from the source sql database.

I am considering using sqldmo to transfer object by object to the new
database. Is this the 'best' method? Will sqldmo work for sql 2008 as I
vaguely recall reading somewhere that sqldmo is a legacy object library?

Any recommendation or suggestion appreciated

Many thanks,
Jonathan

 

[David H]

That might be a question for a SQLServer group. If I understand things
correctly, the only way that Access can make modifications to a SQLServer
database is via stored procedures which have to reside upon the server.
Access calls the stored procedure and the sp does all of the work. But, that
doesn't mean that it isn't possible.

 

[Chris O'C via AccessMonster.com]

Have you tried the TransferSQLDatabase method?

http://msdn.microsoft.com/en-us/library/aa221622(office.11).aspx

Chris