473,899 Members | 4,012 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 #1
23 12053
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
The error description doesn't give much to go. I guess I would start by checking the following:

Doublecheck that you have the spelling correct, including Capital Letters.

I have not worked much with SQL server. Could it be a issue of related records? That if you delete that record, another record would be left without an orphan? Access can enforce referential entegrity, allthough I believe its only for an access database. Whether and how SQL server implements the same I simply don't know enough about.

The third option I would look into, is whether there are any triggers related to the table, causing the deletion to be cancelled.

Another thing to check, could be to open the SQL server directly, and try to execute the SQL statement from within the server environment.

Hope that helps you, lets hear how it goes!
Feb 23 '12 #2
AndyB2
49 New Member
Spelling is correct, I copied and pasted of the table name into the VBA already.

No triggers on the table

I did go into the SQL Server and it did NOT like the "*" in Delete * from. It ran in the Server as "Delete from SupperSummary". Still getting same error when I use "Delete from dbo.SupperSumma ry" in VBA.

If I change dbo.SupperSumma ry to just SupperSummary it errored out like I thought it would, when it couldn't find the table.
Feb 23 '12 #3
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
As I said, not much experience in SQL server, but if it was me, next thing i would try:
Expand|Select|Wrap|Line Numbers
  1. Delete dbo.SupperSummary.* from dbo.SupperSummary
Otherwise look up the syntax for a DELETE statement, specific to SQL server.
Feb 23 '12 #4
AndyB2
49 New Member
No dice :(

The syntax for SQL from my book is one of two:

1) Delete from SupperSummary
2) Truncate table SupperSummary

Both work in on the SQL server. VBA does not like Truncate in the SQL string.
Feb 23 '12 #5
NeoPa
32,584 Recognized Expert Moderator MVP
Andy, I doubt that the T-SQL syntax will be much help to you here unless you're using a Pass-Thru query. If so, then you need to move the question to the SQL Server forum as it's not Access related (or simply say so and I'll do that part for you).

If it's an Access question, and that is true even if working on a linked SQL Server table, then you are actually tring to work in Jet SQL. The SQL Engine for Access is Jet so all SQL in that context must conform to Jet SQL syntax. I can tell you that Jet SQL doesn't handle deleting from multiple tables in the same same SQL command (except maybe deleting from a query that has multiple tables INNER JOINed). I suggest your best bet is to find the actual syntax of what you want to do, which can be somewhat difficult I realise. Here's some help for that (Finding Jet SQL Help).
Feb 23 '12 #6
ADezii
8,834 Recognized Expert Expert
Try:
SQLStg = "Delete FROM dbo_DATA_AREA;"
Feb 23 '12 #7
AndyB2
49 New Member
It ran on the Server as "Delete from SupperSummary". Still getting same error when I use "Delete from dbo.SupperSumma ry" in VBA.
Feb 24 '12 #8
NeoPa
32,584 Recognized Expert Moderator MVP
dbo.SupperSumma ry is a SQL Server reference. I would certainly not expect that even to be a possible valid name for a linked table in an Access database. Please check the name as it's known to Access and try that instead.
Feb 24 '12 #9
AndyB2
49 New Member
dbo.SupperSumma ry is the name of the linked table inside of Access. It is linked through ODBC.
Feb 24 '12 #10

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
10307
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
4128
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
16676
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
5985
by: twinkle19 | last post by:
upon showing data report on VB, "automation error the specified module could not be found" prompts.
0
1166
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
9997
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
9843
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,...
0
11272
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10971
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,...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8039
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
7201
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
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
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

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.