The SQL for creating a new database is simple:
CREATE DATABASE dbname
But how do you execute that in ASP? I tried the obvious:

Set cmdDB = Server.CreateObject("ADODB.Command")
cmdDB.CommandText = "CREATE DATABASE dbname"
cmdDB.CommandType = adCmdText
cmdDB.Execute

And I get the error message:
Operation is not allowed on an object referencing a closed or invalid connection.

But to assign an open Connection to the Command object I have to have a file name when I open the Connection, but the file doesn't exist yet.

What am I missing? Or is this a Catch-22?

This is not a catch-22. You need to connect to a database in SQL to do stuff as you know. Obviously you cannot connect to the database you are about to connect, but you should be able to connect to "master" with an authorized user login. Then you should be able to create the new database.

As long as you have an authorized user to connect to master with you are all set.

Peter
------------------------------------------------------
Work smarter, not harder.

And what do you mean by "I have to have a file name"? What kind of file do you need? Do you mean a connection file like a UDL?

Peter
------------------------------------------------------
Work smarter, not harder.

Are you trying to create a new database in SQL Server? Or are you trying to create another database, like Microsoft Access?

Your original post mentions SQL, but that might just as well refer to T-SQL..... And the file reference makes me suspect an .MDB database.


Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

<p>To open a connection, isn't a filename needed? The string dbname in the following:

<p>Set cnDB = Server.CreateObject("ADODB.Connection")
cnDB.Open "DBQ=" & dbname & ";ReadOnly=0;" & "DRIVER={Microsoft Access Driver (*.mdb)};"

<p>If I omit the DBQ=filename I get an error message:

<p>Drivers error '80040e4e'
<br>Operation was canceled.


<p>

Thanks. Could you clarify what the ASP is to connect to "master"? I'm kind of groping around here, with only "Beginning ASP 3.0" and what I can find online for reference.

My goal is to create a new MS Access database, to which I can copy data that's in an Excel spreadsheet. There are limitations on how much I can do to the .xls spreadsheet -- eg, ALTER isn't implemented -- which I believe won't be an issue with the .mdb file.

Right. If it's an Access database, you may want to check out this article.

It describes how to create a new Access database from code, and add a table with a couple of columns in it.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Excellent -- that looks like exactly what I want.

I notice that when you create the first column you set the property col.ParentCatalog = catDB, but you don't do that with the other columns. Does that property get associated with the table as a whole once it is associated with one of the table's columns?

In the example in the article, ParentCatalog is required for the ID column. The code uses an AutoIncrement property, and to use that successfully, ADOX needs to know what kind of database you're using (specified in the catDB).

You could also add it to the table, if you want:     but it doesn't make a difference in this case, because no special properties are used.

Cheers,

Imar




---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.