Excel VBA to Create SQL Table

I have found the following code which is supposed to create an SQL table within an SQl Database.

The database is specified in dbpath and the table to be created is in tblname.

However when I run this code I have a problem connecting to right SQL database.

For example if I specify dbpath as "WIN2k8\Test\ABC" ie the machine name is WIN2k8, sql instance is Test database is ABC.

For some reason it keeps picking up the dbpath as where I have saved the Excel workbook then WIN2k8\test\abc.

Can somebody help?

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 = ActiveS开发者_如何学编程heet.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  

---

The Jet.OLEDB provider is for MS Access. Try using a SQL Server connection string if that is what you are trying to connect to. You can find the different types of connections strings at http://www.connectionstrings.com/.

Hope that helps

---

You could also try thy udl file trick described at the following location - You can also try to the udl file approach to get your connection string - http://msdn.microsoft.com/en-us/library/aa140076%28office.10%29.aspx