On Jul 17, 8:14*am, Rich P <rpng...@aol.comwrote:
Hi Scott,
From what I have gathered - you have an ODBC linked table in Access from
a sql server. *You export records from this linked table and then
execute some command to delete these records from the linked table. *But
after closing the Access app and reopening the app the records reappear.
I have a few questions:
1) are the records reappearing cumulatively? *like you export records on
day1 - they reappear on day2 so you export the same records plus new
records and manually delete the old ones from the day2 export - and on
day3 you now have records from day1 and day2...
2) how are you deleting the records? *Are you using a delete query
against the linked table or using a DoCmd.RunSql "Delete * From your
tbl" statement? Or are you using a server based stored procedure?
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
Hello Rich,
Thank you for the reply. Yes, I'm using Access application with ODBC
link table to MS SQL server.
1) Because the records sometime delete and reappear, it causes day2
records to also contain day1 records, which causes duplication.
2) Currently, I'm using PassThruQuery VBA function to "DELETE FROM
ExportInvoiceList WHERE SELECTED <0". Below is my PassThruQuery
function.
Function CreatePassThruQuery(ByVal strQueryName As String, ByVal
strSQL As String, ByVal bolReturnRecords As Boolean, ByVal bolExecute
As Boolean) As Boolean
' this function will create a TDPS2000 PassThruQuery
On Error Resume Next
DoCmd.SetWarnings False
CurrentDb.QueryDefs.Delete (strQueryName)
On Error GoTo err_CreatePassThruQuery
If strQueryName = "" Or strSQL = "" Then
CreatePassThruQuery = False
GoTo exit_CreatePassThruQuery
End If
Set myquery = CurrentDb.CreateQueryDef(strQueryName)
myquery.Connect = "ODBC;" & GetDSN()
myquery.CreateProperty
If bolReturnRecords = True Then
strSQL = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN " & strSQL & " COMMIT TRAN"
End If
myquery.SQL = strSQL
myquery.ReturnsRecords = bolReturnRecords
CurrentDb.QueryDefs.Refresh
myquery.Close
If bolExecute Then
DoCmd.SetWarnings False
DoCmd.OpenQuery strQueryName
End If
CreatePassThruQuery = True
exit_CreatePassThruQuery:
Exit Function
err_CreatePassThruQuery:
Dim strTemp As String
Dim intMsgBox As Integer
strTemp = "Error creating query in funtion CreatePassThruQuery.
Error " & Err.Number & " " & Err.Description & "Would you like to
debug?"
myquery.Close
intMsgBox = MsgBox(strTemp, vbYesNo, "Warning!")
If intMsgBox = vbYes Then Stop
Resume exit_CreatePassThruQuery
End Function
I guess I can try and change it to DoCmd.RunSql. It's a bizarre issue
for me. My thoughts are maybe it has something to do with MS SQL
server too busy to commit. Just some additional information for you.
This delete process works fine on all of my clients except for one.
So, I don't think the problem is code related.
Appreciate your time.
Scott
