ODBC data tables not editable using Access query, under certain conditions.

Question

I have ODBC database tables from a SQL database linked to my Access Application.  

While in Access,  I create a "Select" query with one of the ODBC tables, I am able to edit columns values.  No Problem.

When I add another table to the query, no matter if the second table is from the same ODBC tables or from my Local Access tables, the column values which were previously editable, no long can be edited.  

FYI, I have no problem using multiple Access tables in query results and editing.

Please help if you can.  Thank you

Gary

Answer 1

I think the issue might be that your join is on a single field, not the entire primary key. 

It's typically better to use subforms for editing multiple tables.

Answer 2

Thanks again for your quick response.

The index error message that you find as the clue had to do with a table which I am NOT trying to access (because of that error message).  The table that I have a link to does have a primary key value and is editable until another table is added.  I will try to create a Form/Sub Form relationship.

I have read your friends document and it looks like it identifies in one of the scenarios the same condition that I have a problem.  Linking on ODBC table except that the table in question does have a unique index value field. 

Thanks,  Gary

Answer 3

Hi Gary,

Ok, The index message is a clue here. I'm going to refer you to an article written by a friend of mine that details why queries can be uneditable. http://rogersaccessblog.blogspot.com/2009/11/th...

Note: this is a non Microsoft article but comes from a trusted source.

The bottom line here is that you need to either fix the issue or use a form with a main form/subform to edit the data.

Answer 4

Hi Scott,

Thank you for a quick response.  I hope that the information below allows you to help diagnose this issue.

External Datafile Tables (micros.db)

mi_def                                  Menuitem Definition table in micros.db

mi_seq                                  PK field

note:  I am cannot link the “mi_def” table due to “too many indexes; reduce indexes and try again”.  If there is a work around for this issue that would be great.  The indexes are part of the micros.db schema so they cannot be changed.

mi_price_def                      “Price” sub table to Menuitem Definition table

mi_price_seq                      PK field

mi_seq                                  FK – foreign key relationship to mi_def table

Preset_amt_1                     Price field to be updated from access

note:   I can link this table.  I can run a “select” query to view and edit field values.  This is true if no other tables are on the query design dialog screen.  When another table is shown on the query design, all fields are not editable.  The second table can be from micros.db or from access.accdb.

Local datafile table (PM_be.accdb)

tblMicros                          Menuitem Definition table in PM_be.accdb

MidefPriceSeqNum           FK – foreign key relationship to mi_price_def table. 

On the following pages please find: 

The select query design in sql form

The select query design dialog screen is shown with both tables included

2.     The results of the select query.  (values are not editable)

Select query statement shown below is as copied from Access query design dialog box.

SELECT micros_mi_price_def.mi_seq, micros_mi_price_def.mi_price_seq, micros_mi_price_def.preset_amt_1, tblMicros.PrinterName

FROM micros_mi_price_def INNER JOIN tblMicros ON micros_mi_price_def.mi_seq = tblMicros.MidefSeqNum

ORDER BY micros_mi_price_def.mi_seq;

3.     

Select Query Desig n

Results of Select Query

Answer 5

Hi Gary, I'm an independent advisor and will try to help.

Multi table queries can frequently be uneditable if the relationships are not exact. This is not uncommon. But to help you further I need to see the SQL for the query that is causing an issue with what the PKs are for each table.