Обсуждение: 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"
'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.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
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 > . > > 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. http://www.python.org 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. >> >> 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. > >
"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. > 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 > >> . >> 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'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... > 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 >
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 sayingfrom experience that Access is a poor choice for any kind of large project or corp use. > 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. >