Jamie,

I am trying to do something similar, I have an access DB that contains
employee information. I need to make a user form in Excel that manipulates
the data in the Access DB. I can handle making the userform and the Access
DB, just wondering if you knew how I would go about connecting and executing
some DML.

thanks,
--
Ben Sullins


"Jamie Collins" wrote:

"Norm" wrote ...

I didn't think of using
the DoCmd to get the TransferSpreadsheet.

I think your instinct was correct. IMO, automating the MS Access app
merely to access data is lousy advice. Using ADO is much more
appropriate.

Surely there's something better to use than opening an
ADO recordset and then writing each record.

Indeed. Open an ADO Connection and execute either an INSERT
INTO..SELECT or a SELECT..INTO command. For example:

For an existing table:

INSERT INTO
[Database=C:\MyDB.mdb;].MyExistingTable
(MyCol1, MyCol2, MyCol3)
SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
FROM
[Sheet1$];

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
[Database=C:\MyDB.mdb;].MyExistingTable
FROM
[Sheet1$];
;

Jamie.

--