Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Posts
    28

    Unanswered: Simple Pass Through query not display data...

    Hello All - I hv return the follwing code for a button in a form:

    :quote:
    Option Compare Database
    Option Explicit

    Private Sub InputForARTS_Click()
    Dim strSQL As String
    Dim strQueryName As String
    Dim strConnection As String
    Dim dtmORDDate As Date

    strSQL = "Select * from Factoryorder"
    strQueryName = "GetArtsLpbdEpbd"
    strConnection = _
    "ODBC; DSN=connect-Round;UID=uuu;PWD=pppp;SERVER=ODOW;"
    Call CreateSPT(strQueryName, strSQL, strConnection)

    End Sub

    Function CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    Set mydatabase = DBEngine.Workspaces(0).Databases(0)
    mydatabase.QueryDefs.Delete SPTQueryName
    Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
    myquerydef.Connect = ConnectString
    myquerydef.SQL = SQLString
    myquerydef.ReturnsRecords = True
    myquerydef.Close
    End Function
    :unquote:

    I am expecting to display data from FactoryOrder Tale from Oracle when I press button in Form. It doesn't. Where am I doing wrong?

    Thanks a ton in advance...

    Sunil

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    All your code does is create a Pass Thru query...nothing more. If you want to do something like display the data you will actually need to run the query that you create.

    C

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    In your subprocedure try something like

    myquerydef.execute

    After you define/create it, but before you close it.

    Check out Allen Browne's Cheatsheet on DAO for more info.
    Me.Geek = True

  4. #4
    Join Date
    Apr 2005
    Posts
    28
    This is what I added (Bold Chars)
    .
    .
    .
    myquerydef.SQL = SQLString
    myquerydef.ReturnsRecords = True
    myquerydef.Execute
    myquerydef.Close
    End Function

    Now when I press the Form Button, It does nothing... What else I might be missing? Somewhere somebody told that for a select pass-through query I have to use the 'recordset'. I don't know how to use it if I have to use it.

    I want data to be displayed on screen after pressing the button.

    Please help...

    Thanks

    Sunil

    Please help...

  5. #5
    Join Date
    Apr 2005
    Posts
    28

    Gurus Please help...

    This is my current version of VBA code in MS Access:

    :quote:
    Option Compare Database
    Option Explicit

    Private Sub InputForARTS_Click()
    Dim strSQL As String
    Dim strQueryName As String
    Dim strConnection As String
    Dim dtmORDDate As Date

    strSQL = "Select * from Factoryorder"
    strQueryName = "GetArtsLpbdEpbd"
    strConnection = "ODBC;DSN=Test-HEX;UID=uuu;PWD=ppp;SERVER=sss;"
    Call CreateSPT(strQueryName, strSQL, strConnection)

    End Sub


    Function CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    Set mydatabase = DBEngine.Workspaces(0).Databases(0)
    mydatabase.QueryDefs.Delete SPTQueryName
    Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
    myquerydef.Connect = ConnectString
    myquerydef.SQL = SQLString
    myquerydef.ReturnsRecords = True
    myquerydef.Execute
    myquerydef.Close
    End Function
    :unquote:

    When I run the above module I get the following error:

    "Run-time error '3065' Cannot execute a select query."

    The how do I this simple pass through select query?


    This what I am doing:
    1) created a form w/ one command button
    2) Created above code w/ Build Event for that button
    3) trying to execute this by clicking the busston and expecting that it will display the selected data on my screen

    Am I missing something in the above steps/process?

    Please help...


    Thanks

  6. #6
    Join Date
    Apr 2005
    Posts
    28
    someone please help...

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Why not just use

    Docmd. runsql strSQL

    ?
    Me.Geek = True

  8. #8
    Join Date
    Apr 2005
    Posts
    28
    I am not very much familiar w/ VBA code and specially w/ pass through query. I am just doing based on all suggestions from experts like you. No one told me to use 'Docmd. runsql'. So I did not use.

    Could you please let me know where all I need to modify so that this query can run on Oracle side and display output on my screen?

    Thanks for your help...

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Take out the execute line from your function. Then after the line "Call CreateSPT(strQueryName, strSQL, strConnection)", do like:

    DoCmd.OpenQuery strQueryName, acViewNormal, acReadOnly

    The runsql method works for just a regular query, I think this may work better for you though. give it a shot.
    Me.Geek = True

  10. #10
    Join Date
    Apr 2005
    Posts
    28
    Hello NCKDRYR - Your suggestion worked fantastically. And displayed the data on my screen.

    To take it to the next level, I am inputting from user and using that to create query. As follows:

    :quote:
    Option Compare Database
    Option Explicit

    Private Sub InputForARTS_Click()
    Dim strSQL As String
    Dim strQueryName As String
    Dim strConnection As String

    Dim dtmLaneID As String
    dtmLaneID = InputBox("Enter Lane ID (Lane1/Lane2.Lane3)")


    strSQL = " Select * FROM UD_LANE_POLICY WHERE LANE = " & dtmLaneID & ""
    strQueryName = "GetArtsLpbdEpbd"
    strConnection = "ODBC;DSN=DDDD;UID=UUUU;PWD=PPPP;SERVER=SSSS;"
    Call CreateSPT(strQueryName, strSQL, strConnection)
    DoCmd.OpenQuery strQueryName, acViewNormal, acReadOnly

    End Sub


    Function CreateSPT(SPTQueryName As String, SQLString As String, ConnectString As String)
    Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    Set mydatabase = DBEngine.Workspaces(0).Databases(0)
    mydatabase.QueryDefs.Delete SPTQueryName
    Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
    myquerydef.Connect = ConnectString
    myquerydef.SQL = SQLString
    myquerydef.ReturnsRecords = True
    'myquerydef.Execute
    myquerydef.Close
    End Function
    :unquote:

    Now when I press Form Button, I get this error:
    :quote:
    Run-time error '3146'
    ODBC-- call failed.
    [Microsoft]ODBC drive for Oracle][Oracle]Ora-00904:"LANE1":
    invalid identifier (#904)
    :unquote:

    the 'LANE1' was the user input...

    I think when we pass any parameter to the passthrough query, simple DoCmd.OpenQuery may not work.

    Any other suggestion please?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding your ODBC error:

    You currently have your strSQL performing an integer type match (using just the ") but your dtmLaneID variable is dimmed as a string value! This is what is giving you the ODBC error! Either dim dtmLaneID as a variant/integer or change your strSQL statement (if it's 'Lane1' and not just 1 then change your strSQL).

    If it is indeed a string value, change your strsql to:

    strSQL = " Select * FROM UD_LANE_POLICY WHERE LANE = '" & dtmLaneID & "'"

    (note the LANE = ' " & dtmLaneID & " ' " at the end.)

    Remember for strSQL criteria matching:
    " => integer type matches
    ' " => string type matches
    # " => date type matches

    (You may also want to test dtmLaneID for a blank or other value that you don't want the user to input before creating strSQL.)

    Personally, I'd probably just create a listbox or combobox with the values to select from and then base your strSQL against the listbox or combobox: ie.

    strSQL = " Select * FROM UD_LANE_POLICY WHERE LANE = ' " & Forms!MyFormName!MyComboboxName & " ' "

    and the combobox would be an unbound field on the form where the rowsourcetype is a List Values and then the rowsource are the values (ie. "Lane1";"Lane2";"Lane3") with the limit to list = true (and I'd also test to make sure MyComboboxName is not blank before creating the strSQL.)
    Last edited by pkstormy; 11-04-09 at 23:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2009
    Posts
    1

    How can this be used to create a pass-through query but not return and records?

    I have used this code to create pass-through queries that are very customizable. What I cam trying to figure out is can this code be changed to create the pass-through query without return any records? Every time I change the query to return no records, the code resets it to return records. I want to create a temp table on SQL Server and then link it to a few other tables.

    Thanks,

    Brock

Posting Permissions

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