Using Excel VBA to Create SQL Tables

Question

Hi All,

I am trying to use Excel VBA to automate the creation of a SQL table in an existing SQL Database.

I have come across the following code on this side.

Private Sub CreateDatabaseFromExcel()

Dim dbConnectStr As String 
Dim Catalog As Object 
Dim cnt As ADODB.Connection 
Dim dbPath As String 
Dim tblName As String 

'Set database name in the Excel Sheet 
dbPath = ActiveSheet.Range("B1").Value 'Database Name 
tblName = ActiveSheet.Range("B2").Value 'Table Name 

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";" 

'Create new database using name entered in Excel Cell ("B1") 
Set Catalog = CreateObject("ADOX.Catalog") 
Catalog.Create dbConnectStr 
Set Catalog = Nothing 

'Connect to database and insert a new table 
Set cnt = New ADODB.Connection 
With cnt 
    .Open dbConnectStr 
    .Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _ 
             "[RTNumber] text(9) WITH Compression, " & _ 
             "[AccountNumber] text(10) WITH Compression, " & _ 
             "[Address] text(150) WITH Compression, " & _ 
             "[City] text(50) WITH Compression, " & _ 
             "[ProvinceState] text(2) WITH Compression, " & _ 
             "[Postal] text(6) WITH Compression, " & _ 
             "[AccountAmount] decimal(6))" 
End With 
Set cnt = Nothing 

End Sub

However i can't successfully get it to work?

What I am trying to do is actually use Excel to create a table not a database? The database already exists. I would just like to create a new table. The name of the table will be referenced from cell A1 in Sheet 1.

Can somebody please help.

Thanks

Regards

Gerard

Answer 1

Just comment these lines out:

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

This should do it, as these lines create the new database. The latter code just opens it and creates the table you want.