run pass through query to delete records on sql server from VB

Is there a quick way to run a pass through to delete records, like the execute command...
CurrentDb.Execute "accessquerytodelete"
I use the execute to run delete queries (to a linked table) that are saved in access and I have a pass through with the connection string in the properties, but, can't run it with the execute command.

thanks
avoorheisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
You should be able to delete from a linked table just as if it were a true native Access table.
 
There is no need to execute this as a pass-thru query.
0
avoorheisAuthor Commented:
sure, but, it takes forever.
0
jmoss111Commented:
Where does the item come from? Tell me a bit more about this please. I currently do this with a pass through selecting info from a screen and building a delete statement, or you could use an SP with parameter. An yeah, it's a lot quicker.

Jim
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NatchiketCommented:
Hi avoorheis.  I generally run a passthrough query using a temporary querydef, thus:


Dim db As DAO.Database
Dim qdf as DAO.QueryDef
Dim strPTQ as String
Dim strConnect as String
 
strConnect = "ODBC;DSN=" & "your dsn name" & ";UID=" & "your database user ID" & ";PWD=" & "your database password"
 
strPTQ = "DELETE FROM YOURTABLE WHERE etc..."
 
Set db = CurrentDb
 
Set qdf = db.CreateQueryDef("", strPTQ)
qdf.Connect = strConnect
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError
qdf.Close
Set qdf = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
avoorheisAuthor Commented:
found the problem, the stored pass query had return records set to true, when I set to false, it worked. I appreciate your responses and some of them will come in handy in the future, so, am splitting points.
0
jmoss111Commented:
Attached code is how I build my connect string and populate querydef.sql

Jim
Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQLEXPRESS;DATABASE=MyDB;UID="
Public Const strConnect2 = ";Trusted_Connection=yes;"
 
Public Sub BuildAPOHSQLPassThru()
Dim strWhere As String
Dim strOrder As String
Dim strSQL As String
Dim db As dao.Database
Set db = CurrentDb
DoCmd.SetWarnings False
If gUserType <> "External" Or gUserType <> "Internal" Then
    Call GetUserType
    Else
End If
If Forms!frmfindinvoices!CkHOLD = -1 Then
    GoTo BuildHolds
    Else
End If
'Build Aging SQL===========================================================================================================================
    strSQL = "SELECT SupplierName, InvoiceNumber, AmountInUSD, DueDate, PONumber, ERP, Status, Location FROM tblAPAging "
    strOrder = " ORDER BY DueDate ASC;"
    
    If Forms!frmfindinvoices!ckDbAmts = -1 Then
        strWhere = strWhere & "AmountInUSD < 0 " & " AND "
    End If
 
    If Len(Forms!frmfindinvoices!txtSuppNm & "") > 0 Then
        strWhere = strWhere & "SupplierName LIKE " & Chr$(39) & "%" & Forms!frmfindinvoices!txtSuppNm & "%" & Chr$(39) & " AND "
    End If
    
    If Len(Forms!frmfindinvoices!txtInvoice & "") > 0 Then
        strWhere = strWhere & "InvoiceNumber = " & Chr$(39) & Forms!frmfindinvoices!txtInvoice & Chr$(39) & " AND "
    End If
    If Len(Forms!frmfindinvoices!txtPO & "") > 0 Then
        strWhere = strWhere & "PONumber = " & Chr$(39) & Forms!frmfindinvoices!txtPO & Chr$(39) & " AND "
    End If
    If Len(Forms!frmfindinvoices!txtLocation & "") > 0 Then
        strWhere = strWhere & "Location = " & Chr$(39) & Forms!frmfindinvoices!txtLocation & Chr$(39) & " AND "
    End If
   
    If Len(Forms!frmfindinvoices!txtFromDate & "") > 0 Then
                strWhere = strWhere & "DueDate BETWEEN " & Chr$(39) & Forms!frmfindinvoices!txtFromDate & Chr$(39) & " AND "
    End If
    If Len(Forms!frmfindinvoices!txtThruDate & "") > 0 Then
                strWhere = strWhere & Chr$(39) & Forms!frmfindinvoices!txtThruDate & Chr$(39) & " AND "
    End If
    GoTo AddWhereClause
            
  
 
AddWhereClause:
    strWhere = strWhere & "Category = " & Chr$(39) & gUserType & Chr$(39) & " AND "
    If Len(strWhere) > 0 Then
        strWhere = "WHERE " & Left$(strWhere, Len(strWhere) - 5)
        strSQL = strSQL & strWhere
        Else
        MsgBox ("You must select criteria before running the query!")
        GoTo Bail
    End If
db.QueryDefs("qptGetData").SQL = strSQL & strOrder
db.QueryDefs("qptGetData").Connect = strConnect1 & gUsrNm & strConnect2
 
Bail:
DoCmd.SetWarnings True
End Sub
 
 
Public Sub BuildSQLConnectStrings()
Dim db As dao.Database
Set db = CurrentDb
DoCmd.SetWarnings False
db.QueryDefs("qptGetICUser").Connect = strConnect1 & gUsrNm & strConnect2
 
DoCmd.SetWarnings True
 
End Sub

Open in new window

0
jmoss111Commented:
Thanks for the B...  not   What would it have taken to get an A?
0
avoorheisAuthor Commented:
well if you look at my question, I was looking for a simple way to delete records in a linked table. The answered turned out to be:
CurrentDb.Execute "accessquerytodelete"
one line of code, not 76
Which is simpler?
0
jmoss111Commented:
And I gave you a very good example of pulling data from a form that you could do any kind of pass through that you wanted. You did want pass through because linked tables are too slow.

You know which is quicker.
0
avoorheisAuthor Commented:
certainly, just execute a saved query that is a pass through.
0
jmoss111Commented:
0
avoorheisAuthor Commented:
well, put it in that question and maybe I'll give you an A.
I don't want to give you a hard time; I appreciate every expert that participates and I'm generally very liberal with points/grading, but, sometimes it seems like the question doesn't really get read and answers can range from "to the point" to "what question were you answering?".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.