there is basic misunderstanding.... MS Access has TWO separate object models.
1. Access Object Model - helps you to open forms and reports, .....
2. Data Object Models (ADO and DAO) - manipulates tables and queires
I don't want to talk about number 1.
You can manipulate data and queires using ADO or older DAO. ADO is very very universal solution and I really recommend it. It has two separate DLLs - ADODB (for data manipulation - read data, update data, ....) and ADOX for data definition (create field, create index, ....). It's little bit more confusing: you can use ADODB for data definition (if you use pure SQL and DDL - data definition language CREATE TABLE, ....). But remember if you want to dig for data use ADODB, if you want to create new column, table or index, use ADOX.
ADO is universal top layer for OLE DB connectors. You can use ADO for almost everything and thanks to OLE DB for ODBC drivers, you can use it also for ODBC.
ADO has one very small problem. It goes ANSI way, so it supports STORED PROCEDURES and VIEWS and NOT queires. So if you need to create query or read query, use DAO. By query I mean really query stored in MDB file, not SQL statement (something like select * from mytable).
I can see your case as classic ADO problem. You work with Excel and you need to insert some values to existing MDB table. Forget whole MS Access and use pure ADO with JET OLE DB. You can insert something to MDB table even without installing Access on that PC.
what you need to do is to open connection to MDB file and execute your SQL statement (I guess).
Dim ADOComm As New ADODB.Command
Dim myConnection as String
Dim mySQL as string
there are many different solutions, but the easiest non-programmer way would be to forget whole Access and use pure Excel techniques...
if you go to Data-Get External Data-New Database Query you can get any datasouce....
now, you can do the same with QueryTables.Add method ....
so turn on RECORDER (tools-macro-record new macro) then go to Data-GetExternal Data-New Database Query and import your data, then stop the recording and go to VBA and check what Excel created..... you can use it and import any data using SQL.... anytime.