Обсуждение: Access, ODBC and "other users"
We have an interesting problem with Access and ODBC. We have a fairly complex application, all tables and such are on Postgres. 1/2 the application is an Access front end (for nice reporting and some data entry) and 1/2 of the application is a web front end (for "public" access to some of the data, and interfaces for some remote administration. There is an overnight script that runs that updates a couple of tables (some detailed calculations that just take too long to do on the fly). After this script runs, Access then doesn't let anyone update the table, because "another user has modified the data". However, as far as pg is concerned the same exact user has changed data, the user used in the ODBC driver connection is the same user that's used both on the web end, and in the overnight script. Is there a way to tell Access to ignore this? Are there ways around this that anyone can think of, short of 1) relinking the table every morning or 2) creating a new table just for the calculation results? -- .Michelle --------------------------- Michelle Murrain mmurrain@dbdes.com Database Designs Associates, Inc. Boston 617.889.0929 Amherst 413.253.2874 Cell 413.222.6350 www.dbdes.com AIM:pearlbear0 ICQ:129250575 "Our capacity to make peace with another person and with the world depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh
Hello,
Where does your script run? In Access, or in a stored procedure? I guess it's in Access.
Are you using the latest driver, and does this problem continue even if you shut down Access, and start it again?
Strange...
-----Message d'origine-----
De : Michelle Murrain [mailto:tech@murrain.net]
Envoyé : mardi, 4. novembre 2003 01:11
À : pgsql-odbc@postgresql.org
Objet : [ODBC] Access, ODBC and "other users"
We have an interesting problem with Access and ODBC. We have a fairly
complex application, all tables and such are on Postgres. 1/2 the
application is an Access front end (for nice reporting and some data
entry) and 1/2 of the application is a web front end (for "public"
access to some of the data, and interfaces for some remote
administration. There is an overnight script that runs that updates a
couple of tables (some detailed calculations that just take too long to
do on the fly). After this script runs, Access then doesn't let anyone
update the table, because "another user has modified the data".
However, as far as pg is concerned the same exact user has changed data,
the user used in the ODBC driver connection is the same user that's used
both on the web end, and in the overnight script.
Is there a way to tell Access to ignore this? Are there ways around this
that anyone can think of, short of 1) relinking the table every morning
or 2) creating a new table just for the calculation results?
--
.Michelle
---------------------------
Michelle Murrain
mmurrain@dbdes.com
Database Designs Associates, Inc.
Boston 617.889.0929
Amherst 413.253.2874
Cell 413.222.6350
www.dbdes.com
AIM:pearlbear0 ICQ:129250575
"Our capacity to make peace with another person and with the world
depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh
---------------------------(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
My first guess has to do with transactioning. Are you using any type of explicit transactions (BeginTrans) that might not have committed or rolled back, while your overnight script was changing the data? Michelle Murrain wrote: > > We have an interesting problem with Access and ODBC. We have a fairly > complex application, all tables and such are on Postgres. 1/2 the > application is an Access front end (for nice reporting and some data > entry) and 1/2 of the application is a web front end (for "public" > access to some of the data, and interfaces for some remote > administration. There is an overnight script that runs that updates a > couple of tables (some detailed calculations that just take too long to > do on the fly). After this script runs, Access then doesn't let anyone > update the table, because "another user has modified the data". > > However, as far as pg is concerned the same exact user has changed data, > the user used in the ODBC driver connection is the same user that's used > both on the web end, and in the overnight script. > > Is there a way to tell Access to ignore this? Are there ways around this > that anyone can think of, short of 1) relinking the table every morning > or 2) creating a new table just for the calculation results? > > -- > .Michelle > --------------------------- > Michelle Murrain > mmurrain@dbdes.com > Database Designs Associates, Inc. > Boston 617.889.0929 > Amherst 413.253.2874 > Cell 413.222.6350 > www.dbdes.com > > AIM:pearlbear0 ICQ:129250575 > > "Our capacity to make peace with another person and with the world > depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh > > ---------------------------(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
> Michelle Murrain wrote: > > We have an interesting problem with Access and ODBC. ... > Access then doesn't let anyone update the table, because > > "another user has modified the data". On Tuesday 04 November 2003 6:22 am, Greg Campbell wrote: > My first guess has to do with transactioning. Are you using any > type of explicit transactions (BeginTrans) that might not have > committed or rolled back, while your overnight script was changing > the data? No, it's one of many oddities in Access/ODBC. Even on a small PG table used by only one person we see this in Access. We haven't figured it out, yet, but we know it doesn't occur on all tables. Our solution will probably be to just phase out Access as we did with MSSQL but if we figure it out I'll post the info. Cheers, Steve
I had the the same problem using
Access form / linked pg tables
now it works with an default at the boolean field
for example the column nocalc
ALTER TABLE public.awork ALTER COLUMN nocalc DEFAULT false;
AND
don't use a timestamptz field in your
Access select statement
ALTER TABLE public.awork ADD COLUMN log_time timestamptz;
ALTER TABLE public.awork ALTER COLUMN log_time DEFAULT
"timestamp"('now'::text);
perhaps it helps
moin,
henry
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Steve Crawford
Sent: Wednesday, November 05, 2003 12:06 AM
To: Greg Campbell; tech@murrain.net
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Access, ODBC and "other users"
> Michelle Murrain wrote:
> > We have an interesting problem with Access and ODBC. ...
> Access then doesn't let anyone update the table, because
> > "another user has modified the data".
On Tuesday 04 November 2003 6:22 am, Greg Campbell wrote:
> My first guess has to do with transactioning. Are you using any
> type of explicit transactions (BeginTrans) that might not have
> committed or rolled back, while your overnight script was changing
> the data?
No, it's one of many oddities in Access/ODBC. Even on a small PG table
used by only one person we see this in Access. We haven't figured it
out, yet, but we know it doesn't occur on all tables. Our solution
will probably be to just phase out Access as we did with MSSQL but if
we figure it out I'll post the info.
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Tuesday 04 November 2003 03:05 pm, you wrote: > > Michelle Murrain wrote: > > > We have an interesting problem with Access and ODBC. ... > > > > Access then doesn't let anyone update the table, because > > > > > "another user has modified the data". > > On Tuesday 04 November 2003 6:22 am, Greg Campbell wrote: > > My first guess has to do with transactioning. Are you using any > > type of explicit transactions (BeginTrans) that might not have > > committed or rolled back, while your overnight script was changing > > the data? > > No, it's one of many oddities in Access/ODBC. Even on a small PG table > used by only one person we see this in Access. We haven't figured it > out, yet, but we know it doesn't occur on all tables. Our solution > will probably be to just phase out Access as we did with MSSQL but if > we figure it out I'll post the info. > > Cheers, > Steve > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org You might want to take a look at the following URL. It addresses this issue http://techdocs.postgresql.org/guides/MSAccess#50 -- Adrian Klaver aklaver@comcast.net