Access Front End and MySQL Database

When I first started down this slippery slope 2 or 3 weeks ago, I first tried to download and install 5.1 on my local system. I can't remember the problem now, but I could never get it to install, gave up, and went to 5.0 Here is the info on what I am using:

Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 107
Server version: 5.0.77-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

I am running this on an HP Xeon workstation, with XP professional SP 3.

Here is some more info:
I have a much smaller file in another Access database, with around 2200 records. I used Navicat to import this file, VerifiedMembers, into the MySQL database on Bluehost. Then I closed Navicat, opened the CCVF.mdb file, and linked to VerifiedMembers with ODBC. I can easily open the table in datasheet view and everything looks perfect.

So, I am beginning to think that there is something about the way my big files are uploaded that prevents me from seeing the data. I can't use Navicat because there is a 51.5 MB limit imposed by Bluehost for uploading. Basically I had to download a large self-extracting CSV file, extract it, use Navicat to import the text file into a local MySQL table (already set up with the correct structure), then use MySQL to dump an SQL table file. After zipping with WinZip, the file was below the 51.5 MB limit, and I could use file manager on my Bluehost cPanel to extract it. Finally, I used Putty execute the .sql file and create the tabel in my online database. I had to use Navicat because the CSV file has dates as M/D/YYYY and I couldn't find a way to get the date information correctly into the MySQL table. Navicat seems to take care of this problem, and the table winds up with the correct date values in Bluehost.
 
Wait, I think you misunderstood my earlier suggestion. I didn't say to try MySQL 5.1; only to try MyODBC 5.1; that's the MySQL's ODBC driver. I don't know of a MyODBC 5.0 and want to make that clear.

The hows of import won't really matter because in ODBC context, we're basically translating between the MySQL's table definition and what Access perceives it to be and you've already set up the table.

The link I gave you earlier indicates that if you use MySQL's DATE data type, Access can have problems with that data type and advises to use DATETIME instead. Is that the case with your dates?

With your VerifiedMember table, can you actually edit the table? Does it have DATE or any MySQL's custom data types?
 
Whoops, my eyes are getting tired. I will try the 5.1 MyODBC later and see what happens. Yes, I can edit from Access -- I changed a couple of records and checked back online to see if they were changed correctly. There are two date fields that are in M/D/YYYY format, and they look ok. They are both DATE/TIME fields.
 
Banana,
MyODBC 5.1 produces the same results. Also, Open Office Base works perfectly, although slowly, with either version. I will grind through the document you suggested to see if there is anything else. I really appreciate your help in solving this problem!
 
If you're still stumped, another option is to strip down to only the problematic table, zip it up and upload the access database and the .sql file to create the table in MySQL. I can then take a look at it.
 
Banana,
I think the problem is solved -- and I can't thank you enough for all your help! One of the marvelous things about the net is being able to focus more than one mind on a problem. As I suspected from the beginning, it turned out to be something incredibly simple. My primary key is an integer, usually 7 digits, and I had defined it in my Access table as a long integer, which then becomes bigint in SQL (20 digits, apparently). Then I noticed that the key in the tables that actually work were designated as int in SQL, length 11. By changing the SQL structure from bigint to int, I can now see the data, and don't even need a timestamp field.

Thanks again for your help -- I have enjoyed the exchange!
 
I'm glad you were able to get it sorted.

I should have had asked for the table definitions in start but didn't think to do so. It's funny because I was helping someone else with a MySQL-Access problem and it wasn't until I had a look at the definition before problem became obvious.

Goes to illustrate the importance of verifying that your datatypes are compatible and translated correctly.
 

Users who are viewing this thread

Back
Top Bottom