MS Access DB engine cannot find the input table...

[time2hike]

Hi, I have an Access Database that is linked to Oracle 11G tables. I have a vba Sub that is trying to delete selected data from the Oracle table. I am getting an error that Microsoft Access Database engine cannot find the input table or query.

Big Picture: This database is used by multiple users. We want users to be able to update data in the oracle table with the minimum of probablity of running into record locking errors. We are selecting data based on each users criteria and setting it into a temporary Access Table that is uniquely identified based on their selection (Example: they select all records that are associated with SBPRJTRPTGRPID so we name the temp table based on the SBPRJTRPTGRPID) . The user makes their updates in an Access Form linking to the temp table. When they close the Form we want Access to Delete the records in the Oracle table and replace them with the records from the temporary table that the user has updated. Then Access will delete the temporary table and close the Form.

My problem is that when the system goes to delete the records it does not recognize the Linked Oracle table. I know that the table name is correct. I think I need to do something with the TableDef.Connec t but, I am not sure how to go about this. When I do a debug.print on TableDef.Connec t I get the following information: ODBC;DSN=TRNDEV ;UID=TRN_FMDB;P WD=*****; Can you help? Below is the Code for the Delete. It fails on the Open Recordset.

Dim dblSbprjtGrp As Double
Dim sSQL2 As String
Dim rs2 As ADODB.Recordset
dblSbprjtGrp = Me.SBPRJTRPTGRPID.Value
'Replace the Subprojects in TBLMNGRSBPRJTRPTDETAILS with the updated Subproject list on the temporary table
sSQL2 = " DELETE * FROM TBLMNGRSBRPRJTRPTDETAILS WHERE SUBPRJTRPTGRPID = " & dblSbprjtGrp
Debug.Print sSQL2
'Deletes records from TBLMNGRSBPRJTRPTDETAILS
Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

The full code of the Sub contains the Adodb.Connectio n and Path prior to this action and opens another recordset so I know there are no issues there.

Thank you for looking at this with me.

[ADezii]

Looks like a Syntax Error, try:

1. CurrentDB.Execute strSQL2, dbFailOnError

2. Or
   

   Dim dblSbprjtGrp As Double
   Dim sSQL2 As String
   Dim rs2 As ADODB.Recordset
   
   Set rs2 = New ADODB.Recordset
   
   dblSbprjtGrp = Me.SBPRJTRPTGRPID.Value
   
   sSQL2 = "SELECT * FROM TBLMNGRSBRPRJTRPTDETAILS WHERE SUBPRJTRPTGRPID = " & dblSbprjtGrp
   
   rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   
   With rs2
     Do While Not .EOF
       .Delete
         .MoveNext
     Loop
   End With
   
   rs2.Close
   Set rs2 = Nothing

[time2hike]

ADezii, I see where you are going with this and the Loop or Execute are needed to get through the recordset. Thank you for that, however, I am still getting the same error. The Microsoft Access database engine cannot find the input table or query 'TBLMNGRSBRPRJT RPTDETAILS'.

I tried using the TableDef Information ODBC;DSN=TRNDEV ;UID=TRN_FMDB;P WD=*****; to tell the system where to find 'TBLMNGRSBRPRJT RPTDETAILS'. I entered it like this:

   
'Replace the Subprojects in TBLMNGRSBPRJTRPTDETAILS with the updated Subproject list on the temporary table
sSQL2 = " DELETE '*' FROM ODBC.TRNDEV.TRN_FMDB.TBLMNGRSBRPRJTRPTDETAILS WHERE SUBPRJTRPTGRPID = " & dblSbprjtGrp
Debug.Print sSQL2
'Deletes records from TBLMNGRSBPRJTRPTDETAILS
CurrentDb.Execute sSQL2, dbFailOnError

The system error changed to "Could not find file 'C:\Users\hom\D esktop\ODBC.TRN DEV.TRN_FMDB'."

[techiebynature]

I have the exact same problem. Have a Oracle table linked to ms access 2000 mdb. During Debug if I type in immidiate window - DAOObject.Table Defs.Items(101) .Name I get the table name as schemaname.tabl e1 however when I say DAOOBject.OpenR ecordset("Selec t * from table1") it says can not find table1. Even tried DAOOBject.OpenR ecordset("Selec t * from schemaname.tabl e1") but did not work. Please let me know if you were able to solve this.

[time2hike]

techiebynature I have not found the solution yet but, I will post it here when I do. Please let me know if you solve it.

[zmbd]

@time2hike

Can you open a normal select query on the table in question?

[time2hike]

zmbd, I appologize for the delay in answering your question about if I can open a normal select query of the table in question. The answer to this is yes, I can select from the table in an access query and using the VBA Code below.

Private Sub cmdTest_Click()

    Dim conn As ADODB.Connection
    Dim stPath As String
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    
    ' Set the string to the path of your database
    stPath = CurrentDb.Name
    Debug.Print stPath
    ' Open connection to the database
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & stPath & ";"
    conn.Open
    sSQL = "SELECT * " _
        & " FROM TBLMNGRSBPRJTRPTDETAILS " _
        & " WHERE (((TBLMNGRSBPRJTRPTDETAILS.SBPRJTRPTGRPID)=" & [Forms]![frmDialogMngrSbprjtRptGrp]![txtRptGrpID] & "));"
    Set rst = New ADODB.Recordset
    rst.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do Until rst.EOF
        Debug.Print rst!SBPRJTRPTGRPID & ", " & rst!SubprojectID
        rst.MoveNext
    Loop

End Sub

When I write a simple delete query on this table and run it, before attaching it to any commands and VBA code, I am getting the attached error. -- ODBC-delete on a linked table failed. [Oracle][ODBC][Ora]ORA-01460: Unimplemented or unreasonable conversion requested (#1460). I did some research on this error and found that it might have something to do with being in Windows 7 environment and the way the ODBC Links are set up. I have a choice between Oracle in OraClient11g_ho me1 and Microsoft ODBC for Oracle. When I switch the data Source link to using the Microsoft ODBC for Oracle I am able to delete the records I need to and my problem is solved!

Thank you for your assistance.