Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: SQL Select Statement

    Hi Team,

    My first post so go easy! Also, this is my first crack at VBA and accessing another application.

    I have a word document that links to an access database. The connection is fine and I can extract the necessary data if I hard code the search criteria in the SQL statement.

    However, when I use a variable in the SQL statement I get an error. What I am doing wrong?

    Thanks in anticipation!

    Gary

    Sub GetConnected()
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strConn As String
    Dim strLenderCode As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=Lender_Details.mdb;" + _
    "Persist Security Info=False"

    strLenderCode = "AMP"
    Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset


    rst.Open "SELECT * FROM tbl_Lender_Contact_Details WHERE LenderID = 'ANZ'", strConn
    (when I use this line above, it connects to the database and pulls out the required data)


    (however, if I set the variable strLenderCode to ANZ or AMP etc and use that variable in the select statement, it doesn't work. What am I doing wrong?)
    rst.Open "SELECT * FROM tbl_Lender_Contact_Details WHERE LenderID = strLenderCode", strConn

    Selection.TypeText Text:=rst.Fields(1).Value

    rst.Close
    Set rst = Nothing
    conn.Close

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    its something like this...

    rst.Open "SELECT * FROM tbl_Lender_Contact_Details WHERE LenderID = " & strLenderCode, strConn

    inside the qoutes is a string and strLenderCode is a string and you need to concatenate the two. I believe & is the VBA concatenation operator for strings but I do not have MS Office in front of me at the moment and I am very sleepy in the head. I bet Google knows...
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    or.....go to the sql server forum (or access)...

    And you stored procedures instead?

    Just a thought
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Useful contribution Brett

    I'm going to move this thread to the Access topic as I feel it would be better suited there.

    P.S. welcome to the forum
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as LenderID is a string variable (based on WHERE LenderID = 'ANZ'",) then you need to encapsulate the parameter with quote marks

    Code:
    rst.Open "SELECT * FROM tbl_Lender_Contact_Details WHERE LenderID = '" & strLenderCode & "'", strConn
    that assumes that you have a value in strLenderCode
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2009
    Posts
    2
    Thanks all for your response - great work! The solution was WHERE LenderID = '" & strLenderCode & "'", strConn

    Thanks again!

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    when I doing this what I do

    just make it easyer reading

    SQL = "SELECT * FROM tbl_Lender_Contact_Details WHERE LenderID = '[strLenderCode]'"
    NEWSQL = Replace(SQL,"[strLenderCode]",strLenderCode)

    rst.Open NEWSQL, strConn
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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