I'm working up a database in PostgreSQL (8.3) that users will interact with (update and query) via Microsoft Access 2003. Several tables have primary keys defined as 'serial' in PostgreSQL. These columns, when linked into Access, show up as 'Number' and don't auto-increment when inserting new data into the tables via Access. Is there a way to make serial primary keys work through Access? Maybe serial isn't the appropriate type? All I need is something unique that is automatically generated by Access when a user starts a new record.
+2
A:
A serial is a integer with a default value from a sequence. If you don't insert anything in this column, the default value is used. That will always happen, no matter what client you use. Access 2003 works fine as a client, also with default values.
Tip: Ignore this column in Access and let the database use the default value.
Frank Heikens
2010-06-17 19:58:02
Definitely +1 to ignoring it on insert and the db will just handle it.
rfusca
2010-06-17 20:47:47
+1 On any of your forms with a control bound to the serial column, set Locked = Yes and Enabled = No on the "Data" tab of the control's property sheet. The users got no reason to ever touch that value.
HansUp
2010-06-17 22:21:39
Gotcha. I'll give that a try. I didn't think it would work because the serially-generated id is necessary as part of a link/join table insertion (many to many sort of relationship) and I would think Access would need to know it in order to insert the correct data into the link table (i.e. Books table, Authors table, BooksAuthors link table: if we create a new Book with a serial ID, then add an author from a static list, book_id is needed for the link table).
cswingle
2010-06-18 02:32:46
Use a form for Books. Include a subform for BooksAuthors, with a combo box for Authors. Use book_id as the link master/child field between the form and subform. When you add a new book in the main form, then move focus to the subform, the new book record is saved and PostgreSQL will give it the book_id. That book_id is available for any new BooksAuthors records you add in the subform. This isn't really a PostgeSQL-specific issue; it works the same way with Jet tables. Ask a new question if you run into trouble.
HansUp
2010-06-18 13:41:02