Display Counter in sql insert


New Member
Mar 14, 2007

i use the following code to insert a number of records in a access table from a recordset (pulled from a dbase file):

rext is a recordset who get the data from dbase.

rext.Open _
"select nrf from invoice", cntext, adOpenStatic, adLockOptimistic

rstdt is a recordset who help me to put data in a access table

rstdt.Open "select * from lf", cntdt, adOpenStatic, adLockOptimistic

Do While Not rext.EOF

With rstdt
.Fields("nrf") = rext!nrf
End With

ListRow = ListRow + 1

Set rstdt = Nothing

let's say that i have 2 variables:
n1 who store the number of records from the first recordset and a form opened modal.

i want in the caption of the form, named message to put the incremented counter when i pull data with select and then when i put data in the access table
like: 1/1256.. wait
2/1256.... wait.. and so on


Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop


MrExcel MVP
Sep 6, 2002
there are a few issues here -- firstly I'd be inclined to just use the Application.StatusBar to display progress as opposed to a form for the sake of simplicity - that said ideally you would want to have screenupdating set to false to speed up your routine, however, you would need to toggle on/off as and when you want to update the status bar... it's probably not worth while displaying every iteration given your code is likely to run relatively quickly in which case you won't be able to keep up with the iterations -- better to display say every 10 iterations ... but this is for you to decide pending the performance of your routine.

Next issue you have will be whether or not you are able to count the items in your initial recordset without having to loop through it... if you can use

Dim rst1_cnt as long
rst1_cnt = rext.RecordCount

and rst1_cnt does not return -1 then you have your total records count to be inserted...
if however rst1_cnt returns -1 then the driver can't tell you that so you must then either loop the recordset (before insertion routine) to count records within or alternatively run an additional query that simply retrieves the count of items that you're subsequent select query is going to return ... so say my SELECT query was

"SELECT * FROM table1 WHERE field1 = value1"

If I wanted to know the number of records in the resulting recordset and I could not use the .RecordCount functionality I would probably run another query before I ran the above query, namely:

"SELECT COUNT(*) FROM table1 WHERE field1 = value1"

same criteria but instead of returning all records I'll just return the count of records that meet those criteria...thus the result of this query would give me total record count (rst1_cnt), assuming I now have a value for total records (rst1_cnt) I can then use this to set the status bar message whilst looping and inserting records from this recordset:

Application.StatusBar = i & " of " & rst1_cnt & " Records Processed"
i = i + 1

where i = inserts thus far in your loop

at the end of the routine you must reset the statusbar such that it will work correctly

Application.StatusBar = False

I hope that points you in the right direction.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back