SQL statement returns zero records
 

Can someone tell me what is wrong with my procedure here?
When I try to access this from my Access database, I get
zero records returned.

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 * FROM qryPlantProductLine WHERE
PlantName = '" & strPlantName & "' "
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 Recordset.MaxRecords
'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

SQL statement returns zero records
 

Eric

Try this

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

Recordset.CursurLocation = adUseClient

With Recordset
'Filter
'Src = "SELECT * FROM qryPlantProductLine WHERE


Also, you should consider different variable names than Recordset and
Connection.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com