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
In a real application that uses a client/server type database you should
NEVER construct queries that return a million rows.
You have to keep in mind that each record has to be sent accross the
wire via TCP/IP and it is going to take a lot of time to bring back 1
million rows and I am sure your network admin would notice increased
network traffic.
In a real application you would use something like the Delphi Tdataset
based components which will only bring back the records that can be seen
on the screen, then when you scroll, it brings back more.
you can manually create something like this using Postgresql handy LIMIT
and OFFSET sql keywords in your query to build a paging system.
Access is actually a very poor choice for client/server application
development, like I said before, invest some time learning Delphi, it's
not difficult at all and you won't have these problems.
You can get a copy of Delphi 7 personal edition and you can use the zeos
components with it. http://www.zeoslib.net
Tony
Show quoted text
.
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
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.
You could use Python Win32 and get a lot of the same "feeling" that you
would get from Delphi.
Sincerely,
Joshua D. Drake
All you need is the Personal Edition of say Delphi 6 or Delphi 7, and
even Delphi 2005.
For Delphi 2005 personal edition see this link:
http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake
Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005
can do, and I bet you can find version 5 on ebay or elseware for dirt cheap.
The personal editions do not include database access components by
themselves, but you certainly can use third party tools such as Zeos
(http://www.zeoslib.net) or those from
http://www.microolap.com.
You could even grab a copy of libpq.pas if you want to do direct access
to PG.
You get what you pay for and there is nothing available for Python or
WXwidgets that even comes close to Delphi for RAD/GUI database development.
Not to mention all the other stuff you can do with Delphi, it's also a
great programming language for console apps, services, TCP/IP servers.
There are even remote control applications built with it ala PC Anywhere.
Plus if you call borland, I am sure they would give you the upgrade
price if you have a old copy of VB laying around for a competive upgrade.
Show quoted text
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.
I'm pretty sure that zeosdbo needs a version of Delphi with TDataset
support. I don't think that the Personal editions have that. You can use
the direct access parts of Zeos with the personal editions but then you
might as well use Free Pascal and Lazarus as they have just ported ZeosDbo
see http://www.lazarus.freepascal.org/.
Ben
"Tony Caduto" <tony_caduto@amsoftwaredesign.com> wrote in message
news:426BC579.3010404@amsoftwaredesign.com...
Show quoted text
All you need is the Personal Edition of say Delphi 6 or Delphi 7, and even
Delphi 2005.
For Delphi 2005 personal edition see this link:
http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake
Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005
can do, and I bet you can find version 5 on ebay or elseware for dirt
cheap.The personal editions do not include database access components by
themselves, but you certainly can use third party tools such as Zeos
(http://www.zeoslib.net) or those from
http://www.microolap.com.
You could even grab a copy of libpq.pas if you want to do direct access to
PG.You get what you pay for and there is nothing available for Python or
WXwidgets that even comes close to Delphi for RAD/GUI database
development.
Not to mention all the other stuff you can do with Delphi, it's also a
great programming language for console apps, services, TCP/IP servers.
There are even remote control applications built with it ala PC Anywhere.Plus if you call borland, I am sure they would give you the upgrade price
if you have a old copy of VB laying around for a competive upgrade.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.---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Tony Caduto" <tony_caduto@amsoftwaredesign.com> wrote in message
news:4269DB12.6090102@amsoftwaredesign.com...
In a real application that uses a client/server type database you should
NEVER construct queries that return a million rows.
You have to keep in mind that each record has to be sent accross the wire
via TCP/IP and it is going to take a lot of time to bring back 1 million
rows and I am sure your network admin would notice increased network
traffic.In a real application you would use something like the Delphi Tdataset
based components which will only bring back the records that can be seen
on the screen, then when you scroll, it brings back more.you can manually create something like this using Postgresql handy LIMIT
and OFFSET sql keywords in your query to build a paging system.Access is actually a very poor choice for client/server application
development, like I said before, invest some time learning Delphi, it's
Access may be a poor choice for a Delphi developer to use. But for many of
us, it's an excellent choice. Everything depends on your situation and on
your needs.
The OP is starting from ground zero in learning about Access (or anything,
apparently) and client-server apps. Of course there will be difficulty,
regardless of the choice of interface.
Show quoted text
not difficult at all and you won't have these problems.
You can get a copy of Delphi 7 personal edition and you can use the zeos
components with it. http://www.zeoslib.netTony
.
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---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
I guess I was thinking more of the "big picture"
Your right of course, for someone just fooling around at home or for a very small business Access is great, but I am saying from experience that Access is
a poor choice for any kind of large project or corp use.
Show quoted text
Access may be a poor choice for a Delphi developer to use. But for many of
us, it's an excellent choice. Everything depends on your situation and on
your needs.