Playing with PostgreSQL and Access VBA

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