E
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
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