Delete query referencing a pass-through results in error

[ozinm]

Delete query referencing a pass-through results in error [Solved]

Here's the delete query:

Delete from LocalTable 
where LocalTable.ID IN 
(SELECT PassThroughQuery.ID FROM PassThroughQuery);

The table called "LocalTable" stores a list of IDs I want to delete.
The query called "PassThroughQuery" is an ODBC passthrough query to a MySQL database that returns read-only data.

When I try to run the Delete query I get the error:
Could not delete from specified tables (Error 3086)

The LocalTable is definately NOT read-only.

I'm a bit stuck as to why this should be happening.
Any ideas what's going on?
I'd prefer avoid having to import the data from the passthorugh just so I can run this delete query if I can avoid it.

Thanks in advance for any help and suggestions.

M.

 

[boblarson]

LocalTable is IN SQL Server and not really a local table in Access? Oh, and are you using linked tables or an ADP?

 

[ozinm]

LocalTable is IN SQL Server and not really a local table in Access? Oh, and are you using linked tables or an ADP?

Hi bob.

Happy to clarify.
The local table isn't on a SQL server. It's a real table in my mdb not a link.

The Access DB is not a ADP, hence I can have local tables.

The PassThroughQuery is a query that uses ODBC to connect to a MySQL database using pass-though (which I believe makes that particular table read-only as it relies on mySQL to do all the work).

 

[Banana]

FWIW, I don't think that is a MySQL specific thing but rather a oddity with Access.

As you found out, referencing a PT won't work. However, using a linked table works OK. I just tested it.

DELETE city1.*
FROM city1 
INNER JOIN city ON city1.ID = city.ID;

As well as:

DELETE FROM city
WHERE ID IN (
   SELECT ID
   FROM PT
);

Where city1 is a local copy of the MySQL server's city table, while I've linked the city table. PT is the pass-through query.

I know that kind of question has cropped up before but I can't find the thread I was thinking of.

Will linking the table, even temporarily be sufficient for your purpose? If not, the other alternative I can think of right now would be to look at doing it via VBA using either DAO or ADO recordset but that's almost certainly going to be ineffective. Will have to think about it a bit more.

 

[ozinm]

Hi Banana,
Thanks for the test. Much appriciated.
I thought I might have something seriously wrong with my setup as i couldn't find anything on-line discribing the same issue.
At least I know I'm not going mad!

Unfortunately the PT is acually a dynamically created query rather than a table.

I think I may have to think about rewriting the query so it generates a user specific temp-table in mySQL that I can then link to.


Thanks again for all your help.

All the best

M

 

[Banana]

Hmmm.

If you have a second to test... Try syntax like this:

DELETE FROM localtable WHERE ID IN (SELECT ID FROM ["ODBC;Driver={MySQL ODBC 5.1 Driver};Server=XXX.XXX.XXX.XXX;Database=MyDatabase"].NameOfQuery;)

The syntax may be slightly off, but the idea is that you do "just-in-time" connection and retrieve the query. Note that you reference the query as identified on the MySQL, and not whatever you may call your PT. See if this behaves differently.

 

[ozinm]

Hi Banana,
Nice idea for a test. Here's the code syntax corrected.

DELETE *
FROM LocalTable
WHERE LocalTable.ID IN 
(SELECT ID FROM [ODBC;DSN=MyDatabase;].viewTest);

I set up an ODBC datasource so the the connection string wasn't too onerous. Obviously, as before, I've stripped out my naming conventions in the example above to simplify reading of table names and fields.

The key thing is that this test worked!
It successfully deleted the records which I think proves that it's definitely Access misbehaving.

I'm not sure if I can use this to create as a working solution.
I'd have to allow users the ability create their own views on the mySQL server. My gut tells me that this is not a good thing to do

 

[Banana]

I'm glad that was the trick. I still couldn't find that thread I was thinking of but I'm pretty sure that was the same solution.

Regarding the query itself... Do you mind sharing your view SQL and explaining why the user has to define it manually? I'm sure that we can just use parameters so there's no need to dynamically create a view or run an ad hoc query.

 

[ozinm]

Hi Banana.

The SQL is actually generated on the fly from a custom search form.
Basically, I've got a bit of code that picks up all the unbound fields with a particular prefix in their name that contain content and uses the name & the content to write a SELECT WHERE statement.
This means I can develop the search form very quickly.
(Oh and I can't use the filter by form function in Access, as the front end is to be used with Access Runtime)


I suppose I could rewrite the form to generate input for a SP rather than write SQL but the SP would have an awfully long parameter list.

BTW I've never tried embedding an ODBC link directly into a query before (honestly I didn't know it could be done). Would you know off the top of your head, the syntax to call an SP with parameters passed?

 

[Banana]

That would have to be done in a PassThrough query definitely. Embedded connection works as long there's a table or view we can reference. Stored procedures or ad-hoc SQL do not work in that context, unfortunately.

However, if your process only requires a simple SELECT...WHERE, you could wrap this in a view which you can then reference via embedded link technique. For parameters, though, that would mean a second supporting table on MySQL where the criteria gets inserted and the view would reference those in determining the newest set of criteria to apply upon on the result and returning it.

But then again, it actually could be cheaper to simply import the IDs to be deleted and use that to process the deletion. Depends on how many rows and how intensive the process is.

 

[ozinm]

Hi Banana,
Thanks for all your help and suggestions.
It was really useful to be able to bat some ideas back and forth. Have some cred.

As the list of IDs in the local table only needs to survive until access is closed I think I'm going to rewrite my code so it uses a MySQL temporary table rather than a local one.
That way I can completely circumvent the problem.

All the best

M.