title: Populate Access table from Excel worksheet
body: <p>I found some code online that exports my Excel data to an Access table. The problem is that it creates the headers correctly but does not populate the data at all. How do I tell Excel to go through the data and export those fields?</p>
  
  <p>Here's the code:</p>
  
  <pre><code>Sub Z_CreateTable()
  
  'If this subroutine throws errors, remember to go to:
  'Tools &gt; References and select 'Microsoft ActiveX Data Objects #.# Library'
  
  Dim dbConnectStr As String
  Dim Catalog As Object
  Dim cnt As ADODB.Connection
  Dim dbPath As String
  Dim strSQL As String
  Dim rngDB As Range
  Dim wbPath As String
  Dim stSQL As String
  Dim strTable As Object
  
  'Set database name here
  dbPath = "T:\Projects\testdata1.mdb"
  dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; dbPath &amp; ";"
  
  'Create new database
  Set Catalog = CreateObject("ADOX.Catalog")
  Catalog.Create dbConnectStr
  
  'Connect to database and insert a new table
  Set cnt = New ADODB.Connection
  cnt.CursorLocation = adUseServer
  With cnt
     .Open dbConnectStr
     .Execute "CREATE TABLE tblSample ([FIELD1] text(50) WITH Compression, " &amp; _
               "[NAME] text(150) WITH Compression, " &amp; _
               "[BLANK] text(10) WITH Compression, " &amp; _
               "[CLASSID] text(10) WITH Compression, " &amp; _
               "[TYPE] text(5) WITH Compression, " &amp; _
               "[FIELD2] text(5) WITH Compression, " &amp; _
               "[FIELD3] text(5) WITH Compression, " &amp; _
               "[FIELD4] text(15) WITH Compression, " &amp; _
               "[START YEAR] text(15) WITH Compression, " &amp; _
               "[END YEAR] text(10) WITH Compression)"
  
  End With
  Set cnt = Nothing
  
  End Sub
  </code></pre>