Results 1 to 15 of 15
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    Database Connectivity Issue to MYSQL

    I have a MYSQL database which I am using data from to my Access Database through the ODBC. In my Access I have the linked tables from the MYSQL. I created my own Tables and there is a filed in my Table which is related to a key field in one of the linked Tables from MYSQL to retrieve some info. I have had this running for about a year without an issue. However recently I have run into an issue on 2 laptops from the from the 6 laptops which run these databases. When I go to certain records from the linked tables the data all appears as #Name?



    However retrieving the same records on the other 4 laptops have no issue. I am trying to figure out what would cause this. I have checked the memory as well as the Regional settings but can't see any difference on these 2 laptops from the other 4.

    Any ideas would be very helpful.

    Thanks,

    Sam

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    did you do any upgrade to those laptops?
    are all pcs on the same version OS?
    save version of Access?

    was this a recent issue?

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    in addition to Ranmans 'same'

    same odbc driver?

    and just to clarify

    When I go to certain records from the linked tables the data all appears as #Name?
    do you mean in a table of say 1000 records, perhaps 20 of them appear as #name? or do you mean all records of certain tables?

    There was in an issue in sql server where autonumber primary keys were defined as bigint, which was not recognised by access resulting in whole tables displaying #name. Just wondering if you are having the same problem with mySQL

  4. #4
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Sorry. been away for a few days. All Pcs running windows 10 Pro with Office 2016. I haven't specifically done any upgrades that I am aware of besides the regular window automatic updates. They were all using the same driver 5.3.6

    On my machine I tried upgrading to 5.3.8 to see if this would make a difference but it didn't. I also tried completely removing office 2016 and ODBC drivers and reinstalling both but still same issue.

    I am not sure what it is but seems to be some type of processing issue. The table from the MySQL has 3500 records. The table in Access has 600 records and pulls matching info for each of these records from the MySQL Table. I have run a few tests to try and isolate the issue.

    1. When I return all 600 records in the query which the forms runs from as I scroll through the records using the form's next button, it always crashes with the error when it comes to a specific record.
    2. I modified the query to only show this record which crashes and the form shows the info properly.
    3. I modified the query to only show the records for June and again it crashes at that record. Total records were 98
    4. I modified the query to show the records for a couple of days. Total records were 12 but this time the form displayed all records properly including this record.

    So while this record as well as some others seem to cause an issue (only on 2 of the machines), it works fine when then population is smaller.

    Anyone have any ideas where to look next.

    Thanks
    Sam

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not really. Could be the record has a corruption of some sort - have you compacted and repaired - and rebuilt the indexes in MySQL

    Alternatively perhaps there is something in the query causing a problem - an unhandled null for example

  6. #6
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Did a compact and reindex but to no avail.

    If there was an issue with the record of unhandled null, wouldn't that effect every laptop. Why would only 2 running the same query crash?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    each user has their own copy of the front end - if the ones that are failing is slightly corrupted it is possible in their version that a null is knocking it over the edge. It is just one of the things to check.

  8. #8
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    No. The Access front end is located on the network which everyone access to the same acdb file.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that is probably the problem then. Each user should have their own copy of the front end - in much the same way as each user has their own copy of excel/word etc. When users share a front end, small corruptions can start to appear as inconsistencies. Then suddenly it will stop working. May take a few days, months even a few years, but it will happen at some point.

    Make a new db, copy the objects from the old front end - suggest in the order of queries, modules, forms then reports, compacting between each object type. then relink to your backend and distribute a copy to each user - and bin the shared front end

  10. #10
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    But that would mean every time I modify a form or query or report on the front end, I would need to update each copy. Also I forgot to mention that even when I make a copy locally on my desktop and try to run it I have the same issue even though it is a local copy and no one is accessing it.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    But that would mean every time I modify a form or query or report on the front end, I would need to update each copy
    correct. There are plenty of threads on this and other forums as to how to distribute front ends easily. Also if you look around, you'll find plenty of threads started by people who shared their front end and lost everything. Making a copy will not necessarily remove the corruption, what you have copied may already be corrupted.

    But to step back, I'm not saying that is the problem, just that it is a possible reason.

  12. #12
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Ok. I suppose I can try this. Not sure if you can help with this. I believe the difference between the front end is the tables being the back-end and the forms, queries and reports being the front end. I once used the access wizard to split it and is all it did was move the tables to a separate file and linked them to the front-end.

    That being said, Can I simply create a new Access file and import the tables to it. Then make a new access file and link these tables in and copy in my forms, queries and reports and have this file copied on each person's computer, but leave the file with the tables on the network.

    Will this work?

    Thanks,

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    didn't think you could use the access wizard to split the backend of to MySQL.

    If your backend is MySQL, you should be linking to that - so I don't understand your question.

    You need to make sure you have a clean front end. There are a number of ways to do this if you google it. But in principle

    1. Create a new db
    2. create the linked tables to the MySQL backend
    then import each object type, one at a time and compacting after each import. I would do them in this order
    3. queries - then compact
    4. modules - then decompile and compact
    5. forms - then decompile and compact
    6. reports - then decompile and compact

    if any import fails - that implies a corruption (not necessarily relevant to your particular issue) which means you may not be able to import that particular object, so you would have to recreate it

    7. Ensure all modules have Option Explicit at the top, just below Option Compare database
    8. finally, in the vba editor, select debug>compile. If any errors, fix the errors and repeat until no errors reported.
    9. That will be as clean as you can get it. So put a copy in a safe place as the master and distribute to all users.

    To be extra safe, rather than distributing a .accdb, create and distribute a .accde


    to decompile you will need to close the app, open access with the decompile switch then open the app from this instance of access

    To make it easier to open with the decompile switch I create a shortcut to the access program and add the decompile switch - it should look something like this

    "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

    you are using 2016, so Office14 will be something else. And if you are running 64bit access, the (x86) won't be there. Note the space before /decompile

    If the problem persists, the implication will be it is something to do with machine settings

  14. #14
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Thanks for the information. To explain the first part of your comment with MySQL.

    I have a database in MySQL which I have linked into an Access database. I have then added some tables into this database where I have additional information. I have a key field in these tables which I use to link to the tables from the MySQL and using quesries retrieve the information from the MySQL table that is relative to my records.

    So what I am thinking to do now is create a separate Access file with these Access Tables and then create a new Front-End Access file where I link both the MySQL tables as well as the Access Tables from the other Access file and then bring in the other objects as you suggested.

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    yes the front end should consist only of queries, forms, reports and modules

    put the access tables in an access back end and link to them in the front end as you would for mySQL

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

Similar Threads

  1. MySQL View issue
    By Maze in forum Misc
    Replies: 1
    Last Post: 11-09-2016, 07:12 AM
  2. Can't update MySql Database
    By GegeMArtinelli in forum Access
    Replies: 7
    Last Post: 06-02-2014, 02:21 AM
  3. linked mysql database
    By nkuebelbeck in forum Misc
    Replies: 3
    Last Post: 05-30-2013, 09:58 AM
  4. Database connectivity Issue
    By mkc80 in forum Access
    Replies: 1
    Last Post: 11-15-2012, 04:02 AM
  5. Replies: 5
    Last Post: 05-16-2012, 12:48 AM

Posting Permissions

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