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. It is grabbing
my crosstab queries in Access and pulling them into Excel. I want to
use the queries I created in Access, I don't want to re-create another
query.

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.

Hello,
found the answer. if anyone needs it, here it is. It will also paste in
headers. "Test" is the name of the worksheet inside the Excel workbook.

Sub IMPORT_test4()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer

'Connect to your Access
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=P:\Service\SIRS.accdb;"
cn.Open

'Create your recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM qryAllIncidents;", cn, adOpenKeyset, adLockReadOnly,
adCmdText



Worksheets("test").[a2].CopyFromRecordset rs

'Add to your current workbook and add the field names as column headers
For Counter = 0 To rs.Fields.Count - 1
Worksheets("test").[a1].Offset(0, Counter).Value = rs.Fields(Counter).name
Worksheets("test").[a1].Offset(0, Counter).Font.Bold = True
Next Counter


'Clean up Objects
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Sub IMPORT_test4()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer

'Connect to your Access
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=C:\Documents and Settings\mycomputer\Desktop\SIRS TTO.accdb;"
cn.Open

'Create your recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM qryAllIncidents;", cn, adOpenKeyset, adLockReadOnly,
adCmdText

'Add to your current workbook and add the field names as column headers
(optional)


Worksheets("test").[a2].CopyFromRecordset rs

For Counter = 0 To rs.Fields.Count - 1
Worksheets("test").[a1].Offset(0, Counter).Value = rs.Fields(Counter).name
Worksheets("test").[a1].Offset(0, Counter).Font.Bold = True
Next Counter


'Clean up ADO Objects
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


"mp80237" wrote:

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. It is grabbing
my crosstab queries in Access and pulling them into Excel. I want to
use the queries I created in Access, I don't want to re-create another
query.

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.