Thread: PostgreSQL serials in MS-Access are writable, but they are supposed to be read-only

Antti Toivanen <antti.toivanen@telemerkki.fi> said:

> -----Original Message-----
> From: Antti Toivanen [mailto:antti.toivanen@telemerkki.fi]
> Sent: 29 May 2002 17:02
> To: Dave Page
> Subject: RE: [pgadmin-support] Migration from MS Sql server using the
> plug-in
>
> Hi again!
>
>
>
> Okay, I did as you told me to.
>
> Basicly the PostgreSQL's serial_col now works as supposed.
>
>
>
> But I ran to a another problem, altough it is not related to the
> Migration plugin nor pgAdmin.
>
>
>
> The MS-Access doesn't regognize the ID field as a Auto_Increment.
>
> For example: this is problem if I want to copy a record from table A to
> table B with ADO (or DAO), field by field.
>
> If the procedure doesn't know which fields are writable and which arent
> (the Auto_Increment from PostgreSQL is writable), it might try to create
> a duplicated key since it copies it from the old record.
>
>
>
> Do you know a fully Access compatible way to handle this without
> possible code modifications, or can you address it for those who can
> make such a way possible?

Unlike Access' Auto_Increment fields, which are always read-only, PG (& most
other databases) uses sequences, which _can_ get the next number, or can
allow you to supply a number.

In my Access front-ends, I usually hide this column in datasheet view, and,
change the properties of the textbox on form view to mark it as read-only.
Very easy to do.

- J.

--

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

Antti --

I don't think there's any way, through ODBC, to mimic the AutoIncrement type
as used by Access. So I wouldn't hold your breath that PG (or any other
ODBC-connected database backend) is going to implement it.

If your concern is that people can change the auto-incremented field, just
write a rule in PG to change deny updates to this field.

I don't think you understand my advice about not showing the ID field. You
_don't_ have to write this as a separate query for each table. If your users
are simply opening up tables directly, just hide the column in datasheet
view. (Format -> Hide Column, IIRC). I'm not sure what you mean about how
this is not a solution for "general functions", but it's a transparent way
to keep users from fiddling with this field while still giving them direct
access to the table.

I think it's always smarter, though, to deny users access to tables directly
in Access. A good idea is to build a form for each table that shows that
table in datasheet view. This gives you much more flexibility to validate
data, present meaningful error messages, and provide column-level security
constraints. Plus, you can show the ID field, but make it read-only (& if
you really want, to make this look like the Access AutoNumber feature, you
can prefetch sequence.nextval() when an update is made to any field in your
record, just like happens in Access.)

The nice thing about this is that, since it's still a datasheet, it looks to
your users like a table, so they don't understand that anything different is
going on.

- J.

--
Joel BURTON  |  joel@joelburton  |  www.joelburton.com  |  aim:wjoelburton
Information Technology & Knowledge Management Consultant


> -----Original Message-----
> From: Antti Toivanen [mailto:antti.toivanen@telemerkki.fi]
> Sent: Monday, June 10, 2002 2:10 AM
> To: 'Joel Burton'
> Subject: RE: [ODBC] PostgreSQL serials in MS-Access are writable, but
> they are supposed to be read-only
>
>
> Hi Joel!
>
> My problem has nothing to do with forms. I could do a query for each
> table which doesnt show the ID field, but that is not a solution for a
> general functions. I realize that currenty there is no easy way to do
> what I want, but I hope that those who actually develope the PostgreSQL
> would implement the Auto_Increment datatype for MSAccess.
>
> Regards,
> Antti
>
> -----Original Message-----
> From: Joel Burton [mailto:joel@joelburton.com]
> Sent: 4. kesäkuuta 2002 4:12
> To: Antti Toivanen; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] PostgreSQL serials in MS-Access are writable, but
> they are supposed to be read-only
>
> Antti Toivanen <antti.toivanen@telemerkki.fi> said:
>
> > -----Original Message-----
> > From: Antti Toivanen [mailto:antti.toivanen@telemerkki.fi]
> > Sent: 29 May 2002 17:02
> > To: Dave Page
> > Subject: RE: [pgadmin-support] Migration from MS Sql server using the
> > plug-in
> >
> > Hi again!
> >
> >
> >
> > Okay, I did as you told me to.
> >
> > Basicly the PostgreSQL's serial_col now works as supposed.
> >
> >
> >
> > But I ran to a another problem, altough it is not related to the
> > Migration plugin nor pgAdmin.
> >
> >
> >
> > The MS-Access doesn't regognize the ID field as a Auto_Increment.
> >
> > For example: this is problem if I want to copy a record from table A
> to
> > table B with ADO (or DAO), field by field.
> >
> > If the procedure doesn't know which fields are writable and which
> arent
> > (the Auto_Increment from PostgreSQL is writable), it might try to
> create
> > a duplicated key since it copies it from the old record.
> >
> >
> >
> > Do you know a fully Access compatible way to handle this without
> > possible code modifications, or can you address it for those who can
> > make such a way possible?
>
> Unlike Access' Auto_Increment fields, which are always read-only, PG (&
> most
> other databases) uses sequences, which _can_ get the next number, or can
>
> allow you to supply a number.
>
> In my Access front-ends, I usually hide this column in datasheet view,
> and,
> change the properties of the textbox on form view to mark it as
> read-only.
> Very easy to do.
>
> - J.
>
> --
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>
>
>