Thread: problem with serial data type and access
I know it's not fully IT with the list, but maybe somebody can help me. I'm dealing with this scenario: access 97 is connected through odbc to a postgresql server. All tables are saved in postgresql and access is used only to generated the program interface. Everything works fines, but I'm having problems with the serial data type. I know a serial is an integer having as default the next value of a sequence. Since it is an integer, access does not recognize it as an autoincrement value, and it asks for is value. Did anyone of you already have this problem? Thanks... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia.
Attachment
On Thu, May 15, 2008 at 7:54 AM, Ottavio Campana <ottavio@campana.vi.it> wrote: > I know it's not fully IT with the list, but maybe somebody can help me. > > I'm dealing with this scenario: access 97 is connected through odbc to a > postgresql server. All tables are saved in postgresql and access is used > only to generated the program interface. > > Everything works fines, but I'm having problems with the serial data type. I > know a serial is an integer having as default the next value of a sequence. > Since it is an integer, access does not recognize it as an autoincrement > value, and it asks for is value. > > Did anyone of you already have this problem? Can you feed it a value of DEFAULT???
Scott Marlowe ha scritto: > On Thu, May 15, 2008 at 7:54 AM, Ottavio Campana <ottavio@campana.vi.it> wrote: >> I know it's not fully IT with the list, but maybe somebody can help me. >> >> I'm dealing with this scenario: access 97 is connected through odbc to a >> postgresql server. All tables are saved in postgresql and access is used >> only to generated the program interface. >> >> Everything works fines, but I'm having problems with the serial data type. I >> know a serial is an integer having as default the next value of a sequence. >> Since it is an integer, access does not recognize it as an autoincrement >> value, and it asks for is value. >> >> Did anyone of you already have this problem? > > Can you feed it a value of DEFAULT??? I'd like to, but I don't think I could do it. The fact is that the serial data type is in pratice an integer, and when I also try to connect with pgadminIII I see an integer data type and not a serial. I think that since it sees an integer, it does not understand that it is a serial, and access does not recognize it as autoincrement. I'm stuck at this point... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia.
Attachment
On Thursday 15 May 2008 9:14 am, Ottavio Campana wrote: > Scott Marlowe ha scritto: > > On Thu, May 15, 2008 at 7:54 AM, Ottavio Campana <ottavio@campana.vi.it> wrote: > >> I know it's not fully IT with the list, but maybe somebody can help me. > >> > >> I'm dealing with this scenario: access 97 is connected through odbc to a > >> postgresql server. All tables are saved in postgresql and access is used > >> only to generated the program interface. > >> > >> Everything works fines, but I'm having problems with the serial data > >> type. I know a serial is an integer having as default the next value of > >> a sequence. Since it is an integer, access does not recognize it as an > >> autoincrement value, and it asks for is value. > >> > >> Did anyone of you already have this problem? > > > > Can you feed it a value of DEFAULT??? > > I'd like to, but I don't think I could do it. > > The fact is that the serial data type is in pratice an integer, and when > I also try to connect with pgadminIII I see an integer data type and not > a serial. I think that since it sees an integer, it does not understand > that it is a serial, and access does not recognize it as autoincrement. > > I'm stuck at this point... Did you mark this field as the primary key when you linked the table? -- Adrian Klaver aklaver@comcast.net
Adrian Klaver ha scritto: >> The fact is that the serial data type is in pratice an integer, and when >> I also try to connect with pgadminIII I see an integer data type and not >> a serial. I think that since it sees an integer, it does not understand >> that it is a serial, and access does not recognize it as autoincrement. >> >> I'm stuck at this point... > Did you mark this field as the primary key when you linked the table? yes I did -- Non c'e' piu' forza nella normalita', c'e' solo monotonia.
Attachment
On Thursday 15 May 2008 11:44 pm, Ottavio Campana wrote: > Adrian Klaver ha scritto: > >> The fact is that the serial data type is in pratice an integer, and when > >> I also try to connect with pgadminIII I see an integer data type and not > >> a serial. I think that since it sees an integer, it does not understand > >> that it is a serial, and access does not recognize it as autoincrement. > >> > >> I'm stuck at this point... > > > > Did you mark this field as the primary key when you linked the table? > > yes I did Just out of curiosity, create a test table using the WITH OIDS clause and a serial data type column along with some test columns and try to INSERT a record. -- Adrian Klaver aklaver@comcast.net
On Thu, May 15, 2008 at 6:54 AM, Ottavio Campana <ottavio@campana.vi.it> wrote:
> I'm dealing with this scenario: access 97 is connected through odbc to a
> postgresql server. All tables are saved in postgresql and access is used
> only to generated the program interface.
>
> Everything works fines, but I'm having problems with the serial data type. I
> know a serial is an integer having as default the next value of a sequence.
> Since it is an integer, access does not recognize it as an autoincrement
> value, and it asks for is value.
>
> Did anyone of you already have this problem?
Back when I was using V95, I remember having this problem and finding
a simple way to get around it. The only problem is that I can't
remember what it was. MS-Access 2003 doesn't seem to suffer from this
problem.
One brute-force method to get around it is to create a function that
uses an ADO connection to call the PostgreSQL back-end for
nextval('sequence_name'). Next using the Before_insert() event in
access, you could manually set the new id for your primary key and
complete the record insertion.
--
Regards,
Richard Broersma Jr.
Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug