Pass-through Delete Query

[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.CreateQueryDef("")
strConnect = "Driver=SQL SERVER;SERVER = NTSQLSERVER;Database=TEST;UID=mlp;password='';"
qdfPassThrough.Connect = "ODBC;" & strConnect
qdfPassThrough.SQL = "DELETE FROM tempDefTable"
qdfPassThrough.Execute

Please help me

Thanks
Jambai

[ADezii]

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.CreateQueryDef("")
strConnect = "Driver=SQL SERVER;SERVER = NTSQLSERVER;Database=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:

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