Database Creation

[harjeen]

Is there any way to create a new database on an SQL Server (7.0) using ADO?

Thanks in advance,

Nahid Harjee

[JHausmann]

Sure, you can execute DML in a command object.

After I posted this response, I re-read your question. My answer is for tables and other objects. SQL Server wasn't designed to create database objects on the fly, primarily because you're required to have a license to run it and because there are parameters, for performance, storage and security, that need to be set/maintained. Perhaps you can explain why you think you need to create a database on the fly?



[Edited by JHausmann on 06-19-2000 at 04:28 PM]

[harjeen]

Thanks a lot for the response.

The reason I need to create a database on the fly is because I want to make a simple VB app. that can create a database and all the tables in it, without me having to go into SQL Server Enterprise Manager and specifying all the field types, etc., etc.

In other words, I'm trying to make several databases (named differently, of course) all following the same template.

Nahid Harjee

[kovy27]

Hi,

well JHausmann was right. I dont think theres a way of creating an SQL Server database on the fly using (ADO), there are too many things that have to be done using SQL Server alone. Like storage and security (Grants and Revokes). You can use ADO to connect to the database using the proper DSN of course and use ADO's objects to interact with the database.

[JHausmann]

You can create your tables on the fly but you'll need to fix the number of databases. I'm still not clear why you need all the different databases and tables. From what you've posted thus far, I would create one database with as many tables as you need. In those tables that require it, I'd have a userid. Your code would use the userid to ensure that a user (company, whatever) would only access the data that pertains to them.

I'd use different databases for a test region, a validation region and a production region (but only if you can't get a server for each environment).

[harjeen]

Yup, that's exactly the reason for all the databases. I need one for testing, one for doing a demo for my supervisors and one which will be the final production database.

[JHausmann]

You can either build a script that builds your structure or you can use the tools provided to copy the structures.

Where I work, we've created a set of scripts that create everything. If we want a new database, we just run the scripts...