Delete from Oracle tables using Access

[antomack]

Essentially what I need is to be able to delete records from an Oracle table using A97. I know that it must be something simple but I just don't seem to be getting anywhere. I need to run a delete query on a table and then an append query to append the records for the run week. Each week would involve about 300,000 records with records being kept for 2 years or so. The tables are linked using ODBC.

The situation arises from the migration of an A97 application to Oracle 8i. I have migrated some of the tables and am having problems with some of the delete queries which are trying to delete from the Oracle table. I am getting an message that 'you must use an operable query'. The query itself is fairly straight forward just deleting from the table for a specific year and week.

The delete needs to take in two parameters for the run year and week. The queries run in order as specified in a table and are run by the following section of code. RsControl is populated from a table containing details of the queries to be run and the order to be run in.

   '*****************************
   ' Is there a query to be run ?
   '*****************************
   If Not IsNull(RsControl!QueryName) Then
      Set CallQry = db.OpenQueryDef(RsControl!QueryName)
      '************************************
      ' Does it need year/week parameters ?
      '************************************
      If RsControl!Parameters = -1 Then
         CallQry![Year?] = BaseTablesActualYear
         CallQry![Week?] = BaseTablesActualWeek
      End If
      '********************************
      ' Does it need a type parameter ?
      '********************************
      If Not IsNull(RsControl!TypeParameter) Then
         CallQry.Parameters(0) = RsControl!TypeParameter
      End If

      CallQry.Execute
      CallQry.Close
   End If

One of the delete queries involved is:
DELETE PolicyPremium.*
FROM PolicyPremium
WHERE ((([PolicyPremium].[Year_no])=[Year?]) AND(([PolicyPremium].[Week_no])=[Week?]));

I have created SQL PassThrough queries which seem to work when the year and week are hardcoded but how do you send parameters to them? Also the delete doesn't seem to be committed as a select will bring up the records that are supposed to be deleted.

I have also tried ADO connections to Oracle but can't seem to get the connection right.

Can anyone help in relation to this? Also are there any issues in relation to committing the delete since it is being deleted from an Oracle table.

If you require further details let me know.

 

[Pat Hartman]

Tables linked via ODBC are only updateable by Access if they have unique primary keys.

To use a pass-through query qith parameters, you'll need to build the query in code so VBA can resolve the parameters.

strSQL = "Delete * From YourTable Where SomeField = " & Forms!YourFormName!YourControl

Then run the SQL string.