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

[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.SupperSumma ry" in VBA.

[NeoPa]

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.

[AndyB2]

dbo.SupperSumma ry 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_SupperSumma ry. 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.SupperSumma ry. 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.SupperSumma ry" 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.SupperSumma ry, 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.SupperSumma ry", that's what I started with.

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

Query Name: Truncate_dbo_Su pperSummary
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_SupperSumma ry. 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_SupperSumma ry 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_SupperSumma ry 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?