2

I have MySQL table which is linked to access using odbc(Mysql ODBC connector 8.0 32 bit). Initially one of the column has length of Varchar(1000) and everything works fine. However now I need to increase the column size to Varchar(2000). However as soon as I increase the size of column to 2000, link table become read only. I cannot perform create, delete or update operation. Any suggestion(s) would be highly appreciated.

Also i am using MS Access 2010. you can find attached picture of mysqlMysql Table and resulting linktable.

Access

Maqsood
  • 369
  • 4
  • 17
  • Have you tried to delete and re-add the linked table (or run `Tabledef.RefreshLink`), so that Access picks up the changed table definition? – Andre Nov 06 '18 at 10:59
  • @Andre yes i deleted and re-add link table multiple times also run refresh link multiple times but no success – Maqsood Nov 06 '18 at 11:01
  • Odd. Neither Access nor the ODBC driver should care whether the column is Varchar(1000) or Varchar(2000). Just to be sure: Has Access recognized the **primary key**? i.e. does it show the "key" icon on the appropriate column(s) in table design view? – Andre Nov 06 '18 at 11:04
  • @Andre yes. Also if i remove the column from table it works fine. – Maqsood Nov 06 '18 at 11:26
  • Then it must be something specific with the MySQL ODBC driver, which I have no experience with, sorry. -- Perhaps add to your question the name and version of the driver. – Andre Nov 06 '18 at 11:52
  • Purely for testing you could try one of the older 5.x drivers, https://dev.mysql.com/downloads/connector/odbc/5.3.html – Andre Nov 06 '18 at 12:41
  • @Andre i installed older version. It solve editing problem but now for few records i am getting #DELETED in Access form. I deleted the link table and re-add it but issue remain same. However when i open the linktable, I can see records but its not somehow mapping to fields. – Maqsood Nov 06 '18 at 16:10
  • try odbc 5.3.9 That's the latest stable that i know. 8.0 have issues on win10 machines. – Krish Nov 07 '18 at 14:41
  • Your config meets the [Docs](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html)? Linked tables have a primary key and a proper timestamp field (Default CURRENT_TIMESTAMP, On Update CURRENT_TIMESTAMP), Connector/ODBC "Return matching rows" option is enabled – ComputerVersteher Dec 07 '18 at 01:55

0 Answers0