423,850 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

3086 error could not delete from specified tables

P: 49
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
Share this Question
Share on Google+
23 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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

P: 49
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.SupperSummary" in VBA.

If I change dbo.SupperSummary to just SupperSummary it errored out like I thought it would, when it couldn't find the table.
Feb 23 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

P: 49
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
Expert Mod 15k+
P: 31,121
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
Expert 5K+
P: 8,591
Try:
SQLStg = "Delete FROM dbo_DATA_AREA;"
Feb 23 '12 #7

P: 49
It ran on the Server as "Delete from SupperSummary". Still getting same error when I use "Delete from dbo.SupperSummary" in VBA.
Feb 24 '12 #8

NeoPa
Expert Mod 15k+
P: 31,121
dbo.SupperSummary 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

P: 49
dbo.SupperSummary is the name of the linked table inside of Access. It is linked through ODBC.
Feb 24 '12 #10

NeoPa
Expert Mod 15k+
P: 31,121
That seems strange. Unfortunately I no longer have access to a SQL Server to play with :-(

I did notice though, that post #1 refers to it as dbo_SupperSummary. From my limited memory, this is what I would have expected to see for the linked table within Access. I'm very curious as to how this came to be if it's shown in Access as dbo.SupperSummary. It's curious to say the least. Why would your code use that reference unless you'd seen it that way in Access (You certainly wouldn't have seen it like that in SQL Server).

AndyB2:
It ran on the Server as "Delete from SupperSummary". Still getting same error when I use "Delete from dbo.SupperSummary" in VBA.
The first sentence makes sense and would be expected to work. T-SQL syntax, in use on the server, allows such a command.
The second sentence doesn't so much. Even assuming Access has an object named dbo.SupperSummary, then the Jet SQL syntax is different from T-SQL and requires an asterisk (*) after the DELETE command. In Jet, you'd need :
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM dbo.SupperSummary
Feb 24 '12 #11

P: 49
No luck on "DELETE * FROM dbo.SupperSummary", that's what I started with.

I did take your idea on a pass-through query:

Query Name: Truncate_dbo_SupperSummary
Query: truncate table SupperSummary;

That works when I fire it off inside Access, but get hung up in my code when I call it:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
  2.  
Do I need to call a pass-through query a different way?
Feb 24 '12 #12

NeoPa
Expert Mod 15k+
P: 31,121
AndyB2:
Do I need to call a pass-through query a different way?
Yes. In as much as that way is invalid for all queries except SELECT ones (where data is displayed in a query window within Access). All action queries need to be called differently from that.

There's no reason I can see for a Pass-Thru query to be necessary, mind you, but you've still not explained how come post #1 refers to dbo_SupperSummary. I suspect that's quite important.

There are two main ways to invoke a SQL command :
  1. From a database object reference :
    Expand|Select|Wrap|Line Numbers
    1. Dim cdb As DAO.Database
    2.  
    3. Set cdb = CurrentDb()
    4. Call cdb.Execute("UPDATE blah blah blah")
  2. From the DoCmd object :
    Expand|Select|Wrap|Line Numbers
    1. Call DoCmd.RunSQL("UPDATE blah blah blah")
    In this case you may get warnings indicating how many records have been effected, unless these are turned off for the duration.
Feb 24 '12 #13

P: 49
dbo_SupperSummary is the table name Access created when I linked to table SupperSummary in the SQL server. I have deleted the link and recreated it, same name is created.

I attemped the above options 1 and 2 with no luck. Both of the options seem to want to write the SQL in the statement. Is there a way to evoke the passthrough query I created in Access?
Feb 24 '12 #14

NeoPa
Expert Mod 15k+
P: 31,121
AndyB2:
dbo_SupperSummary is the table name Access created when I linked to table SupperSummary in the SQL server. I have deleted the link and recreated it, same name is created.
You post this without any recognition of the fact you've been saying exactly the opposite throughout the thread!

If this needs to be explained, it's extremely complicated working with someone who contradicts themself every other post. How can we know what the question is supposed to be if you don't seem to have any idea?
Feb 24 '12 #15

P: 49
Looking back I can see were I was using dbo. where I should of been using dbo_ and I appoligize.

When I was talking about testing in the SQL server I was not using the dbo_ prefix, and I can see where that can be confusing also. I should state more clearer what enviroment I am taking my examples from.
Feb 24 '12 #16

P: 49
Current state:

I have a standalone pass-through query called "Truncate_dbo_SupperSummary", it's code is:
Expand|Select|Wrap|Line Numbers
  1. truncate table SupperSummary
  2.  
In this query proprety: the ODBC Connect Str is pointing at the SQL Server

The above works like a charm when I double click it.


The current code that is NOT working in my VBA with the same 3086 error code:

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunSQL("Delete * from dbo_SupperSummary")
  2.  
Should I assume from post #13 that I can call, or cannot call the Pass-through query by name?
Feb 24 '12 #17

P: 49
Found answer:

Pass through query named "Truncate_dbo_SupperSummary"

with code:
Expand|Select|Wrap|Line Numbers
  1. truncate table SupperSummary
  2.  
vba code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
  2.  
Problem: Had different error with this setup.

Solution in pass through query had to turn option: Returns Records to "NO".

Thanks for pointing out pass through queries, and sorry for any confusion or heartburn.
Feb 24 '12 #18

NeoPa
Expert Mod 15k+
P: 31,121
AndyB2:
Should I assume from post #13 that I can call, or cannot call the Pass-through query by name?
I'm not sure post #13 had a good example of that, but if, as I infer from your post, you have saved the pass-thru as a QueryDef, then yes. An example for a query named [Truncate_dbo_SupperSummary] would be :
Expand|Select|Wrap|Line Numbers
  1. Dim cdb As DAO.Database
  2.  
  3. Set cdb = CurrentDb()
  4. Call cdb.QueryDefs("[Truncate_dbo_SupperSummary]").Execute
Feb 24 '12 #19

P: 49
Thanks for the reply, last question.

Is
Expand|Select|Wrap|Line Numbers
  1. Dim cdb As DAO.Database
  2.  
  3. Set cdb = CurrentDb()
  4. Call cdb.QueryDefs("[Truncate_dbo_SupperSummary]").Execute
  5.  
technically better than:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
  2.  
The above code is working, but I would like to know the "correct way"?

Thanks,

AndyB
Feb 24 '12 #20

NeoPa
Expert Mod 15k+
P: 31,121
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

P: 1
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
Expert Mod 15k+
P: 31,121
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
Expert Mod 100+
P: 2,321
New questions should go in new thread. New solutions relevant to the original question are always welcomed.
Oct 25 '12 #24

Post your reply

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