Excel data to Access Macro(Runtime 3265)

[tsweeney]

I used one of the examples floating around here to attempt to begin using an Excel front-end, Access back-end system. However, I'm running across an issue with a Runtime 3265 error.

I can take the sample file provided from the tutorial site and the macros work absolutely perfectly, because I found all of the necessary references and such. I can even just go in there and change some parameters and it will still work in that excel file.

However, I tried applying the same exact code (copy-pasta from one module to the other, but with a different target_DB[first line of the code])

It even creates the table correctly with the correct datatypes for each column in the new DB.

My issue is the enlarged portion of the code. When I go to run the PushTabletoAccess Macro, it bombs out at that specific area, giving me a runtime 3265 error with the description "Item cannot be found in the collection corresponding to the requested name or ordinal.

I am at a complete and total loss as to why it would work fine with one set of data but completely bomb out with a different set of data.

Option Explicit
Const TARGET_DB = "Poured Paint.mdb"
Sub CreateDB_And_Table()
  
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sDB_Path As String
    
    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    'delete the DB if it already exists
    On Error Resume Next
    Kill sDB_Path
    On Error GoTo 0
    
    'create the new database
    Set cat = New ADOX.Catalog
    cat.Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDB_Path & ";"
      
 
     
       
    'create the table
    Set tbl = New ADOX.Table
    tbl.Name = "tblPouredPaint"
    tbl.Columns.Append "Date", adDate
    tbl.Columns.Append "Shift", adVarWChar, 60
    tbl.Columns.Append "Color Name", adVarWChar, 60
    tbl.Columns.Append "Batch Number", adDouble
    tbl.Columns.Append "Quantity", adDouble
    tbl.Columns.Append "Tote Number", adDouble
    cat.Tables.Append tbl
    
    Set cat = Nothing
    
End Sub
Sub PushTableToAccess()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long
    Dim Rw As Long
    
    Sheets("Data").Activate
    Rw = Range("A65536").End(xlUp).Row
    
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="tblPouredPaint", ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable
    
    'Load all records from Excel to Access.
    For i = 2 To Rw
        rst.AddNew
       [COLOR=black] [/COLOR][SIZE=4][COLOR=black]For j = 1 To 7
            rst(Cells(1, j).Value) = Cells(i, j).Value[/COLOR][/SIZE]
        Next j
        rst.Update
    Next i
    
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

 

[tsweeney]

Ok, I figured this one out on my own as well.

Apparently, I had the table that was being created with one name "Color Name" instead of Color like it was in my Excel file. Once I fixed all of that, everything worked like a charm.

Guess it wasn't much use to ask on here when I made such a dumb oversight.