Pass-through Delete Query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JAMBAI
    New Member
    • Feb 2007
    • 28

    #1

    Pass-through Delete Query

    am trying to delete records in the SQL Server from MS Access forms. I got error 3065 cannot execute a select qury, when trying the below code.

    Dim qdfPassThrough As DAO.QueryDef
    Dim rstPassThrough As DAO.Recordset
    Dim strConnect As String
    Dim dbCurrent As DAO.Database
    Set qdfPassThrough = CurrentDb.Creat eQueryDef("")
    strConnect = "Driver=SQL SERVER;SERVER = NTSQLSERVER;Dat abase=TEST;UID= mlp;password='' ;"
    qdfPassThrough. Connect = "ODBC;" & strConnect
    qdfPassThrough. SQL = "DELETE FROM tempDefTable"
    qdfPassThrough. Execute

    Please help me

    Thanks
    Jambai
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by JAMBAI
    am trying to delete records in the SQL Server from MS Access forms. I got error 3065 cannot execute a select qury, when trying the below code.

    Dim qdfPassThrough As DAO.QueryDef
    Dim rstPassThrough As DAO.Recordset
    Dim strConnect As String
    Dim dbCurrent As DAO.Database
    Set qdfPassThrough = CurrentDb.Creat eQueryDef("")
    strConnect = "Driver=SQL SERVER;SERVER = NTSQLSERVER;Dat abase=TEST;UID= mlp;password='' ;"
    qdfPassThrough. Connect = "ODBC;" & strConnect
    qdfPassThrough. SQL = "DELETE FROM tempDefTable"
    qdfPassThrough. Execute

    Please help me

    Thanks
    Jambai
    Here is code that I use which dynamically creates a Pass Through Query given a DSN and an ODBC Connection. After creation, the code maximizes the Query Window. Hope this helps:
    Code:
    Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
    Dim strConnect As String
    
    If Not IsNull(CurrentDb.QueryDefs("qrySQLPassThroughTwo").SQL) Then    'doesn't exist
      CurrentDb.QueryDefs.Delete "qrySQLPassThroughTwo"
    End If
    
    Set MyDB = CurrentDb()
    
    Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPassThroughTwo")
    
    strConnect = "DSN=SQL Server;Database=Pubs;UID=sa;PWD="
    qdfPassThrough.Connect = "ODBC;" & strConnect
    qdfPassThrough.SQL = "SELECT * From Authors_2;"
    qdfPassThrough.Close
    
    Application.RefreshDatabaseWindow
    
    DoCmd.OpenQuery "qrySQLPassThroughTwo", acViewNormal, acReadOnly
    DoCmd.Maximize

    Comment

    Working...