title: Excel VBA to Create SQL Table
body: <p>I have found the following code which is supposed to create an SQL table within an SQl Database.</p>
  
  <p>The database is specified in <code>dbpath</code> and the table to be created is in <code>tblname</code>.</p>
  
  <p>However when I run this code I have a problem connecting to right SQL database.</p>
  
  <p>For example if I specify <code>dbpath</code> as "WIN2k8\Test\ABC"  ie the machine name is WIN2k8, sql instance is Test database is ABC.  </p>
  
  <p>For some reason it keeps picking up the dbpath as where I have saved the Excel workbook then WIN2k8\test\abc.</p>
  
  <p>Can somebody help?</p>
  
  <pre><code>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=" &amp; dbPath &amp; ";"  
  
  '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, " &amp; _  
               "[RTNumber] text(9) WITH Compression, " &amp; _  
               "[AccountNumber] text(10) WITH Compression, " &amp; _  
               "[Address] text(150) WITH Compression, " &amp; _  
               "[City] text(50) WITH Compression, " &amp; _  
               "[ProvinceState] text(2) WITH Compression, " &amp; _  
               "[Postal] text(6) WITH Compression, " &amp; _  
               "[AccountAmount] decimal(6))"  
  End With  
  Set cnt = Nothing  
  </code></pre>