Create database in SQL Server (only once) using VBA Excel

Question

I would like to create a database in SQL server using VBA (Excel) just the first time that I will run the code. So the second time I run the code, the database will exist, and it will not create another one.

Could you please show what did you try? – abarisone Apr 16 '15 at 06:41 — abarisone, Apr 16 '15 at 06:41

score 0 · Answer 1 · answered Apr 16 '15 at 07:25

@abarisone

`Public Sub CreateDBTable()

Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbName As String
Dim tblName As String, ServerName As String, UserID As String, pw As String

tblName = shControl.Range("B5") 'Table Name
ServerName = "SERVICESWS15"                 'Enter Server Name or IP
dbName = shControl.Range("B4") 'Enter Database Name
UserID = ""                     'Leave blank for Windows Authentification
pw = ""                   'Leave blank for Windows Authentification

dbConnectStr = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & dbName & ";User Id=" & UserID & ";Password=" & pw & ";"

Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing

Set cnt = New ADODB.Connection
With cnt
    .Open dbConnectStr
    .Execute "CREATE TABLE " & tblName & " (KEY nvarchar(100) NOT NULL, " & _
             "Date nvarchar(100) NOT NULL, " & _
             "PRIMARY KEY (KEY));"
End With
Set cnt = Nothing

End Sub `

There is an error in this line: Catalog.Create dbConnectStr

Error: No such interface supported

wait a minute. are you trying to create tables only or an entire database, as in the original question? — Laszlo T, Apr 16 '15 at 07:39
@LaszloTenki Thank you! Im trying to create the database and a table to that. — vba_dt, Apr 16 '15 at 07:50

score -1 · Answer 2 · edited May 23 '17 at 11:57

It's not very complicated. First make sure that you are referring to the appropriate ADO library like here on the screenshot.

Then your have certain building blocks you will have to use: first make a Connection object (with a connection string), then make a Command object, and last but not least use the ExecuteNonQuery method of Command on your connection object. It does what the name says: executes an SQL command without having a RecordSet as a return object. See examples in the documentation starting from here.

I have not tried it before, but for this to happen without error, you will probably have to set your connection string to the system database called "Master" if working on MS SQL Server.

Of course, the SQL commands you will have to execute are (1) check if the database exists, (2) create db and tables if not.

Then you can create your "normal" Connection object to your database.

WARNING: to be able to create a database, your technical user defined in the VBA script must have high (system admin) privileges which is definitely a HUGE RISK even if you protect your excel. If it's not a sandbox environment, DO NOT DO IT!

Create database in SQL Server (only once) using VBA Excel

2 Answers2