Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Building dynamic SQL (2003)

    I had this working previously, but now I'msure I've diong something basically wrong, though I can't see it.

    In an effort to improve performance, when retrieving data from our AS/400 server, I created appropriate pass-through queries. but these would need editing each time they're run. So I'll do it with code. Have done so before.

    Anyway, there's one particular variable (strMax) which is based on a query on an internal table. Previously, this would give make variable the query result. Now however, it just puts in the SQL.

    Here's the code. What am I doing wrong???



    Dim EIA4Database As DAO.Database, EIA4QueryDef As DAO.QueryDef
    Dim EIA4QueryName As String
    Dim EIA4SQLString As String
    Dim i As Integer
    Dim strMax As String

    'gets the last record from the target table
    strMax = "SELECT Max([DateChange]-1) AS Change FROM tblEIA4REP ORDER BY Max([DateChange]-1) DESC;"

    Set EIA4Database = CurrentDb()
    EIA4QueryName = "pqryEIA4REP"

    'Test for the existance of ppqryEIA4REP in Database. Delete object if exists,
    'and refresh the Querydefs Collection

    If ObjectExists("Queries", EIA4QueryName) = True Then
    EIA4Database.QueryDefs.Delete EIA4QueryName
    EIA4Database.QueryDefs.Refresh
    End If

    'enters the SQL query
    Set EIA4QueryDef = EIA4Database.CreateQueryDef(EIA4QueryName)

    EIA4SQLString = "SELECT "
    EIA4SQLString = EIA4SQLString & " A4A2NC as CustNo, A4B9NA as CustName, A4JSDT as ChangeDate, A4B6VN as ChangeBy "
    EIA4SQLString = EIA4SQLString & " FROM EISGBRDTA.EIA4REP "
    EIA4SQLString = EIA4SQLString & " WHERE A4JSDT >="
    EIA4SQLString = EIA4SQLString & strMax
    EIA4SQLString = EIA4SQLString & ";"

    EIA4QueryDef.SQL = EIA4SQLString
    EIA4QueryDef.Close
    EIA4Database.QueryDefs.Refresh
    DoCmd.OpenQuery EIA4QueryName
    DoCmd.Close acQuery, EIA4QueryName

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building dynamic SQL (2003)

    You're concatenating with a string containing an SQL statement, so the SQL statement will end up in the result. If you want to have the value of Change in the result, you cannot do it this way. You'd have to use DMax:

    strMax = CStr(DMax("DateChange", "tblEIA4REP") - 1)

    If that is not what you meant, please provide a clearer explanation.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building dynamic SQL (2003)

    The SQL in strMax came from a query on an Access table & used the SQL view so I thought it would work. In an effort to understand <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> the why's & wherefores, can you explain why this is not so?

    TIA


    PS, as ever, Hans your solution was spot on <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building dynamic SQL (2003)

    The SQL string is valid in itself, but if you concatenate with it, you concatenate with the text string, not with the result of the SQL. You cannot assign the result of an SQL string to a variable this way - SQL simply doesn't work that way.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building dynamic SQL (2003)

    I can see what you mean; the SQL for the pass-through is in the same syntax (more or less) as I used for the variable. Would it be better to use actual queries for the varable values or build in to the code?


    Now I'm going to get paranoid; the code created query is being saved as a normal Select query, not a pass-through (I have added the ODBC variable).

    Here's the editied code;

    Dim EIA4Database As DAO.Database, EIA4QueryDef As DAO.QueryDef
    Dim EIA4QueryName As String
    Dim EIA4SQLString As String
    Dim dbODBC As DAO.Database
    Dim ConnectString As String
    Dim i As Integer
    Dim strMax As String

    'make ODBC connection
    ConnectString = "ODBC;DSN=banbury eisdta;"

    'gets the last record from the target table
    strMax = CStr(DMax("DateChange", "tblEIA4REP") - 1)


    Set EIA4Database = CurrentDb()
    EIA4QueryName = "pqryEIA4REP"

    'Test for the existance of ppqryEIA4REP in Database. Delete object if exists,
    'and refresh the Querydefs Collection

    If ObjectExists("Queries", EIA4QueryName) = True Then
    EIA4Database.QueryDefs.Delete EIA4QueryName
    EIA4Database.QueryDefs.Refresh
    End If

    'enters the SQL query
    Set EIA4QueryDef = EIA4Database.CreateQueryDef(EIA4QueryName)

    EIA4SQLString = "SELECT "
    EIA4SQLString = EIA4SQLString & " A4A2NC as CustNo, A4B9NA as CustName, A4JSDT as ChangeDate, A4B6VN as ChangeBy "
    EIA4SQLString = EIA4SQLString & " FROM EISGBRDTA.EIA4REP "
    EIA4SQLString = EIA4SQLString & " WHERE A4JSDT >="
    EIA4SQLString = EIA4SQLString & strMax

    EIA4QueryDef.SQL = EIA4SQLString
    EIA4QueryDef.Close
    EIA4Database.QueryDefs.Refresh
    RefreshDatabaseWindow

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building dynamic SQL (2003)

    You don't do anything with the connection string. You should have

    EIA4QueryDef.Connect = ConnectString

    somewhere near the end.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building dynamic SQL (2003)

    Oops, found it; missed

    EIA4QueryDef.Connect = ConnectString

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building dynamic SQL (2003)

    Cheers Hans

Posting Permissions

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