Thread: 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@amphi.com

I guess that you are talking about table alterations. There are several
limitations on PostgreSQL, one of them is adding dropping columns, another
is adding default values, and I can't remeber the rest. However, as well as
pgAccess you might want to take a look at 
http://pgadmin.postgresql.org
which I regularly use (to alter tables right click on them and go to
properties).If you need something not offerd by ALTER TABLE you can rename the
table then create another table with the old name, but altered schema then
copy from one to the other. This can have problems with things that cache a
tables oid though (I think functions and views may do this, not sure as I
don't tend to drop columns from tables (or rename them)). Or you can alter a
pg_dump file to change a table then upload it.
Hope something here helps
- Stuart

-----Original Message-----
From: jim davis [mailto:jdavis@amphi.com]
Sent: 13 December 2001 22:02
To: pgsql-interfaces@postgresql.org
Subject: 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@amphi.com

On Thu, 13 Dec 2001, jim davis wrote:

...
> When it was running on Access, they could preform one of the bazillion
> queries they developed and directly modify the tables from the query. 
...
 I guess you want an updatable result set from a query?  I'm pretty sure
you can do this, but a flag needs to be turned on in the ODBC driver.
Check the documentation.  When it is on, Access will tell you the
recordset is updatable.

Tom

<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>