Error Could not delete from specified tables 2

[MDGarcia]

Good day.

I maintain an MS Access database that connects to Oracle via ODBC. It's being working fine for years.

A few days ago the process to move data from one table to another is giving the error Could not delete from specified tables. According to my client, the process might run well for certain records but not for others.
The process runs as follow:
Run query to move the data to the other table. The query runs fine and moves the data.
Run query to delete the moved records from the original table. This is when the error occurs. The records to be deleted remains in the original table.

This is the query that is being running for a long time. Uses only one table. I have tried many of the recommendations in this and other forums with no results, including setting Unique Records to Yes.
DELETE DOCS_ADMIN_TBLBOX.PALETNUM, *
FROM ADMDOC_ADMIN_TBLBOX
WHERE (((DOCS_ADMIN_TBLBOX.PALETNUM)=getMyPalet())); getMyPalet is a public function that captures the value to be processed.

Your suggestions will be appreciated.

Thanks.


M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[Andrzejek]

I would try:
[tt]
DELETE FROM ADMDOC_ADMIN_TBLBOX
WHERE PALETNUM = getMyPalet();[/tt]

Do you have any Primary Key in this table that is referenced as a Foreign Key in other tabel(s)?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MDGarcia]

Good day Andrzejek

Thank you for your reply.

I've tried your suggestion and the error remains.

I verified the schema and none of the tables has a pk or fk defined.

I will continue my quest.

M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[Andrzejek]

What is the value of [tt]getMyPalet[/tt] for the record(s) that you cannot Delete?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MDGarcia]

Andrzejek

The value is selected on a form and I passed it through the public function.

The only query that won't run is the Delete. The other query that moves from one table to another works fine, using the same function.

Thanks.



M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[MDGarcia]

I have tested the statement with Toad with no problem (using an assigned value.

M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[Andrzejek]

I understand, but it could be beneficial to actually see your Delete statement before you execute it:

Dim SQL As String 
     
SQL = "DELETE FROM ADMDOC_ADMIN_TBLBOX " & _ 
    " WHERE PALETNUM = " & getMyPalet
[red]
Debug.Print SQL[/red]

DoCmd.RunSQL SQL

It may show you something that you may not expect...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I didn't expect Access to be able to edit any database server table that didn't have some identifiable primary/unique key.

That said, I would consider using a pass-through query where you have set the SQL property as needed.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MDGarcia]

Good day.

Andrzejek: I created the DELETE statement but when it executes, does nothing an closes the application. So, I tried executing the original query.
dhookom: I don't have a clear understanding of how the pass-through query will help to delete the records for the value I'm passing.

The delete query was working fine and suddenly started to show the message:
Oracle--delete on a linked table 'DOCS_ADMIN_TBLBOX' failed.
Oracle[ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested
(#1460)

I made sure that the value I'm passing to the public function getMyPalet() is set as string in the Globals module.

I started the whole analysis process again and returned to the original error. While trying to find an alternative, the message reported in this post appeared.

Thank you all.

M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[dhookom]

You stated there was only one table but your code has two: DOCS_ADMIN_TBLBOX and ADMDOC_ADMIN_TBLBOX
A pass-through query uses the server's native brand of SQL and by-pass the MS Access query engine. You would create a blank query and change it to a p-t with the appropriate connection string property. Then use some DAO code to change the SQL property of the query faq701-7433 so that it looks something like:

DELETE 
FROM DOCS_ADMIN_TBLBOX
WHERE PALETNUM='YourAssignedValue'

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

It looks like you still refuse to examine your DELETE statement before you execute it. :-(
And from your code it looks like PALETNUM is a NUMBER, but at the same time:

getMyPalet() is set as string in the Globals module

So, it is not a Number, it is a String...

Dim SQL As String 
     
SQL = "DELETE FROM ADMDOC_ADMIN_TBLBOX " & _ 
    " WHERE PALETNUM = [highlight #FCE94F]'[/highlight]" & [highlight #FCE94F]Replace([/highlight]getMyPalet[highlight #FCE94F], "'", "''")[/highlight] & "[highlight #FCE94F]'[/highlight]"

Debug.Print SQL

DoCmd.RunSQL SQL

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MDGarcia]

Good day.

dhookom: there's a mixup but they're the same table. I will look into p t.

Andrzejek: I am debugging and in the window appears what I want to execute. As I said before, it starts the DELETE but then closes the application and restarts it.

Thank you both for your time and assistance.




M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[MDGarcia]

Good day.

dhookom Based on your FAQ post, I created the following code. No errors, but I'm not sure how the DELETE query will execute. The function works fine creating the pt query. I used the DNS definition set via the ODBC connection (I did not set one manually). I added the execute command to the function, but no command line worked.

Schema: DOCADM_ADMIN
Function fChangeSQL created this pt query: DELETE FROM DOCADM_ADMIN.TBLBOX WHERE DOCADM_ADMIN.TBLBOX.PALETNUM) = '154';

pt query ODBC Connect Str: ODBC;DSN=DOCADM_TST;DBQ=MYDEVDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;
Returns Records: No

I appreciate your comments.

CODE

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click


Dim strOldSQL As String
Dim strSQL As String


strPalet = Me.Combo5.Value
strSQL = "DELETE FROM DOCADM_ADMIN.TBLBOX " & _
"WHERE DOCADM_ADMIN.TBLBOX.PALETNUM) = '" & Replace(getMyPalet(), "'", "''") & "';"
DoCmd.SetWarnings (WarningsOff)
strOldSQL = fChangeSQL("qryDelPalet", strSQL)
DoCmd.SetWarnings (WarningsOn)

Exit_Command10_Click:
Exit Sub

--------------------------------- Globals module
Global strPalet As String

Public Function getMyPalet() As String
getMyPalet = strPalet
End Function

Public Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String) SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
fChangeSQL = qd.SQL
qd.SQL = strSQL
[highlight #FCE94F]'Added by MG. Execute pt query
'db.qd.Execute --> Error Method or data member not found
'db.QueryDefs(pstrQueryName).Execute --> ODBC--Call failed[/highlight]
Set qd = Nothing
Set db = Nothing
End Function

M. Garcia
Senior Programmer Analyst
Puerto Rico

 

[Andrzejek]

Your code should not work because of [highlight #FCE94F]this[/highlight]:

...
strSQL = "DELETE FROM DOCADM_ADMIN.TBLBOX " & _
"WHERE DOCADM_ADMIN.TBLBOX.PALETNUM[highlight #FCE94F])[/highlight] = '" & Replace(getMyPalet(), "'", "''") & "';"

[red]Debug.Print strSQL [/red]
...

Run the Debug to see what you are doing...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MDGarcia]

Hello Andrzejek. The rookie mistakes one makes when writing and rewriting code 50+ times while debugging.

My tests were successful in completing the DELETE process.

Thank you both for your support.

M. Garcia
Senior Programmer Analyst
Puerto Rico