Silent mode for long batch update

[teachingmyself]

I just installed PostgreSQL on my Windows XP Dell, and installed the PostgreSQL ODBC, and then linked to a table in a SQL database with Access.

I wrote my first VBA routine, just to see if it would all work together, and had it add a million rows (1,000,000) to see the speed. I notice that the form updates at the bottom, as each record is added. I want to know if there is some VBA command to tell the form to be "quiet" and not update, since screen output in Windows, because of its GUI nature, is VERY slow. I have some Liberty Basic programs which process a million or so records, an it increase in speed is enormous when there is no screen output.

Of course, I know so little about Access that the only way I could think of to execute my VBA was to create a form, and put a button on it, and put the code in the event of that button. Perhaps there is a better way to run VBA in Access....

I shall be most greatful for any advice. I am going to shop for a VBA book tomorrow. I learned the commands in my code from google searches.

Also, my vba routine died (hung) after it added 250,000 rows (but the PostgreSQL engine was fine)... I suspect the job hung because I let the screen go into screen saver mode... that is just a suspicion.


=====start of code

Option Compare Database

Private Sub Command2_Click()
'sqlcmd = "insert into public_customers (company, cust_num) values

('test', 11)"
'sqlcmd = "insert into public_customers (company, cust_num) values

('test'," + Str$(countit) + ")"
MsgBox sqlcmd
'CurrentDb.Execute sqlcmd, dbFailOnError
countit = 1401000

' doitagain:
countit = countit + 1
sqlcmd = "insert into public_customers (company, cust_num) values

('test'," + Str$(countit) + ")"
MsgBox sqlcmd
'MsgBox "hello world"


'Application.DisplayAlerts = False
DoCmd.SetWarnings False
Dim rs As New ADODB.Recordset
'rs.Open (sqlcmd), CurrentProject.Connection
rs.Open ("SELECT * FROM public_customers"),

CurrentProject.Connection
rs.MoveFirst
BaseSalary = rs.Fields("company")
MsgBox BaseSalary
doitagain:
countit = countit + 1
sqlcmd = "insert into public_customers (company, cust_num) values

('test'," + Str$(countit) + ")"

DoCmd.RunSQL sqlcmd
If countit < 1401100 Then GoTo doitagain
rs.Close
Set rs = Nothing


End Sub
======================end of code


Here is what I wrote:

 

[ghudson]

The Echo command will allow you to turn off the screen updates.

DoCmd.Echo False, "Running alot of code, please wait..."

'you code

Docmd.Echo True, ""

Ensure that you add the Docmd.Echo True command to your error trapping routine for your db can have problems if the code errors and Echo is left in the False state.