[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

ODBC Call failed error using Access 2002 and MySQL

I have set up a development environment where I can successfully connect to a MySQL database on a networked 2003 Server from my XP sp3 laptop with my Access 2002 front end app using MySQL ODBC 5.1.  
When I go to duplicate the installation at a client site, I can get a successfull connection test when I am configuring MySQL ODBC 5.1 between the user and the MySQL server computer, but when I go to run my app I get "ODBC Call Failed"  as my linked tables can't find the MySQL database. (DATABASE = "leanmachinedatamysql") file DNS.
When I try to refresh the links, the Linked Table Manager comes up blank, with no list of any of the linked tables.  MySQL is installed successfully on the client server, as I can restore a back up MySQL database using Navicat, and then use a migration tool I built that uses ODBC to import data from a MS Access database.  I think I don't understand something about how DNS connections work in order to create the connection settings that will work when installed on the client network.  If I call the file connection LeanMachineDataMySQL on my system and set up the same file DNS name on their network, does the fact that the respective servers have different IP addresses break the connection?  What's the best way to connect my app to the MySQL database when installed on the client's network?
0
David Smithstein
Asked:
David Smithstein
  • 7
  • 6
1 Solution
 
Goodangel MatopeSoftware ArchitectCommented:
Well, I suspect this may have something to do with your connectionstring from the access front end. Are you certain this is allowing the correct flexibility? What user name and password are being used in the connection string? If you have hardcoded the user name and password, that may explain why a connection test works but the app does not.
0
 
souquetbressandCommented:
It seems that your issue is that your servers have different IP addresses.  If the table structure isn't identical and you haven't changed the ODBC information to point to the right server you're going to see this error because, according to Access, it can't see the server you're talking about.  

Are you positive that you've configured the ODBC to point to the correct server?  Perhaps I'm misunderstanding what you're saying.
0
 
David SmithsteinCEOAuthor Commented:
When create a file DSN called leanmachinedataMySQL I can open it in Notepad.

[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
UID=user
OPTION=262186
PORT=3306
DATABASE=leanmachinedatamysql
SERVER=192.168.2.108

When I open Linked Table Manager in Acccess it shows me that I have an ODBC connection where DATABASE = "leanmachinedatamysql"

So what is actually stored in the linked tables information?  Is the IP address in there somewhere, or does it referecence the file DSN information?  If it uses the DSN info, and I keep the database name connsistent, can't I put my app on another network and use the File DSN information to direct the link to the correct server?  
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
David SmithsteinCEOAuthor Commented:
I'll double check the username and password, but the idea was to use a consistent convention so that all I needed to do was reconfigure the file DSN information.  Although I've been trying to figure out DSN-less connections options.  Surely someone has figured out the best way to go about this that is the most reliable and easiest to deploy.  Can I do something in VBA that will make setting up a new installation easier?
0
 
souquetbressandCommented:
Let's clarify a few things:

1. Are you setting the app up on a second, entirely different network?  
2. If so, are you positive that the server IP is correct?  
3. Is the database name positively correct?  Matching case etc?


The linked table manager stores the information you input into your ODBC DSN.  It uses that information to connect directly to the database that you request in that ODBC.  The IP address is stored in the ODBC DSN that you created.  

I have seen this problem when you use a User Data Source instead of a System Data Source.  It's rare, but it happens.  Try configuring the link both ways.  The issue is definitely with how you're configuring the ODBC connection.  We'll get to the bottom of it.  This could also be a timeout issue that we may need to look into if the above does not solve the problem.  

Also, please see the link below for how to set up a connection via code.  Click to the link to go the section with the MySQL connection information:

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL

So long as you're reproducing the database and connection schema you should not get an ODBC call error.  Please let me know if this helps.
0
 
souquetbressandCommented:
One more thing:

Make sure the user that you've created to connect to the database through Access has the necessary credentials to access the data you're requesting in the ODBC.
0
 
David SmithsteinCEOAuthor Commented:
30 seconds of glory, then back into the pool of dispare......

I found DSN-less connection code at http://support.microsoft.com/kb/892490.

So I made a temporary table with the table names for all 262 tables of my app and the name of the corresponding tables in the MySQL database. (Both are named the same, except MySQL converts everything to lower case. (Yes, a monstrosity only it's mother could love) then had my start up form cycle through and make all the connections based on my conneciton parameters which the only unique value would be the IP address of the client's server.  I made this field accessbile so I could easlity switch between my localhost development database and the testing database on my local network server simply by changing the server name and restarting the app.  The end user likewises has to enter the IP address once, then the app is configured to connect to their local server.

Then I made a neat little progress bar so the user knows the connection will take a few moments.  I installed it at a client site and viola....all 262 tables are connected to the server computer and I can log in.  

Then I started spot checking functionality and I started running into areas that were working fine when connected via DSN to MySQL that now give me the error message "Reserved Error (-7776); there is no message for this error"

I found references to early bugs in Ver 5.0 of MyODBC that didn't like the "_" in table names, so I checked that, but that wasn' it.  I made the table names all lower case for both databases, but that wasn't it either.

If I make the query behind one of the forms that has this issue only pull from the table with no filters or connections to other tables, I can edit the data from the query. Once I add a filter, or even make a one-to-one connection to another table, I get this error as soon as I try to enter data and leave the field.

I can also open the table from the linked table in my applicaiton and edit data without error.

Please tell me you suspect something else I can check. Do I need to do something special to my queries when I have a DSN-less connection?  ODBC Timeout settings?   -  Thanks!

Here is the code I am using:

'--------start up form Frm_EmployeeWholeNameLogin actually calls a connection form Frm_DSNLessConnection that runs the progress bar as each table gets connected
'----------(user name and password were removed)---------

On Error GoTo Err_Form_Load
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
'------Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)   - Function variables listed here for reference

  Dim Counter As Integer
 
  Counter = 1
Keepgoing:
Form.Visible = True
Form.Refresh
Form.Caption = "Connecting to " & Forms![Frm_EmployeeWholeNameLogin]![ServerIP]

  If Counter < 263 Then
   If AttachDSNLessTable(Me![TLMTableName], Me![ServerTableName], Forms![Frm_EmployeeWholeNameLogin]![ServerIP], "leanmachinedatamysql", "username", "password") Then
        '// All is okay.
        DoCmd.GoToRecord acDataForm, "Frm_DSNLessConnection", acNext
        Counter = Counter + 1
        Me!Text5.Width = Me!Text5.Width + 15
        Form.Refresh
        GoTo Keepgoing
    Else
        '// Not okay.
        Form.Visible = True
         MousePointer = 0
        Exit Sub
    End If
  End If
   
   
   DoCmd.OpenForm "Frm_EmployeeWholeNameCheck"
   DoCmd.Close acForm, "Frm_DSNLessConnection"
   
 

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
--------Module-------------------------------------------------------------

Option Compare Database
'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to MySQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
   
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
0
 
souquetbressandCommented:
Alright, I've done some pretty deep research on this issue and it seems that this error is usually tied to timestamp issues.  Do you have any timestamp fields in your tables?
0
 
David SmithsteinCEOAuthor Commented:
Yes, one of the first things I found I needed to run Access on MySQL was to make sure each table had a time stamp field.  It seems like the DSN-less connection behaves differently than a DSN connection.  The original code was for a SQL connection.  Could MysQL ODBC process a DSN-less connection differently? It seems like that was the  only thing that changed.
0
 
souquetbressandCommented:
Okay, change:

     stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

to
 
     stConnect = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=" &stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

This change is for consistency.  

Also, the error that you're getting ("Reserved Error (-7776); there is no message for this error") has been directly related to the timestamp issue.  Access does not agree with the formatting "0000-00-00 00:00:00" and thus that format has to be updated using a mask to the table or the form.

Try updating your timestamp fields in Access with a mask matching the above mentioned format.  I believe that should solve this issue.  From there we can work on troubleshooting additional problems.
0
 
David SmithsteinCEOAuthor Commented:
I don't think it's a time stamp issue since I can recreate the error simply by editing a text  field from within an open query that is only filtering for a specific record from a single table.  I'll see what happens if I use the trusted connection.
0
 
souquetbressandCommented:
http://forums.mysql.com/read.php?65,14085,14085
http://www.issociate.de/board/post/323530/Reserevd_Error_-7776_--_Urgent.html
http://www.thingsivedone.co.uk/?p=23
http://www.bigresource.com/Tracker/Track-mysql-aANHnjXF/

Just a few links showing that the error that you're getting is a timestamp issue.  I would highly recommend checking into this issue to resolve this error.  Again, once that error has been resolved we can begin to troubleshoot any remaining issues.  
0
 
David SmithsteinCEOAuthor Commented:
That was it!!!  http://www.thingsivedone.co.uk/?p=23
Once I ran a query in MySQL that put date values in all the existing timestamp fields the (-7776) error went away.  If you are ever in San Francisco I will gladly buy you all the beer you can drink. :-)
0
 
souquetbressandCommented:
Excellent!  So glad we were able to get this worked out!  If you need anything else, feel free to contact me.  :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now