|
It was something of a struggle to get Access to talk to PostgresSQL using
ODBC.
It was even more of a struggle, since I know nothing about Access or VBA,
to create a "Form" in Access, and a Button on the Form, to execute the VBA code,
below.
If it were not for the Internet, and Google, I could never have scrounged
up the proper syntax of these VBA commands. What I wanted to do is to
write some sort of batch program to just add a million rows to a table, and
benchmark it.
When I had it loop and add 1000 rows, it was ok... When I told
it to add a million rows.... then after 250,000 the Access application hung (but
not the whole machine, and not the PosgreSQL). I suspect that it
hung because of something to do with the Windows screen saver. The other
thing I noticed, as it was adding all those records, is that Access updates the
bottom of the form with a record count. That sort of screen I/O in Windows
really slows things down, and when you turn it off, IF you can turn it off, then
file operations speed up enormously. I think I am going to look for a book
on VBA and access.
I was very impressed by what one member said regarding Delphi, but, when I
looked at pricing,... well I would have to lie through my teeth to get the cheap
academic version, and the personal version sounds like it doesnt have the file
access abilities for PostgreSQL. And the prices for enterprise versions at
programmersparadise.com like $4000, sort of puts me off.
So here is my weird Access VBA that tried to add a million rows.
I really just want to learn some new skills. Thats why I am doing
these things.
=====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 |
