ACCESS 2003 and MySQL - problems with linked tables (1 Viewer)

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 6, 2019

• #1

Hi,

I have an MS-ACCESS 2003 application with linked tables located in MySQL 5.7.25.
The Forms and Queries work correctly with the linked tables, as long as the tables include up to X records, depend which table.

When a table exceeded X records, there is one of 2 problems (depend which table):

1. 1. A table with 4-5 fields (works well with about up to 200 records): When opening the table in ACCESS in View mode, I get the "ODBC - call failed" message, and all fields have "#Name?" value.
   But, when accessing the table data from the Forms, the data is displayed OK.
   
   
2. 2. A table, with 20 fields (works well only up to 100 records): In View mode in ACCESS, I can see all data correctly.
   But I cannot access the data from my Forms. The table and fields are not recognized in the code.

When working with these table directly from Access (not as linked tables) - all works correctly, no matter how many records are in the tables.

Hope you can help me with these issues. THANKS!

 

theDBguy

I’m here to help

Staff member

Local time

Today, 02:38

Joined

Oct 29, 2018

Messages

21,454

• May 6, 2019

• #2

Hi. Welcome to the forum. It sounds like you may have some network issues. It "feels" like a timeout problem. Just a guess...

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 6, 2019

• #3

Hi,
The Auto Reconnect flag is set.

How can I check if it is indeed timeout problem?
Hoe to solve it?

 

S

sonic8

AWF VIP

Local time

Today, 11:38

Joined

Oct 27, 2015

Messages

998

• May 6, 2019

• #4

Do the MySQL tables have a primary key? If yes which data type is it?

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 6, 2019

• #5

Yes. The primaty key is int(11).

 

S

sonic8

AWF VIP

Local time

Today, 11:38

Joined

Oct 27, 2015

Messages

998

• May 6, 2019

• #6

EFRAT_MA said:

Yes. The primaty key is int(11).

Click to expand...

Int(11) at least potentially exceeds the max value of an Access/VBA Long Integer. That might be a problem. Which data type is shown in the linked tables in Access?


Can you explain your issue no. 2. in more detail? I'm not sure what I should make of "The table and fields are not recognized in the code.". Can you show some code that is not working and the error message(s) caused by it?

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 6, 2019

• #7

Regarding issue #1, I created a very simple table (for test) with 3 fields:
1. Primary key - INT(11) - sequential number starting with "1"
2. FLD1 - VARCHAR(50)
3. FLD2 - VARCHAR(50)

Regarding issue #2:
When trying to display a Form with the table data, I get the Run-Time error 2113: "‏The value you entered isn't valid for this field".
Pressing the DEBUG option and entering to the code, shows that the error occurs in Form_Active Sub, when trying to refer to one of the table fields.

Again, up to 100 records, the data displayed correctly in this form.

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 7, 2019

• #8

OK, I was able to isolate the problem:

The problem occurs when viewing all table records.
When retrieving one specific record (or few records), everything works properly.

But I have to show ALL records.
Maybe anyway this is a TIMEOUT problem? How can I check it? How can it be solved? (Auto Reconnect flag is set in the ODBC)

 

S

sonic8

AWF VIP

Local time

Today, 11:38

Joined

Oct 27, 2015

Messages

998

• May 7, 2019

• #9

No, none of this is a timeout issue!
I think, both issues are caused by Access not correctly recognizing the MySQL data types in the tables.
That's why I asked "Which data type is shown in the linked tables in Access?"
I guess your Int(11) primary keys are not recognized as integer values in Access but either as Double or Text. This might cause data type conversion issues.

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 7, 2019

• #10

The ODBC Device Driver is: "MySQL ODBC 8.0 Unicode Driver".
Now I defined "MySQL ODBC 8.0 ANSI Driver", and in some cases it works.
May be this is the reason for the problem?
Continue checking and I will update you.

 

S

sonic8

AWF VIP

Local time

Today, 11:38

Joined

Oct 27, 2015

Messages

998

• May 7, 2019

• #11

I haven't done much work with MySQL+Access recently, so haven't used the Unicode driver, yet. It's certainly possible that it is causing issues, particularly with Access 2003.


Don't forget to check on the potential Int(11) issue!

 

E

EFRAT_MA

New member

Local time

Today, 02:38

Joined

May 6, 2019

Messages

7

• May 8, 2019

• #12

Thanks a lot for your help.
changing to"MySQL ODBC 8.0 ANSI Driver" solved the problem.

 

theDBguy

I’m here to help

Staff member

Local time

Today, 02:38

Joined

Oct 29, 2018

Messages

21,454

• May 8, 2019

• #13

EFRAT_MA said:

Thanks a lot for your help.
changing to"MySQL ODBC 8.0 ANSI Driver" solved the problem.

Click to expand...

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

Lightwave

Ad astra

Local time

Today, 10:38

Joined

Sep 27, 2004

Messages

1,521

• May 8, 2019

• #14

For anyone following this I would always recommend 32 bit windows drivers and 32bit version of MS Access.

Here's a link to database drivers.

https://dev.mysql.com/downloads/connector/odbc/

Be very careful with primary keys as well - in SQL SERVER big int which is often standard primary keys error out in earlier version of MS Access (and even then I don't think runtime can cope with big int)