It's the where clause in the SQL statement.
Use...
(If CONTR_NBR is a number) Where CONTR_NBR=" & contract
(If CONTR_NBR is text) Where CONTR_NBR='" & contract &"'"
So it should be...
Sub squery()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cstring As String
Dim qstr, contract As String
Dim intRow As Integer
Dim DBasePath As String
Dim I
cstring = "C:\CovB.mdb"
Set cn = New ADODB.Connection
With cn
..Provider = "Microsoft.JET.OLEDB.4.0"
..Open cstring
End With
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
For I = 1 To 10
contract = Sheets(1).Range("A" & I)
qstr = "Select CONTR_NBR, COV_TYPE, BENEFIT From CB Where CONTR_NBR=" &
contract
rs.Open qstr, cn
Do Until rs.EOF
intRow = intRow + 1
Sheets(2).Cells(intRow, 1) = rs.Fields("CONTR_NBR")
Sheets(2).Cells(intRow, 2) = rs.Fields("COV_TYPE")
Sheets(2).Cells(intRow, 3) = rs.Fields("BENEFIT")
rs.MoveNext
Loop
rs.Close
Next
ThisWorkbook.Save
cn.Close
End Sub
--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread:
http://www.excelforum.com/showthread...hreadid=486866