Store Number of Records Updated-Appended Daily in tbl 2

[LevelThought]

Assistance needed to solve problem.

Every day I perform an update query and then two append queries from an Oracle 8.1.7 database to a local Access table.

Is it possible to store the number of records updated and appended to a local Access table (Titled "Update_Append Log") on a daily basis similar to something like this?:

Date--------UpdateQuery---AppendQuery1----AppendQuery2
Jan 1,2006--80------------20--------------10
Jan 2,2006--95------------30--------------20


Note, a field titled "DateModified" is populated when the update query modifies any of the records within the local Access table and a field titled "DateInserted" is modified when a new record is appended to the local Access table. Consequently, I was trying to incorporate VBA to count the number of records that have a value in the fields titled "DateModified" and "DateInserted."

Any additional insight would be greatly appreciated.

 

[Remou]

Is the RecordsAffected property any use to you? For example:

Dim qdf As DAO.QueryDef
strSQL = "INSERT INTO tblTable ( Field1 ) " _
& "SELECT Field1 " _
& "FROM tblAdd;"
Set qdf = CurrentDb.QueryDefs("qryQuery")
qdf.SQL = strSQL
qdf.Execute
Debug.Print qdf.RecordsAffected

 

[TheAceMan1]

How are ya LevelThought . . .

Excellent choice by [blue]Remou[/blue].

Beaware if you use the querydef.execute method you'll have to run it twice (one using the [blue]Update SQL[/blue] the other the [blue]Append SQL[/blue]).

BTW: [blue]how are you performing your update/append?[/blue]

See Ya! . . . . . .

 

[LevelThought]

Currently, the Update and Append Query are performed using Jet Sql.

Additional Context follows,
I am interested in initially setting the 3 queries to command buttons on a form that will be accessed by the manager.

Command Button 1 is for the Update Query
Command Button 2 is for the first Append Query
Command Button 3 is for the second Append Query

Of course, the update query synchronizes the data in the local Access table to the Oracle data.

Append Query 1 appends all records within the Oracle database to the local Access table that had a payment interface the Oracle database the current day.

Append Query 2 appends all records within the Oracle database to the local Access table that might not have populated the Access table during the one-time historical load performed a month ago. The reason why the accounts did not initially populate the Access table could be due to the account could have been incorrectly loaded in the Oracle database at the time of the historical load.

I would like to create a Access table with the following fields; Date, UpdateQuery, AppendQuery1, and AppendQuery2.
Every day, upon running the 3 queries, I would like this table to be automatically populated with the date that the 3 queries ran and the number of records within the main table that was updated, the number of records within the main table that was appended as a result of the first append query and the number of records that was appended as a result of the second append query.

I am not familiar with the RecordsAffected property but this definitely appears that this might work! I will test and post back.

How would you do it differently? Any issues/comments regarding my logic?

 

[Remou]

I guess you have your reasons for not linking the Oracle tables. It may be better to have Date/Time, Query Name and
RecordsAffected the log file fields.

 

[LevelThought]

Remou,

Good point about the field names. I will modify accordingly.

The Access database is linked to the Oracle database.

How would the original code that you posted change if I want to automatically capture the date/time that the query ran, and the number of records updated and/or appended?