trouble linking MYSQL tables to Access

[buratti]

Hello. I am trying to link a MySQL database to my Access file and am having some trouble. First off the MySQL database was created by my other, industry specific, software provider. They recently updated to the MySQL format so people like me could now access the data outside of that software. They also had a stipulation of "you're on your own" if you run into any errors or problems when accessing/manipulating data.

So I was able to set up and link the tables in the database to my access file, but one of 2 problems occur depending on the settings.

1. The primary key's data type of most of the tables in the MySQL DB are set to BIGINT. When I link the tables, Access reads this as a text type. Then I get data type mismatch errors when trying to run queries, inserting subforms and etc, because the related fields in other tables are of regular INT data types and access is reading it a number format.

2. After research on this BIGINT problem I found a solution, but it creates another problem... The solution was to Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator and creating a dummy column with type Timestamp. After doing this and relinking the tables, the data type is now correctly showing as number, but whenever I try to view data in the table (either directly from the table, in a query, or on a form based on the table/query) every field in every record is marked as #DELETED#. The total number of records in the table is correct however. Most of the tables already had a field/column of Timestamp type, but even if I added another one I still got the #DELETED# problem. Oddly enough though whenever I added the new field to the table that field did not show up in the table after re-linking.

Any suggestions on fixing these problems?

 

[jdraw]

I don't know the answer to your problem/issue but I think you may find some help here
http://www.codekabinett.com/page.php?Theme=4&Lang=2

Also there may be people on the forum that use MYSql with Access. You may do some searching to see if you can find someone - then perhaps join a thread or send a PM...

Do you have a physical copy of the MySQL database or are you accessing their MySql database on their site?

 

[buratti]

Ah I think I found the culprit... At first I just skimmed through your linked article and it seemed like nothing I already found out myself. I knew it had something to do with the BIGINT data type, but I tried all of the workarounds with no success. Then i re-read your link and found one little sentence that i didn't notice before and it ended my fight to fix this...

"You can't use BIGINT-Columns as Primary Keys in your tables in any circumstances. Even with the Change BIGINT Colums to INToption Access will not be able to deal with BIGINT Primary Keys."

All of the fields with the BIGINT type in the MySQL DB are primary fields.

What type of catastrophe would I create if I were to change all of the existing BIGINT types to basic INT? I am not accessing the DB on their site as I have my own copy on my computer. Like I stated earlier, the developer doesn't recommend any type of updating (or modifying structure in this case) of the database.

There are already tens of thousands of records through the tables, and it is confusing to me as to why the developer used the BIGINT type anyway because most of the fields marked as BIGINT are auto increment ID fields, with the exception of one table, the max value so far just hit only 5 digits. The one exception being that one table is in the 500000 range, not digits but actual value (being only 6 digits). If I were to change this could it possibly have any effect on something that they programmed for their software? In my understanding it is the same type just the size of the number being stored is the difference, and I couldn't see us ever reaching the BILLION mark(or whatever the max is for plain INT) where we would need the BIGINT.
Any thoughts?