My issue is with Excel 2007 and Access 2007, the examples provide are
previous versions. Also, my queries have already been built in Access as
crosstab queries, and I don't want to import as a pivot. Don't want to go
into details as why, but want to keep existing query in access. Thank you
for the help.

M

"Ron de Bruin" wrote:

Maybe this page that use ADO will help
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mp80237" wrote in message ...
Hello, we currently upgraded to 2007 from 2000. I had a great vb code that I
loved and seems to no longer work. It even added the field names. I know I
am to do a ADODB connection. I have already added the requested reference.
But this is not working. Current VB script is below. My my new database is
SIRS.accdb and when I changed the name it does not work.

Sub IMPORT_ACCESS_INCIDENT()

Dim db As DAO.Database, qry As DAO.QueryDef, rec As DAO.Recordset

Dim ST As String
Dim i As Integer
Dim R As Long
Dim C As Integer


'set path to database
Path = "P:\Service\SIRS.mdb"

'now get data from Access
Set db = DBEngine.Workspaces(0).OpenDatabase(Path) ' Open database

'get data for Total Incidents
Set qry = db.QueryDefs("Q-Incidents")
Set rec = qry.OpenRecordset
Sheets("Summary").[c2].CopyFromRecordset rec

For Counter = 0 To rec.Fields.Count - 1
Worksheets("Summary").[c1].Offset(0, Counter).Value =
rec.Fields(Counter).Name
Worksheets("Summary").[c1].Offset(0, Counter).Font.Bold = True
Next Counter

End Sub

Please help.