Re: pgAccess v. Access2000

This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Part of the problem here is that Access allows you to update
resultsets from queries that are not normally updatable (in other
database systems). If Access thinks it can perform a join because it
has the primary key and all required fields available, it will allow
updates, even where most real databases wouldn't. Part of the reason
for this is that the metadata is quite close to the client, and
therefore the performance overhead of doing this is not that great.
And Access also keeps a load of metadata, so it should be able to
provide that type of functionality. It also assumes a single user,
so even though it's possible to do multi-user access in Access, it is
not optimised for it, and this is one of the ways that it is not
optimised. The main problem with updating resultsets from queries
(other than the dead simple ones) is that the update logic becomes
quite complex without the use of certain assumptions, ones that the
MS designers made in the interests of expediency, but ones which
RDBMS designers would not choose, in the interests of scalability and
robustness (neither of which were considerations, apparently, when
Access was built).

So, some queries (not all, certainly) that would be updatable in
Access you will find are not updatable using other databases. You
need to write functions/procedures/update queries for these cases.

Have fun...

MikeA

>> -----Original Message-----
>> From: jim davis [mailto:jdavis(at)amphi(dot)com]
>> Sent: 13 December 2001 22:02
>> To: pgsql-interfaces(at)postgresql(dot)org
>> Subject: [INTERFACES] pgAccess v. Access2000
>>
>>
>> Ok, I am sure if i spent 3 days looking through the archives I
>> might find someone with an answer...
>> Anyway, we have this database currently running on
>> Access2000 with a MS
>> Personal Web server. This is bad, as we finally talked "them"
>> into letting us move it to a PostgreSQL server and Apache. I was
>> able to export the tables and data out of the Access database with
>> out any problems. The problems arise when the folks that maintain
>> the database
>> want to go and look at the stuff in it and make changes, or
>> set flags.
>> When it was running on Access, they could preform one of the
>> bazillion
>> queries they developed and directly modify the tables from
>> the query.
>> My first attempt was to install the PostgreSQL ODBC drivers
>> and attack
>> it that way. I was able to link the tables and I can even update
>> the tables, but when I make a query either in pass-through or
>> "design view"
>> mode, it will query, but I cannot edit the tables. Everything I
>> have read on the issue says I can't. So I went look for another
>> answer, and
>> found pgAccess. It seems like it nice little app, however lacking
>> in docs. I can create a query in visual design view (just like
>> MS Access)
>> and it works great, however I am stuck with the same
>> problem. I cannot
>> directly edit the table based on the query. In addition, one of
>> the queries they have set up, prompts for a "criteria" value.
>> The "visual
>> design view" part of pgAccess has a line for criteria, but
>> as far as I
>> can tell, it has to be entered at the time of the query. Is
>> there a way
>> to make pgAccess prompt for a value? As unstable and
>> unsecure as Access
>> is, it does seem to offer some nice features that I am sure our
>> folks are not going to want to give up if it makes their lives
>> harder to use a
>> better DB like PostgreSQL. Thanks!
>> --
>> -Jim Davis
>> Network Coordinator II
>> Amphitheater Public Schools
>> Voice: (520)696-5222
>> Fax: (520)696-5070
>> e-mail: jdavis(at)amphi(dot)com
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an
>> appropriate subscribe-nomail command to majordomo(at)postgresql(dot)org
>> so that your message can get through to the mailing list cleanly
>>

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBPB3XWnympNV/C086EQKzZwCg708sgThewBpEY2BGF0N4f7X35C8AoPpR
zD/QAADft2Zdf9k0HRdUwwTk
=31i4
-----END PGP SIGNATURE-----