Getting records from Access 97 to Excel 97

[Eric]

Hi everyone,

In my code below, I am accessing a Access 97 database to
grab some records and place these in my Excel 97
spradsheet. When the code is ran, I am getting back
heading names in my SQL statement, but no data. I have
checked the SQL statemnt in Access and I get data
returned, but not while in Excel. This one has me stumpted.

Here's the code from Excel VBE:

Sub PlantProductLines()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim Row As Integer

Dim strPlantName As String

'Database Information
DBFullName = "J:\QA\QAMaster\QAMaster.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create RecordSet
strPlantName = ActiveSheet.Range("L4").Value
Set Recordset = New ADODB.Recordset

With Recordset
'Filter
Src = "SELECT qryPlantProductLine.PlantName,
qryPlantProductLine.LineCode, qryPlantProductLine.LineName
FROM `J:\QA\QAMaster\QAMaster`.qryPlantProductLine
qryPlantProductLine WHERE
(qryPlantProductLine.PlantName='Leola')"
.Open Source:=Src, ActiveConnection:=Connection
Debug.Print .MaxRecords
Debug.Print .RecordCount

'Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("P12").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
MsgBox Recordset.RecordCount
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

 

[merjet]

When the code is ran, I am getting back
heading names in my SQL statement, but no data.

After the code that writes the field names, you need another
line of code to get the data, something like this:
Range("P12").Offset(1, 0).CopyFromRecordset Recordset

For further info, see:
http://www.erlandsendata.no/english/index.php?d=envbadacimportado

HTH,
Merjet

 

[Guest]

But when call for the number of Records (Debug.Print
Recordset.MaxRecords), shouldn't I get how many records
there are? I'm not even getting that.

 

[merjet]

But when call for the number of Records (Debug.Print

Recordset.MaxRecords), shouldn't I get how many records
there are? I'm not even getting that.

I believe that with a query (but not a table) you would have
to move to the last record to get the correct record count.

HTH,
Merjet