Results 1 to 4 of 4

Thread: MS Access error

  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: MS Access error

    I have create a module which contains a function which creates a query.

    The syntax is
    Function TSCheckExport()

    On Error GoTo TSCheckExport_Err
    Dim qdf As QueryDef
    Dim strSQL As String

    strSQL = "execute procedure rsp_turret_steel_wells_fargo_create_check_file('" & ([Forms]![frm_WellsFargoCheckDate]![CheckDate]) & "')"

    MsgBox strSQL, vbOKOnly, ""

    Set qdf = CurrentDb.CreateQueryDef("NewQuery", strSQL)
    qdf.Close

    TSCheckExport_Exit:
    Exit Function

    TSCheckExport_Err:
    MsgBox Error$
    Resume TSCheckExport_Exit

    End Function



    When I paste the SQL into a new query window and save...no errors.

    When I run the function I receive the error Invalid SQL Statement; expected delete insert procedure select or update.

    Any ideas?

    Thanks
    John

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I suspect when you do it manually you're creating a pass-through query. Personally, I'd change the SQL of an existing query rather than creating a new one every time.
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I agree with pbaldy. Try:
    Code:
    strConnect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;
    Set qdf = CurrentDb.CreateQueryDef("NewQuery")
    With qdf
        .Connect = strConnect
        .SQL = strSQL
        .Close
    End With
    Have a nice day!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I thought you agreed with me?

    I thought CreateQueryDef would create a new query. I do:

    Set qdf = db.QueryDefs("ExistingQueryName")
    Paul

Posting Permissions

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