"CREATE DATABASE" withOUT ADOX (SQL)

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
L

Legacy 98055

Guest
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

Active Member
Joined
Apr 13, 2009
Messages
350
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.
 
L

Legacy 98055

Guest
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

Active Member
Joined
Apr 13, 2009
Messages
350

ADVERTISEMENT

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.
 

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350

ADVERTISEMENT

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

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
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!
 
L

Legacy 98055

Guest
"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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top