Results 1 to 7 of 7

Migrating Access 97 to Win 7-64 -> ODBC problem

  1. #1
    maddaze is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Aug 2013
    Location
    Bordeaux, France
    Posts
    4

    Migrating Access 97 to Win 7-64 -> ODBC problem

    I have an Access 97 db that I've been using as the front end to a MySQL db, first under Win XP, then Win 7-32. Please note in passing that Access 97 works fine under Win 7-32.



    I've just recently upgraded my system to Win 7-64 and I've been fighting my way thru the problems of getting everything to work together. But now I'm stuck.

    The 32-bit ODBC connection I created using the MySQL ODBC connector changes long varchar fields (over 255 chars) to Access text fields and queries containing those fields no longer work. Of course, these queries worked perfectly using ODBC under Win 7-32. Help!! I'd hate to lose the data in the long varchar fields.

    Any suggestions? (Other than upgrading Access! I retired ten years ago after spending hundreds of hours in app development using Access 97. I don't want to spend the time or money to upgrade Access.)

    Harvey

  2. #2
    vincent-leeway is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    36
    I guess you should go for the relevant MySQL ODBC driver for Windows 7.

  3. #3
    maddaze is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Aug 2013
    Location
    Bordeaux, France
    Posts
    4
    Quote Originally Posted by vincent-leeway View Post
    I guess you should go for the relevant MySQL ODBC driver for Windows 7.
    OK. Which one is that?

    Harvey

  4. #4
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by maddaze View Post
    The 32-bit ODBC connection I created using the MySQL ODBC connector changes long varchar fields (over 255 chars) to Access text fields and queries containing those fields no longer work. Of course, these queries worked perfectly using ODBC under Win 7-32. Help!! I'd hate to lose the data in the long varchar fields....
    MySQL doesn't support VARCHAR fields over 255 characters. Anything over the 255 limit will either be stored in a TEXT or BLOB instead. And both of those data types should translate into Access Memo Fields, not Text.

  5. #5
    maddaze is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Aug 2013
    Location
    Bordeaux, France
    Posts
    4
    Quote Originally Posted by Rawb View Post
    MySQL doesn't support VARCHAR fields over 255 characters. Anything over the 255 limit will either be stored in a TEXT or BLOB instead. And both of those data types should translate into Access Memo Fields, not Text.
    Quoting from dev.mysql.com/doc/refman/5.0/en/char.html: The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

    As it happens, I'm running MySQL 5.0.96 and typically make my varchar fields 4 096 characters. Old habit going back several decades to when I developed a database in Fortran II that handled varchar fields. <nostalgic smile>

    I've just created a Win7-32 bit installation and have confirmed that 1) the MySQL ODBC 5.2 connector does not map varchar fields correctly in Access 97, and 2) the 3.51 connector does.

    Do I understand you to be suggesting that if I convert the varchar fields to text, the MySQL ODBC connector 5.2 will map them correctly to Access 97 memo fields? I suppose I could break down and run a little test to discover the answer myself.

    Harvey

  6. #6
    maddaze is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Aug 2013
    Location
    Bordeaux, France
    Posts
    4
    Problem resolved: On the Win7-64 bit machine, I uninstalled all MySQL ODBC drivers, installed the 64-bit ODBC 3.51 driver, relinked the MySQL tables to the Access 97 database, and everything seems to work as before. <silly grin>

    Harvey

  7. #7
    vincent-leeway is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    36
    great! way to go.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Migrating Access 2010
    By cheyanne in forum Access
    Replies: 2
    Last Post: 06-13-2012, 12:06 PM
  2. Replies: 0
    Last Post: 12-01-2011, 10:23 AM
  3. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  4. Migrating
    By prstoessel in forum Access
    Replies: 7
    Last Post: 08-02-2011, 01:41 PM
  5. Migrating Access
    By teckles in forum Access
    Replies: 2
    Last Post: 09-16-2010, 03:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums