1

When using Microsoft Access for Microsoft 365 (16.0.12827.20328) 64-Bit as a front end for linked tables to an IBM iSeries database using odbc some columns are missing.

The ODBC drivers all point to same file named cwbodbc.dll but can be called either:

  • IBM i Access ODBC driver
  • IBM Client Access ODBC driver
  • IBM iSeries Access ODBC driver

We recently did a refresh/relink on the shared tables and they no longer work as expected.

However we I have a backup of the same accdb database from a week ago and it works correctly, using exact same connecting string and system dsn settings.

I tried a pass-through with exact same connection settings and it works showing all columns. However I need it to be editable, so we require linked table method.

It seems to relate to the odbc property setting (from https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzaik/connectkeywords.htm) ConvertDateTimeToChar=4 (map timestamp to char). When I change this back to default value the linked table now opens with all columns. However on editing a table with a timestamp column I get the error:

"Write conflict, this record has been changed since you started editing. Another user has changed the record since current user started editing. "

Microsoft documentation suggests using the Date/Time Extended data type which included fractions of seconds but it’s not available on my version of access (which is the latest).

How can I get these iSeries linked tables working again in Microsoft Access?

tibba69
  • 83
  • 4
  • Review https://stackoverflow.com/questions/62464046/how-can-i-use-ms-access-date-time-extended-data-type and https://stackoverflow.com/questions/62481084/record-has-been-changed-error-if-data-is-changed-by-vba?noredirect=1#comment110499399_62481084 – June7 Jun 21 '20 at 00:13
  • @june7 I reviewed your link but one relates to MySQL and the other mentions a “date/time extended” field type which could help but is still only in beta versions of ms access. The real problem is why are columns missing when I set odbc connection string property “DATETIMETOCHAR=4”? It used to work. – tibba69 Jun 23 '20 at 16:13
  • Also this link from IBM seems to explain what is happening, but does offer how to fix the issue. https://www.ibm.com/support/pages/microsoft®-access-and-sql0180-syntax-date-time-or-timestamp-not-valid – tibba69 Jun 23 '20 at 16:21

1 Answers1

0

wondering: can you successfully run an Update or Append query on that table?

if so - a work around might be to write the table into a local Access table (without that problem field)….apply your manual edits... and then update/append back to the live table...

admittedly it's a kludge....but....

Cahaba Data
  • 624
  • 1
  • 4
  • 4