473,372 Members | 1,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Delete records in MS Access reappear afterward

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
6 4058
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
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
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
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
I will wager you on that - a bottle of your Canadian whisky.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jul 17 '08 #6
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Steve | last post by:
I have several pairs of synchronized subforms in an application. I have a Delete button for each pair that uses the following code or similar to delete a record in the second subform: ...
6
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from...
4
by: AlanAylett | last post by:
Hi all wondering if anybody could spot anything wrong with this SQL DELETE statement, both my Access and SQL skills are not what they used to be so i reckon its something simple, any suggestions...
8
by: John Baker | last post by:
Hi: Access 2000 W98! I have a table with numerous records in it, and am attempting to delete certain records that have been selected from it. These are selected based on the ID number in a...
3
by: Tim Marshall | last post by:
HI all, Access 2003, Jet back end. Rather than annoy my users in a particular app by having relationships with enforced relational integrity refuse to delete a record with related records, I'm...
8
by: paulwilliamsonremove | last post by:
Hi, Using Access 2003. I am trying to delete records from one table/query ("qryHistoryPersonIDs") if they don't exist in another table ("qryDonations"). But the SQL syntax I came up with is...
12
by: hallpa1 | last post by:
Hi All, I am designing a purge process for a db that has grown to almost 200GB. My purge process will remove about 1/3 of the 500 million rows spread over seven tables. Currently there are about...
8
by: Neil | last post by:
I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked tables over a LAN and a WAN. The system has been operational for years with relatively few problems. Recently, WAN...
5
WyvsEyeView
by: WyvsEyeView | last post by:
Upon clicking Delete to delete the current record on frmTopics, I want several things to happen: 1) Display a custom message rather than Access's standard "You are about to delete n records" one....
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.