Append data to Access - Runtime error 3270

[minette]

Hi all, I have the following code which appends 3 excel ranges to 3 different Access tables, and works perfectly in a normal Access linked table. However, when I try it on an Access Table linked to DB2, I get a runtime error 3270 "Property not found" when trying to append the data to the AGY table. Both of the other two tables works fine.

PS: I set the Access_DB and Excel_Path variables elsewhere. Also, the reason why I set oApp to visible, is so that I can enter the username and password when connecting to DB2, and this is also working correctly.

Does anyone know why this is not working for me?

Sub ACCESS_APPEND()
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    oApp.OpenCurrentDatabase Access_DB
    
    DoCmd.RunSQL "DELETE CRC.* FROM CRC"
    DoCmd.RunSQL "DELETE MTM.* FROM MTM"
    DoCmd.RunSQL "DELETE AGY.* FROM AGY"
    
    'Does not work on this line - this table holds roughly 40000 records (with 4 fields)
    oApp.DoCmd.TransferSpreadsheet acImport, 8, "AGY", Excel_Path, True, "AGY_Range"
    
    'Works on this line - this table holds roughly 500 records (with 3 fields)
    oApp.DoCmd.TransferSpreadsheet acImport, 8, "MTM", Excel_Path, True, "MTM_Range"
    
    'Works on this line - this one holds roughly 80 records (with 5 fields)
    oApp.DoCmd.TransferSpreadsheet acImport, 8, "CRC", Excel_Path, True, "CRC_Range"
    oApp.Quit
    Set db = Nothing
End Sub

 

[minette]

Hi everyone, just wondering if anyone were able to figure out why this code is not working correctly? I don't know what else to try.....

 

[RoryA]

Can you run exactly the same command but targeting a local table, to test? If that works, you may need to do the import that way, then run an append query to the linked table.

 

[RoryA]

And just to check, how is AGY_Range defined?

 

[minette]

Hi Rory - thanks for responding. I knew I could count on you to have a look.

I'm not very good on Access at all (hence why I do everything from Excel). How do you run a command to target a local table? Also, (if that does work) will I have to set up the Append Query manually in Access, or can I do it programmatically? I know once it's set up in Access I can run it from Excel programmatically.

The AGY_Range is defined in exactly the same way as the other two (CRC_Range and MTM_Range).

I'm doing a MS Excel VBA course in London in a few months time, so hopefully I'll be able to answer many of my own queries from then....

 

[RoryA]

You would need to create a table in the db with the same fields as your linked table and then run the code. Have you double-checked that the field names and types match between your workbook and the table? I assume the range includes column headers?

 

[minette]

Hi Rory - yes, all field names and types are exactly the same. Triple checked it, as that was the first thing I thought it might be. Since all three tables are similar, and two are working, could it have anything to do with having so many rows to append? I know it works when appending it to the normal linked table, but perhaps appending it to a DB2 linked table has some restrictions?

 

[RoryA]

It's possible - that's partly why I suggested trying it with a local table. if that doesn't work either, then there may be a row count issue.

 

[minette]

OK, I'll try that and post back....