Database Question [SOLVED]

[hothead]

While searching the forums, I found a code that allows one to create tables programmatically.

Set db = CreateDatabase("C:\NewDB.MDB", dbLangGeneral, dbVersion40)
 
'add a table with some fields 
sql = "CREATE TABLE TestTable (ID COUNTER, SetName TEXT(50), SetVal TEXT(255), Description TEXT(255))" 
db.Execute sql

This worked great, but I can't get it to work with a database that already exists. How do you get this code to work if the database already exists?

[brucevde]

Open the existing database first and then run the CreateTable statement.


Set db = OpenDatabase("C:\NewDB.MDB", ...) - I can't remember the rest of the parameters off hand.

CreateDatabase and OpenDatabase means you are using DAO. Just to let you know, there are other options like ADO, ADOX. Not neccessarily better or worse - it all depends on your program.

[hothead]

Ok, ADO is what I'm using in the first place, but ADO doesn't have a database object that I've found. I'd much rather not have to use DAO just to create the new database if I can avoid that.

Just to let you know, I found this a little bit ago. I already made my program using ADO, and recently found that I could use this to optimize my code a bit by eliminating the need to package the database along with the program.

How do I do this with ADO?

[brucevde]

Same way. Open a connection to the database and execute the sql statement.

Dim objConn as ADODB.Connection
Dim strSQL as String 
 
Set objConn = New ADODB.Connection
 
objConn.Open "provider=microsoft.jet.oledb.4.0;data source=C:\NewDB.MDB"
 
strSQL = "CREATE TABLE TestTable (ID COUNTER, SetName TEXT(50), SetVal TEXT(255), Description TEXT(255))" 
 
objConn.Execute strSQL
objConn.Close

Edit:

Oops, that will only work with an existing database. Not sure if you can create a database with ADO. Probably need ADOX for that.

[hothead]

That's ok. I can improvise. Thank you very much.