Problem Updating/Deleting Records from ODBC Linked Tables

  • Thread starter Thread starter Patricia Ryan
  • Start date Start date
P

Patricia Ryan

Completed an external link to a mainframe DB2 database.
Initially I could add, modify, and delete records. Now
when I try to Update or Delete a record, the message 'No
current record' is received. I've downloaded the
Microsoft Jet 4.0 - Service Pack 7, but it still gives the
same message. Any suggestions??

Thank you!!!
 
You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);

You can also use multiple fields to identify a Unique Record:
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC
(OrderID,CustomerID,ShipToID );
 
Joe - THANK YOU for responding so quickly!!
I have to admit I have limited experience with Access.
What you are saying makes sense, but I'm not sure where to
start with it. So, could you point me in the right
direction to help me get started? Also, when I look at
the Properties for the linked table, it does display the
primary key, indicates it is indexed, and does not allow
duplicates. What will the CREATE INDEX statement do that
is not already in the properties for the table?

Thanks in advance for all your help!
Pat
 
Sometimes Access can't identify the Unique Record Identifier in a linked
table.
But you are saying the design of the linked table shows the PK is correct.
So it looks like Access found it in this case.

The create index statement builds one just for Access that Access can use to
uniquely identify records.
This makes the linked table editable.

Try deleting and re-creating the linked table.
Is it editable?
Close and re-open the .mdb.
Is it still editable?

Try the create index query.
Is it editable?

Are there any decimal fields in the table?
Dates?
These could cause problems during updating too.
(SQL Server uses timestamp columns to help resolve this issue. But you have
DB2.)
 
Joe,
First, I'd like to apologize for not responding sooner....

In response to your questions:
Delete and re-create the linked table.
Is it editable? No
Close and re-open the .mdb.
Is it still editable? No

Try the create index query. Is it editable? I'm still
working on this one.... I'll get back to you when I get
this step completed.

Are there any decimal fields in the table? No
Dates? Yes, a datetimestamp field.

I have been successful using the Update and Delete queries
to modify the tables. Why would that work and not
manipulating data or records directly on the table?
 
Not really sure.
There must be something odd about your environment that will take on site
troubleshooting to resolve.
Sorry.

One last idea: check the settings in the ODBC driver.
I recall that when I connected to an AS400 in the mid-90's that the driver
could be configured to be Read-Only.
If it is, then uncheck it!
 
Joe,
I think we finally have it figured out. Here's what we
needed to do with our ODBC settings so we could
Update/Delete records on our linked tables:
- Select Optimize button >> Microsoft Access
- Advanced button >> Service Tab >> under the CLI
Parameter for PATCH1, select value 'Return primary key
ahead of unique index'
Apparently it looked like we were obtaining the primary
key fields from the DB2 database, but Access was actually
selecting the unique field.
You had us looking at the primary fields, but apparently
the display was deceiving. Thanks for all your help!
 
Glad to see that I was on the right track but you had to configure your
specific driver to act the way I suggested.
Glad it works for you.
 
Back
Top