"CREATE DATABASE" withOUT ADOX (SQL)

[Enigmachrysalis]

The following all is set to occur in Excel VBA (i.e. why it's not in Access forum)

I know of a way to create a database using a new ADOX catalog, BUT as there is a dedicated "CREATE DATABASE db_Name" SQL statement., I was wondering how you can execute a SQL statement (as you would to create a table) when there is no file to which you can connect yet.

I have searched for some manner of execute statement in ADOX, but all I can find is Create in the ADOX library.

P.S. I tried to connect to the prospective save folder location, but the error prompt indicates that the location is in use or it cannot be accessed.

 

[Legacy 98055]

I'm a bit rusty on my ADO, but don't you do this by way of the Command object? You seem to be speaking of the connection object. What do you think?

 

[Enigmachrysalis]

I am no expert myself, but that is exactly what I tried at first, but I believe the command relies on having connected to DB file, which is what I am trying to create on the first place.

Maybe I am missing a key step, but can you connect to the folder location where the DB you are trying to create will exist. I get the error message described in my opening post.

 

[Legacy 98055]

I don't know and I just cannot justify spending very much time on something that I will never use. Is this just to satisfy your curiosity?

 

[Enigmachrysalis]

No. I want to create tables on the fly and it would be nice if I could just execute a quick three word SQL statement instead of ADOX method. Don't reinvent the wheel on my account though.

 

[Blade Hunter]

I suppose you could link to Access and create a new DB then save it.

 

[Enigmachrysalis]

How do you mean? A linked table via the Excel UI, or VBA?

 

[xenou]

Some searching turns up some hits (this was quite a short search).
Create Database on the Fly - ADO(X)
Create Database Example - ADO(X)
Create Database Example DAO
ADO(X) example - on IIS server

My take is:
1) the create database sql statement will be implemented variously, depending on the sql engine you are working with. In short, T-SQL will create a MS SQL Server database, MySQL will create a MySQL database, and Access will create an .mdb
2) As far as I know to work with JET in ADO (Jet being the engine used by MS Access 2003), you need ADOX to actually create a database in code. DAO also has methods to create databases, but no three-liners. I think Access 2007 is a little different now - using the ACE engine. The fact is, even with T-SQL and "CREATE DATABASE" you are not spared further lines of code to create tables, columns, field definitions, constraints, and all the rest ... you really get a shell to start building your project in.

 

[Enigmachrysalis]

I actually saw 3 of those 4 links, but 2 were dependent on the ADOX catalog method (one even drawing form the example that sparked my curiosity!), so I was hoping to find another solution. I did find an old example of using DAO to create the table using the CreateDatabase method, but the code applies to much older versions of Access, so I would have to figure how to update it.

It seems that ADOX is the way to go, at least for the initial creation of the DB unless any of you find a better way!

 

[Legacy 98055]

"It seems that ADOX is the way to go, at least for the initial creation of the DB unless any of you find a better way!"

You seem to be right here. You can create a recordset without a connection and of course a recordset can be built off of multiple tables, but besides persisting to file, I don't know if you can actually create a db without ADOX. If you could, I suppose that ADOX would not exist. It is possible however, that ADOX is implicitly referenced. I don't know. I do know that this behavior is seen elsewhere such as the MSHTML library being implicitly referenced by way of the WebBrowser's Document porperty. Anyway, I wish you well. Have a nice weekend!