Using Excel VBA to Create SQL Tables
Posted
by user307655
on Stack Overflow
See other posts from Stack Overflow
or by user307655
Published on 2010-04-02T12:27:07Z
Indexed on
2010/04/02
12:33 UTC
Read the original article
Hit count: 259
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
© Stack Overflow or respective owner