Hi,
Please look at my code:

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

cstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\CovB.mdb;"

Set cn = New ADODB.connection
cn.Open cstring
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

For i = 1 To 10

contract=worksheets(1).range("A"&i)
qstr="Select CONTR_NBR, COV_TYPE, BENEFIT From CB Where
(CONTR_NBR=contract)"
rs.Open qstr, cn, adOpenStatic, adLockOptimistic

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

Next
ThisWorkbook.Save
rs.Close
cn.Close

End Sub

Here when I assign a value of an excel cell to a variable(contract), it
is showing an error as 'automation error'

Is there any other way to specify the excel cell value as parameter to
query?

MattShoreson wrote:
Where abouts are you getting your error?

If you hard-code the x as 1, does it return records?
If so then you aren't populating x properly.

for i = 1 to 10
x= sheets("Sheet1").cells(i,1)
Select.......id=x;
next i

However, if the hard-coded SQl isnt working then it's most likely a
connection problem.

And in that case.... you'll need to post more detail.

HTH,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=486866