Re: pgAccess v. Access2000 - Mailing list pgsql-interfaces

<p><font size="2"> </font><br /><font size="2">-----BEGIN PGP SIGNED MESSAGE-----</font><br /><font size="2">Hash:
SHA1</font><p><fontsize="2">Part of the problem here is that Access allows you to update</font><br /><font
size="2">resultsetsfrom queries that are not normally updatable (in other</font><br /><font size="2">database
systems). If Access thinks it can perform a join because it</font><br /><font size="2">has the primary key and all
requiredfields available, it will allow</font><br /><font size="2">updates, even where most real databases wouldn't. 
Partof the reason</font><br /><font size="2">for this is that the metadata is quite close to the client, and</font><br
/><fontsize="2">therefore the performance overhead of doing this is not that great. </font><br /><font size="2">And
Accessalso keeps a load of metadata, so it should be able to</font><br /><font size="2">provide that type of
functionality. It also assumes a single user,</font><br /><font size="2">so even though it's possible to do multi-user
accessin Access, it is</font><br /><font size="2">not optimised for it, and this is one of the ways that it is
not</font><br/><font size="2">optimised.  The main problem with updating resultsets from queries</font><br /><font
size="2">(otherthan the dead simple ones) is that the update logic becomes</font><br /><font size="2">quite complex
withoutthe use of certain assumptions, ones that the</font><br /><font size="2">MS designers made in the interests of
expediency,but ones which</font><br /><font size="2">RDBMS designers would not choose, in the interests of scalability
and</font><br/><font size="2">robustness (neither of which were considerations, apparently, when</font><br /><font
size="2">Accesswas built).</font><p><font size="2">So, some queries (not all, certainly) that would be updatable
in</font><br/><font size="2">Access you will find are not updatable using other databases.  You</font><br /><font
size="2">needto write functions/procedures/update queries for these cases.</font><p><font size="2">Have
fun...</font><br/><p><font size="2">MikeA</font><br /><br /><p><font size="2">>> -----Original
Message-----</font><br/><font size="2">>> From: jim davis [<a
href="mailto:jdavis@amphi.com">mailto:jdavis@amphi.com</a>]</font><br/><font size="2">>> Sent: 13 December 2001
22:02</font><br/><font size="2">>> To: pgsql-interfaces@postgresql.org</font><br /><font size="2">>>
Subject:[INTERFACES] pgAccess v. Access2000</font><br /><font size="2">>> </font><br /><font size="2">>>
</font><br/><font size="2">>> Ok, I am sure if i spent 3 days looking through the archives I</font><br /><font
size="2">>>might find someone with an answer... </font><br /><font size="2">>> Anyway, we have this
databasecurrently running on </font><br /><font size="2">>> Access2000 with a MS</font><br /><font
size="2">>>Personal Web server.  This is bad, as we finally talked "them"</font><br /><font size="2">>>
intoletting us move it to a PostgreSQL server and Apache.  I was</font><br /><font size="2">>> able to export the
tablesand data out of the Access database with</font><br /><font size="2">>> out any problems.  The problems
arisewhen the folks that maintain</font><br /><font size="2">>> the database</font><br /><font size="2">>>
wantto go and look at the stuff in it and make changes, or </font><br /><font size="2">>> set flags. </font><br
/><fontsize="2">>> When it was running on Access, they could preform one of the </font><br /><font
size="2">>>bazillion</font><br /><font size="2">>> queries they developed and directly modify the tables
from</font><br /><font size="2">>> the query. </font><br /><font size="2">>> My first attempt was to
installthe PostgreSQL ODBC drivers </font><br /><font size="2">>> and attack</font><br /><font size="2">>>
itthat way.  I was able to link the tables and I can even update</font><br /><font size="2">>> the tables, but
whenI make a query either in pass-through or </font><br /><font size="2">>> "design view"</font><br /><font
size="2">>>mode, it will query, but I cannot edit the tables.  Everything I</font><br /><font size="2">>>
haveread on the issue says I can't.  So I went look for another </font><br /><font size="2">>> answer,
and</font><br/><font size="2">>> found pgAccess.  It seems like it nice little app, however lacking</font><br
/><fontsize="2">>> in docs.  I can create a query in visual design view (just like </font><br /><font
size="2">>>MS Access)</font><br /><font size="2">>> and it works great, however I am stuck with the same
</font><br/><font size="2">>> problem.  I cannot</font><br /><font size="2">>> directly edit the table
basedon the query.  In addition, one of</font><br /><font size="2">>> the queries they have set up, prompts for a
"criteria"value.  </font><br /><font size="2">>> The "visual </font><br /><font size="2">>> design view"
partof pgAccess has a line for criteria, but </font><br /><font size="2">>> as far as I</font><br /><font
size="2">>>can tell, it has to be entered at the time of the query.  Is </font><br /><font size="2">>>
therea way</font><br /><font size="2">>> to make pgAccess prompt for a value?  As unstable and </font><br /><font
size="2">>>unsecure as Access</font><br /><font size="2">>> is, it does seem to offer some nice features
thatI am sure our</font><br /><font size="2">>> folks are not going to want to give up if it makes their lives
</font><br/><font size="2">>> harder to use a</font><br /><font size="2">>> better DB like PostgreSQL. 
Thanks!</font><br/><font size="2">>> -- </font><br /><font size="2">>> -Jim Davis</font><br /><font
size="2">>>Network Coordinator II</font><br /><font size="2">>> Amphitheater Public Schools</font><br
/><fontsize="2">>> Voice:  (520)696-5222</font><br /><font size="2">>> Fax:    (520)696-5070</font><br
/><fontsize="2">>> e-mail: jdavis@amphi.com</font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> ---------------------------(end of </font><br /><font
size="2">>>broadcast)---------------------------</font><br /><font size="2">>> TIP 3: if posting/reading
throughUsenet, please send an</font><br /><font size="2">>> appropriate subscribe-nomail command to
majordomo@postgresql.org</font><br/><font size="2">>> so that your message can get through to the mailing list
cleanly</font><br/><font size="2">>> </font><p><font size="2">-----BEGIN PGP SIGNATURE-----</font><br /><font
size="2">Version:PGPfreeware 7.0.3 for non-commercial use <<a href="http://www.pgp.com"
target="_blank">http://www.pgp.com</a>></font><p><font
size="2">iQA/AwUBPB3XWnympNV/C086EQKzZwCg708sgThewBpEY2BGF0N4f7X35C8AoPpR</font><br/><font
size="2">zD/QAADft2Zdf9k0HRdUwwTk</font><br/><font size="2">=31i4</font><br /><font size="2">-----END PGP
SIGNATURE-----</font><code><fontsize="3"><br /><br /> This e-mail and any attachments are confidential and may also be
privilegedand/or copyright <br /> material of Intec Telecom Systems PLC (or its affiliated companies). If you are not
an<br /> intended or authorised recipient of this e-mail or have received it in error, please delete <br /> it
immediatelyand notify the sender by e-mail. In such a case, reading, reproducing, <br /> printing or further
disseminationof this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom Systems PLC. does not
representor warrant that an attachment hereto is free <br /> from computer viruses or other defects. The opinions
expressedin this e-mail and any <br /> attachments may be those of the author and are not necessarily those of Intec
Telecom<br /> Systems PLC. <br /><br /> This footnote also confirms that this email message has been swept by<br />
MIMEsweeperfor the presence of computer viruses.<br /></font></code>