By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Delete records in MS Access reappear afterward

P: n/a
I'm experiencing a very strange problem. My application is MS Access
front-end and MS SQL server back-end database. I have a SQL statement
that deletes records from a table after an export process. The
problem is occasionally when the delete statement is executed, these
records no longer display on List Box (not even in the MS Access link
table). But when close and reopen the form, those records reappear.
It almost like MS SQL server rejected or reversed my SQL command. Any
help is greatly appreciated.

Scott
Jul 16 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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 Developersdex http://www.developersdex.com ***
Jul 17 '08 #2

P: n/a
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

Jul 17 '08 #3

P: n/a
For your purposes of deleting records from the server -- I would go with
ADO over ODBC. Here is a sample usage of ADO

make sure to make a reference in Tools/References to

Microsoft ActiveX Data Objects 2.x Library
(2.5 or higher that is)

Sub RemoveRecs()
Dim cmd As New ADODB.Command, j As Long
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection= Yes"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Delete tblx Where colID between 5 and 7"
cmd.Execute j, , adExecuteNoRecords
cmd.ActiveConnection.Close
MsgBox j & " records deleted"

End Sub

This is way more reliable, way less code, and way faster.

Note: When writing statements in ADO against a sql server table - you
have to use Transact Sql (tSql). In tSql for Deleting you just state
"Delete tableName Where..." This does not delete the Table. To remove
a Table in sql server you have to state "Drop Table tableName"

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jul 17 '08 #4

P: n/a
Rich P <rp*****@aol.comwrote in news:12**************@news.newsfeeds.com:
This is way more reliable, way less code, and way faster.
This is utter bunk!
Jul 17 '08 #5

P: n/a
I will wager you on that - a bottle of your Canadian whisky.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jul 17 '08 #6

P: n/a
sc********@gmail.com wrote:
DoCmd.SetWarnings False
Ger rid of the above and use an alternative to docmd.runSQL such as below mentioned
currentdb.execute. They're just trouble and don't give you good, if any, error
messages.

Also creating a query like that will lead to bloat in the front end.

Now I know very little about SQL Server. Why not just create the query in VBA code
and execute it using currentdb.execute strSQL, dbfailonerror?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Jul 17 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.