Pass-through Delete Query

[jambai]

I 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

 

[AlexCuse]

Maybe this?

DELETE tempDefTable FROM tempDefTable

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jambai]

Nope,

I got the same error message.

I tried your reply at

http://www.tek-tips.com/viewthread.cfm?qid=1341871&page=8

too, it doesn't help me out.

Thanks
jambai

 

[AlexCuse]

You have a stray quote here:

password='';"

I think you mean to just leave it blank?

You might need to use ADO rather then DAO, but I am not sure about this.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JerryKlmns]

Open a ADO connection to your SQL server, and execute your SQL delete statement!

http://www.connectionstrings.com/?carrier=sqlserver

Dim SQLCnn as ADODB.Connection

Set SQLCnn= New ADODB.Connection
With SQLCnn
   .ConnectionString ="..."
   .Open
   .Execute "DELETE FROM tempDefTable",,129
   .Close
End With
Set SQLCnn = Nothing