+ Reply to Thread
Results 1 to 5 of 5

Cell value as parameter for VBA access query

  1. #1
    Gegle
    Guest

    Cell value as parameter for VBA access query

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


  2. #2
    Forum Contributor
    Join Date
    12-04-2003
    Posts
    360
    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.

  3. #3
    Gegle
    Guest

    Re: Cell value as parameter for VBA access query

    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



  4. #4
    Gegle s
    Guest

    Re: Cell value as parameter for VBA access query

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

  5. #5
    Forum Contributor
    Join Date
    12-04-2003
    Posts
    360
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1