Hi,

I have set of values in a worksheet.
I have written VBA code for access database connection through ADODB.
both excel and access are 2003 version
my query will be in a loop, and it takes parameters from the worksheet
for each run within the loop.

my query looks like:
for i=1 to 10
x=sheets(1).range("A"&i)
Select * from table1 where id=x;
next

I was not able to represent the parameter as variable or even as cell
reference.
only empty recordset is returned.

Can anyone help me in this, please...

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

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

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

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

If a give an actual value to the query parameter, then the code works
fine.

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



*** Sent via Developersdex http://www.developersdex.com ***

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