3086 error could not delete from specified tables

[AndyB2]

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.

Private Sub CopyRecordsToSQL_Click()
Dim Inuse As Boolean
Dim Twait As Date
 
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set dataarea = MyDb.OpenRecordset("dbo_DATA_AREA", DB_OPEN_DYNASET)
Set SQLSummaryData = MyDb.OpenRecordset("dbo_SupperSummary", DB_OPEN_DYNASET)
Set SummaryData = MyDb.OpenRecordset("SupperSummary", DB_OPEN_TABLE)
Dim SQLStg As String
 
'Check Data Area Flag
DoCmd.Hourglass True
If dataarea.Inuse = 0 Then
        Twait = Time
        Twait = DateAdd("s", 15, Twait)
        Do Until TNow >= Twait
             TNow = Time
        Loop
End If
 
'Set Data Area Flag
DoCmd.SetWarnings False
SQLStg = "Delete * from dbo_DATA_AREA"
DoCmd.RunSQL SQLStg
SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,0)"
DoCmd.RunSQL SQLStg
 
'Copy Data
SQLStg = "Delete * from dbo_SupperSummary"
DoCmd.RunSQL SQLStg
SQLStg = "Insert into dbo_SupperSummary (Crew, Asset, Quality, Operator_ID, StartTimeStamp1 VALUES (SummaryData.Crew, SummaryData.Asset, SummaryData.Quality, SummaryData.Operator_ID, SummaryData.StartTimeStamp1)"
DoCmd.RunSQL SQLStg
 

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.

[TheSmileyCoder]

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!

[AndyB2]

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.

[TheSmileyCoder]

As I said, not much experience in SQL server, but if it was me, next thing i would try:

Delete dbo.SupperSummary.* from dbo.SupperSummary

Otherwise look up the syntax for a DELETE statement, specific to SQL server.

[AndyB2]

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.

[NeoPa]

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).

[ADezii]

Try:
SQLStg = "Delete FROM dbo_DATA_AREA;"

[AndyB2]

It ran on the Server as "Delete from SupperSummary". Still getting same error when I use "Delete from dbo.SupperSummary" in VBA.

[NeoPa]

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.

[AndyB2]

dbo.SupperSummary is the name of the linked table inside of Access. It is linked through ODBC.

[NeoPa]

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 :

DELETE * FROM dbo.SupperSummary

[AndyB2]

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:

DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
 

Do I need to call a pass-through query a different way?

[NeoPa]

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 :
   

   Dim cdb As DAO.Database
    
   Set cdb = CurrentDb()
   Call cdb.Execute("UPDATE blah blah blah")

2. From the DoCmd object :
   

   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.

[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.

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?

[NeoPa]

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?

[AndyB2]

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.

[AndyB2]

Current state:

I have a standalone pass-through query called "Truncate_dbo_SupperSummary", it's code is:

truncate table SupperSummary
 

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:

Call DoCmd.RunSQL("Delete * from dbo_SupperSummary")
 

Should I assume from post #13 that I can call, or cannot call the Pass-through query by name?

[AndyB2]

Found answer:

Pass through query named "Truncate_dbo_SupperSummary"

with code:

truncate table SupperSummary
 

vba code:

DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
 

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.

[NeoPa]

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 :

Dim cdb As DAO.Database
 
Set cdb = CurrentDb()
Call cdb.QueryDefs("[Truncate_dbo_SupperSummary]").Execute

[AndyB2]

Thanks for the reply, last question.

Is

Dim cdb As DAO.Database
 
Set cdb = CurrentDb()
Call cdb.QueryDefs("[Truncate_dbo_SupperSummary]").Execute
 

technically better than:

DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
 

The above code is working, but I would like to know the "correct way"?

Thanks,

AndyB

[NeoPa]

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.

[FeelTheWay]

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 !

[NeoPa]

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 :-)

[TheSmileyCoder]

New questions should go in new thread. New solutions relevant to the original question are always welcomed.