473,883 Members | 1,750 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

3086 error could not delete from specified tables

49 New Member
Error is happening on line 30 below when trying to delete an SQL server table. The thing I cannot get my mind around, is I am deleting a table in the same SQL Server on line 24 without any errors? And I'm trying to handle it the same way.


Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyRecordsToSQL_Click()
  2. Dim Inuse As Boolean
  3. Dim Twait As Date
  4.  
  5. Set MyDb = DBEngine.Workspaces(0).Databases(0)
  6. Set dataarea = MyDb.OpenRecordset("dbo_DATA_AREA", DB_OPEN_DYNASET)
  7. Set SQLSummaryData = MyDb.OpenRecordset("dbo_SupperSummary", DB_OPEN_DYNASET)
  8. Set SummaryData = MyDb.OpenRecordset("SupperSummary", DB_OPEN_TABLE)
  9. Dim SQLStg As String
  10.  
  11. 'Check Data Area Flag
  12. DoCmd.Hourglass True
  13. If dataarea.Inuse = 0 Then
  14.         Twait = Time
  15.         Twait = DateAdd("s", 15, Twait)
  16.         Do Until TNow >= Twait
  17.              TNow = Time
  18.         Loop
  19. End If
  20.  
  21. 'Set Data Area Flag
  22. DoCmd.SetWarnings False
  23. SQLStg = "Delete * from dbo_DATA_AREA"
  24. DoCmd.RunSQL SQLStg
  25. SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,0)"
  26. DoCmd.RunSQL SQLStg
  27.  
  28. 'Copy Data
  29. SQLStg = "Delete * from dbo_SupperSummary"
  30. DoCmd.RunSQL SQLStg
  31. SQLStg = "Insert into dbo_SupperSummary (Crew, Asset, Quality, Operator_ID, StartTimeStamp1 VALUES (SummaryData.Crew, SummaryData.Asset, SummaryData.Quality, SummaryData.Operator_ID, SummaryData.StartTimeStamp1)"
  32. DoCmd.RunSQL SQLStg
  33.  
In the Microsoft Visual Basic error box:

Run-time Error '3086;:

Could not delete from specified tables.

I put it in the title of the question. But now I realize it isn't that apparent it is the error description. If you are looking for something else let me know.
Feb 23 '12
23 12052
NeoPa
32,584 Recognized Expert Moderator MVP
I wasn't sure that DoCmd.OpenQuery () worked with action queries generally. Now I've done some tests I know it does, so the difference here is only that the first version (.Execute()) runs it without warning messages whereas the second (.OpenQuery()) can display these messages if enabled. Your choice which you use from there.
Feb 25 '12 #21
FeelTheWay
1 New Member
Hi everyone (sorry for my english I'm french)

I know this post is old but I got the same problem and it's the only post in the web who has a real good discussion about this problem.

I found the solution to use DoCmd.RunSQl ("delete from TABLE")
The table must have a Primary Key. Without it, the 3086 error will be fired !
Oct 25 '12 #22
NeoPa
32,584 Recognized Expert Moderator MVP
How old a thread is is not important. People are looking for and finding answers to their problems all the time.

What you have suggested is an alternative approach which I'm sure works. It doesn't mean that earlier suggestions are no longer valid of course :-)
Oct 25 '12 #23
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
New questions should go in new thread. New solutions relevant to the original question are always welcomed.
Oct 25 '12 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

7
7132
by: Gb | last post by:
Hi There, I have: select myTable.*, Case When myTable.problem_CLOSE_date = myTable.problem_creation_date then 2 When myTable.problem_CLOSE_date >0 and myTable.problem_status='C' then 3 When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
3
10306
by: rn5a | last post by:
I am trying to delete records from a MS-Access DB table using the following query: strSQL=DELETE FROM MyTable WHERE =CInt(Request.QueryString("delete")) con.Execute strSQL When I run the app on my local IIS 5.1 server (on WinXP SP2), records get deleted without any problems but when I upload the same file (which has the above DELETE query) to the actual remote server, then
5
9692
by: MRounds | last post by:
Hi Have looked through many forums but cannot find a solution for this. I have a table where i store certain dates and i want to use this table in several queries to delete records from multiple tables by using a macro to run the queries. I can use DISTINCTROW to delete rows that are equal to a date but cannot figure out how to delete rows that are between a range of dates (stored in . Example of my query : DELETE...
2
4126
by: amolbehl | last post by:
I use VB6.0 and when I execute the code I have given below, I get runtime Error 1004 - Specified value is out of range. Set oXL = CreateObject("Excel.Application") oXL.Visible = True Set oWB = oXL.Workbooks.Add oWB.Windows.Application.Visible = true Set oSheet = oWB.ActiveSheet oSheet.Name = "TEST" oSheet.Visible = xlSheetVisible
3
16665
by: Betty Boop | last post by:
I am trying to import tables using an odbc connection on a periodic basis. I don't want to create a second copy of the tables, so I have to delete them all before doing the import. I found another question similar to this and tried the following code, but it doesn't work. Private Sub btnPBGUpdate_Click() Dim TblName As String Dim obj As AccessObject, dbs As Object Set dbs = Application.CurrentData ...
1
5984
by: twinkle19 | last post by:
upon showing data report on VB, "automation error the specified module could not be found" prompts.
0
1165
by: Duke Slater | last post by:
I have a user who has created an Access 2007 database with one linked table to SQL Server 2005. He wants to purge the linked table and repopulate it, but gets the 3086 error when running a delete statement. I might think the syntax is wrong, but when opening the table in Access the delete option is disabled (but only on the linked table). He can delete records from that table from within SQL Server, so it's not a permissions issue on that...
1
2184
by: jesus4gaveme03 | last post by:
I created a database to visually compare pictures stored on a computer then delete the duplicate pictures from the computer. I imported the pictures into the Pictures table which has an ID field, an attachment field, and a hyperlink field containing the file location of the picture. After comparing the pictures, if they are the same, I append the picture to a table called DuplicatePictures which only has the ID and file path. The problem is that...
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9797
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7977
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6005
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4622
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.