Cell value as parameter for VBA access query

[Gegle]

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

 

[MattShoreson]

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.

 

[Gegle]

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?

 

[Gegle s]

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?

 

[MattShoreson]

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