Populate Access table from Excel worksheet

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?

Here's the code:

Sub Z_CreateTable()

'If this subroutine throws errors, remember to go to:
'Tools > 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=" & dbPath & ";"

'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, " & _
             "[NAME] text(150) WITH Compression, " & _
             "[BLANK] text(10) WITH Compression, " & _
             "[CLASSID] text(10) WITH Compression, " & _
             "[TYPE] text(5) WITH Compression, " & _
             "[FIELD2] text(5) WITH Compression, " & _
             "[FIELD3] text(5) WITH Compression, " & _
             "[FIELD4] text(15) WITH Compression, " & _
             "[START YEAR] text(15)开发者_如何转开发 WITH Compression, " & _
             "[END YEAR] text(10) WITH Compression)"

End With
Set cnt = Nothing

End Sub

See: asp and ms-access db - how to import data from xls file You can update from Excel using fairly standard SQL without first creating a table and without iterating through rows, which is very slow.

Have you tried importing from the Access database? You can create your own table first and that gives you more control over the field data types and indexes.

It is true that this code will only create the table. To insert the records, you'll need to follow this up with an Execute that uses "INSERT INTO tblSample" and then each Excel row.

I don't know how to do this in VBA, but I could do it in VB using a For Each row In {table}.Rows...Next statement, so maybe Googling For Each syntax in VBA would help you. Or maybe someone here can actually give you a good example.

EDIT: Something close to this might work

For i = startRow to endRow
     cnt.Execute "INSERT INTO tblSample VALUES (" & _
                  Cells(i, 1).Value & "," & Cells(i, 2).Value & _
         --And so on
         & ")"
Next i

You'd have to set the values for the start and end rows. This loop would go through each row in your range and insert the value of the cell in that row into the field you specify. You'll need to make the order the same as the columns in your table, so you might have Cells(i, 2).Value first, for instance.

Try executing it immediately after your With block, before the Set cnt = Nothing.

EDIT: Using SELECT INTO

.Execute "SELECT * INTO tblSample FROM "[{Your version of Excel};
          DATABASE={Your Excel spreadsheet}].[{Your sheet name}]"

Replacing your existing .Execute block.