Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: MS Access front against MySQL back!

    I have MS Access database with tables, queries and forms. I did convert all MS Access tables to MySQL tables. I solved autonumber to autoincrement problem.

    Next, I install MySQL server 4, ODBC driver 3.51.

    Then, I did delete all MS Access tables and made links to MySQL tables.

    I made ODBC connection, but everything works okay ONLY from local computer! If I try to access to the MS Access database from any computer in the network - it starts MS Access, but immidiately put the warning messagebox about the problem!

    It works locally, does not work from network! Why?

    Anybody knows for good MS Access, ODBC, MySQL manual?

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    When you are making your links I imagine you are connecting using a DSN ODBC connection that is local to your machine. When you open it from another machine that DSN does not exist so you get the error. You have to either manually create the DSN on every machine that your application will run from or do so in code using the API call SQLConfigDataSource.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Yes - I am using ODBC DSN to connect to MySQL. Do not know - if there is any other way to connect MS Access to MySQL ....

    Is there any simple tutorial how to use SQLConfigDataSource call? With examples? Including MySQL as the backend?

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You can connect to any datasource using ADO.

    If you want to create a DSN programtically instead, here is an example using two functions CreateDSN and RemoveDSN:

    Code:
    Option Explicit
    
    Public g_sServerName        As String
    Public g_sDatabaseName      As String
    Public g_sUID               As String
    Public g_sPWD               As String
    
    'Constant Declaration
    Private Const ODBC_ADD_DSN = 1        ' Add data source
    Private Const ODBC_ADD_SYS_DSN = 4    ' Add System DSN
    Private Const ODBC_CONFIG_DSN = 2     ' Configure (edit) data source
    Private Const ODBC_REMOVE_DSN = 3     ' Remove data source
    Private Const vbAPINull As Long = 0   ' NULL Pointer
    
    Public Const DSNName = "MySQLExportDSN"
    
    'Function Declare
    #If Win32 Then
        Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
    #Else
        Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer
    #End If
    
    Private Declare Function SQLGetInstalledDriver Lib "ODBCCP32.DLL" (ByVal lDrvList As String, ByVal lpszDriver As Long, ByVal lpszAttributes As Long) As Long
    
    Public Function createDSN(ByVal sServerName_IN As String, ByVal sDSNDBName_IN As String, ByVal sUserName_IN As String, ByVal sPWD_IN As String) As Boolean
    On Error GoTo errHandlerSection
        
        #If Win32 Then
            Dim lRet As Long
        #Else
            Dim intRet As Integer
        #End If
        
        Dim strDriver As String
        Dim strAttributes As String
        
        Dim sDriverList As String
        Dim bRetValue As Boolean
     
    '    bRetValue = SQLGetInstalledDriver(sDriverList, Len(sDriverList), Len(sDriverList))
        
        strDriver = ""
        strAttributes = ""
        
        strDriver = "MySQL"
        strAttributes = "Server=" & sServerName_IN
        strAttributes = strAttributes & ";Database=" & sDSNDBName_IN & ";DSN=" & DSNName & ";uid=" & sUserName_IN & ";Password=" & sPWD_IN
        
        lRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)
            
        If lRet = 0 Then
            createDSN = False
        Else
            createDSN = True
        End If
        
        Exit Function
        
    errHandlerSection:
        MsgBox "Cannot create DSN" & vbCrLf & "Error description : " & Err.Description, vbCritical, "Internal Error"
        createDSN = False
    End Function
    
    Public Function RemoveDSN() As Boolean
    On Error GoTo errHandlerSection
        
        #If Win32 Then
            Dim lRet As Long
        #Else
            Dim intRet As Integer
        #End If
        
        Dim strDriver As String
        Dim strAttributes As String
        
        Dim sDriverList As String
        Dim bRetValue As Boolean
        
        lRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, "MySQL", DSNName)
            
        If lRet = 0 Then
            RemoveDSN = False
        Else
            RemoveDSN = True
        End If
        
        Exit Function
        
    errHandlerSection:
        MsgBox "Cannot delete DSN" & vbCrLf & "Error description : " & Err.Description, vbCritical, "Internal Error"
        RemoveDSN = False
    End Function
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    Thank you! I would check this today with MySQL database.

    One another question - this example you did put here means - of course - that MySQL ODBC driver has to be installed at client computer. Is there any possibility to copy the driver from server computer to client computer automatically - from MS Access code? I mean - is there a possibility to have just MS Access .mdb which will do all those jobs at client computer?

    Also, when one tries to install MyODBC to a client computer - must have privileges to do that. That is just one of many questions when considering how to distribute clients for one central MS Access .mdb, linked to MySQL.

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    Quote Originally Posted by computerforce
    when one tries to install MyODBC to a client computer - must have privileges to do that. That is just one of many questions when considering how to distribute clients for one central MS Access .mdb, linked to MySQL.
    I think you can connect using ADO from a VBA module, without using ODBC. There wil possibly have to be drivers installed on each computer, but if you manage to automate/script (for example with Kixtart) a driver installation (also with admin privileges), it is at least possible to automate it, as you then can call/start the script from within access using API calls.

    Daniel
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •