Search code examples
vbavariablesms-accessinsertstore

Getting the number of affected records by an action query in VBA

I'm wondering if it is possible to pick a variable which is being used by MS Access when inserting a specific amount of new data rows to a table which happens during a VBA runtime.

Screenshot of the MS Access Notification (in German):

Screenshot of the MS Access Notification (in German)

MS Access notifies me here in that case that in the middle of a running VBA script 2327 new data rows are being added to the table. I'm not a programmer but I feel that this variable must be stored somewhere (at least temporary).

Does anyone has an idea how to pick/access this number from the window when it appears and then store it into an VBA Integer for further use in the future?

Update:

Basically, I have a main database which has to be updated every day by an import file.

In the first step there is a check for already existing datasets which will therefore only updated by an UPDATE-Query. 

UPDATE ReiseMaster
INNER JOIN Update_Import
ON(ReiseMaster.Col1 = Update_Import.Col1)
   SET ReiseMaster.Col2  = Update_Import.Col2,
       ReiseMaster.Col3  = Update_Import.Col3;

Then, in the second step new datasets which are not existing in the main database will be inserted into it will all the information they contain. This is the SQL-Query which is responsible for appending new data rows during the VBA procedure:

INSERT INTO ReiseMaster ([Col1],[Col2],[Col3])
SELECT [Col1],[Col2],[Col3] FROM Update_Import 
  WHERE NOT EXISTS(SELECT 1 FROM ReiseMaster
  WHERE Update_Import.[Col1] = ReiseMaster.[Col1]);

I am struggling with identifying the amount of new datasets after the procedure, which is in fact already somehow determined by MS Access itself (see Pop-Up). So my idea was to just use the number which is already determined by MS Access.

All SQL-Queries are stored in a string and run by the "DoCmd.RunSQL" command.

Solution

  • Using DAO, it's really easy to get the number of records affected after executing a query.

    You can just use the Database.RecordsAffected property:

    Dim db As DAO.Database
Set db = CurrentDb 'Required, don't use CurrentDb.Execute else this won't work
Dim strSQL as String
strSQL =  "UPDATE ReiseMaster INNER JOIN Update_Import" 'and the rest of your query
db.Execute strSQL, dbFailOnError  'Execute the query using Database.Execute
Dim recordsAffected As Long
recordsAffected = db.RecordsAffected 'Get the number of records affected

    However, this won't allow you to see it before committing the query. To see it and display a prompt, you can use transactions (short sample here), and rollback the transaction if the user doesn't choose to commit.