Display Counter in sql insert

[jubilee]

Hi!

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
.AddNew
.Fields("nrf") = rext!nrf
.Update
End With

rext.MoveNext
ListRow = ListRow + 1
Loop

rext.Close
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



thanks

 

[DonkeyOte]

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:

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

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.