Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 894
  • Last Modified:

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
0
avoorheis
Asked:
avoorheis
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now