ADO from Excel to Access

  • Thread starter Thread starter pk
  • Start date Start date
P

pk

Hello, I hope someone can help me please.

I can load an Excel sheet into an ADO recordset. But,
I now need to write the recordset out to an Access table
that already has the appropriately named columns.

Can someone supply me with example code to do this or
point me to a good web site that shows current technology
example code (Excel XP/Windows 2000)?

Thanks in advance for your assistance and example code.
 
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
You don't need a recordset to do this; in fact, using a recordset will
slow down the process.

Because you can connect to both Excel and MS Access using the MS OLEBE
Jet Provider, you can use the INSERT INTO..SELECT syntax, using the IN
keyword to refer to the data source other than the one used for your
ADODB connection object. For example, if you've connection string
refers to your Excel workbook, try something like:

INSERT INTO MyTable IN 'C:\MyJetDB.mdb' SELECT RefID AS KeyCol,
Surname AS ValueCol FROM [PersonalDetails$]

or if your connection object is connected to the MS Access database
use:

INSERT INTO MyTable SELECT RefID AS KeyCol, Surname AS ValueCol FROM
[PersonalDetails$] IN 'C:\MyWorkbook.xls' 'Excel 8.0'
 
Back
Top