Hi All,

I've read a lot of threads covering MySQL and the Connection Manager, but have not found what I'm looking for.

I support an Access-based POS system, that periodically needs to connect to the SA database to retrieve information such as customer data, accounts, etc.

In previous versions, the code just opened an ADO connection to the SDB database, copied the data, and closed the connection.

For the 2008 version, I changed the connection string for MySQL (based on ConnectionStrings.com), and except for a few minor SQL syntax changes this worked fine.

However, it only works if I have Simply running. I would like to connect to the database whether or not I have Simply open. I'm confused as to the relationship between Simply, the Connection Manager, and MySQL.

I assumed MySQL would be a service running on it's own that I could connect to directly. I am wondering if Simply closes the MySQL service when it shuts down, and if so, can I configure it not to do that (i.e. have the MySQL service running at all times)?

Right now, I am testing on a stand-alone machine. When it is installed on a server and clients connect remotely, which of the following is true in order for my application to retrieve data from Simply via ODBC:

1. Simply has to be open on the database computer
2. Simply has to be open on the client computer
3. Simply has to be open on both the database and client computer

Take a look at the VBA code in the Excel reports that come with Simply 2008. You can connect to any Simply 2008 database with only the Connection Manager open, locally or across a network.

Have several Excel apps work using this - same connection string will work in Access. Make sure that you have referenced the Simply ConnectionManagerService and the SimplyConnectionManagerServiceClient from the VBA Tools menu.

Thanks Brent
I actually did look at one of the Excel sheet macros. When I tried doing the "Refresh Data" option, I was getting errors, and I did not pursue this course.
However, I will take another look and see if I can work the problem.

I'm back at this problem after being distracted by other work.

I tried a couple of the Excel workbooks, "Income Statement - Side by Side" and "Price List".  On both of these I got error # -2146232576 "Automation Error" on the following line of code:

error = dbClient.GetConnectionInfo(filename, host, port)

I was trying to connect to the "Universl.saj" sample file.  I tried while Simply 2008 was running with the Universal company file open, and with Simply shut down.  Same error either way.

Not sure why you are calling GetConnectionInfo. I believe you need an active connection before you can it.

You should be calling StartClient and then GetConnection.

Simply does not need to be running; only the connection manager service needs to be running.

>>>>>Not sure why you are calling GetConnectionInfo

It's not my code, I copied it from "Price List.xls" file installed in C:\Program Files\Simply Accounting Enterprise 2008\Reports\".  I was advised in a previous post to look at this as an example of how to connect to the Simply database.  I believe I was supposed to get which Port to use with the GetConnectionInfo method.  Except that it doesn't connect, instead I get the error described.  It's the code that gets called when I answer "Yes" to the prompt if I want to refresh the data.

Here's the complete function:

Private Sub vUseNewQuery()
    Dim sSystemDB As String
    Dim adoConn As ADODB.Connection
    Dim bValidated As Boolean
    Dim sFileName As String
    Dim lLength As Long
    Dim dbclient As New ConnectionManagerServiceClient
    Dim port As String
    Dim host As String
    Dim error As ConnectionManagerError

    'custom the file name
    sFileName = OpenFile.lpstrFile
    sFileName = LCase$(sFileName)
    lLength = Len(sFileName)
    If (Right$(sFileName, 4) = ".sai") Then
        sFileName = Left$(sFileName, lLength - 4) + Replace(Right$(sFileName, 4), ".sai", ".saj")
    End If
   
    'if the database is not opened, open the database.
    'Get the port and host information for connection.
    error = dbclient.GetConnectionInfo(sFileName, host, port)
    OpenFile.lpstrFile = sFileName
   
    sSystemDB = Left(OpenFile.lpstrFile, Len(OpenFile.lpstrFile) - 1)
    sSystemDB = sSystemDB & "i"
    bValidated = False
   
    host = LCase$(host)
    If (host = "localhost") Then
        'blank server name means to connect on localhost via pipe (if failed then automatically use tcp/ip)
        sConnection = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=;Database=simply;UID=sysadmin;PWD=;Port=" & port & ";Socket=SimplyMySql" & port & ";option=3"
    Else
        'remote connection - only use tcp/ip
        sConnection = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & host & ";Database=simply;UID=sysadmin;PWD=;Port=" & port & ";option=3"
    End If
   
    On Error Resume Next
    'First try to open with no password
    Set adoConn = New ADODB.Connection
    adoConn.Open sConnection
   
    If (Err.Number <> 0) Then
        adoConn.Close
        If (Err.Number = -2147467259) Then 'Database is opened exclusively
            MsgBox Left(sSystemDB, Len(sSystemDB) - 3) & "sai" & Chr(13) & "This file is already in use by another application." & Chr(13) & "Please select a new name or close the file in use.", _
                vbOKOnly + vbExclamation, "Select a database for generating the report " & sName
            vFindFile
            Exit Sub
        End If
           
        Err.Clear
        'Ask the user to enter a user id and password
        bValidated = False
        Do While (Not bValidated)
            LoginFrm.UserIdTxt.SetFocus
            LoginFrm.Show
            If (host = "localhost") Then
                'blank server name means to connect on localhost via pipe (if failed then automatically use tcp/ip)
                sConnection = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=;Database=simply;UID=" & LoginFrm.userId & ";PWD=" & LoginFrm.userPassword & ";Port=" & port & ";Socket=SimplyMySql" & port & ";option=3"
            Else
                'remote connection - only use tcp/ip
                sConnection = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & host & ";Database=simply;UID=" & LoginFrm.userId & ";PWD=" & LoginFrm.userPassword & ";Port=" & port & ";option=3"
            End If
           
            adoConn.Open sConnection
            LoginFrm.UserIdTxt.Text = ""
            LoginFrm.PasswordTxt.Text = ""
            If (Err.Number <> 0) Then
                adoConn.Close
                If (Err.Number = -2147467259) Then 'Database is opened exclusively
                    MsgBox Left(sSystemDB, Len(sSystemDB) - 3) & "sai" & Chr(13) & "This file is already in use by another application." & Chr(13) & "Please select a new name or close the file in use.", _
                        vbOKOnly + vbExclamation, "Select a database for generating the report " & sName
                    vFindFile
                    Exit Sub
                End If
           
                MsgBox "You may not have sufficient rights to access Simply Accounting company data from other products, or your user id or password may not be correct." & Chr(13) & "Please contact your system administrator for assistance.", vbOKOnly + vbInformation, "Simply Accounting"
                bValidated = False
                Err.Clear
            Else
                bValidated = True
            End If
        Loop
        Err.Clear
    Else
        bValidated = True
    End If

    If (bValidated) Then
        vBuildReport adoConn
    End If
End Sub

I just copied this code into an Access module, and in this case I don't get the automation error, but GetConnectionInfo returns the value 10.  Looking this up in the Object Browser, this stands for the constant ConnectionManagerError_Error_MySQL_NotRunning.

 Well, that confirms my earlier suspicion that MySQL is getting shut down when Simply closes.

Could this be an issue with the type of installation?  I didn't do the setup, so the tech who did may have chosen the incorrect options.  Should he have chosen a "data only" installation?

Can someone point me to a link that explains how to properly install the server software so that MySQL is always running (regardless of whether Simply is running on the server)?  Is there a way to check what type of install was done from the Simply interface?

Piecing together the hints I have gotten in this thread and a couple others, my latest attempt is as follows:

Public Sub ConnectToSimply2()
    Dim dbclient As New ConnectionManagerServiceClient
    Dim port As String
    Dim host As String
    Dim error As ConnectionManagerError
    Dim sFileName As String
    Dim db_version As String
   
    sFileName = "C:\Documents and Settings\All Users\Documents\Simply\2008\Samdata\Enterprise\Universl.SAI"
   
    error = dbclient.GetConnectionInfo(sFileName, host, port)
   
    If error = ConnectionManagerError_Error_MySQL_NotRunning Then
        error = dbclient.StartClient("C:\Documents and Settings\All Users\Documents\Simply\2008\Samdata\Enterprise\Universl.SAI", "MyServer.MyDomain.on.ca")
        error = dbclient.GetConnection(sFileName, host, db_version)
    End If
   
End Sub

The StartClient returns 0, which I assume means it worked (what does this method actually do?).

 The GetConnection method returns 8, ConnectionManagerError_Error_MySQLInit.

What am I supposed to fill in the dbVersion parameter with?

Finally, if I do get the GetConnection to work, will this start MySQL, and if so, should I be calling some sort of CloseConnection method when I am done?

Where is the documentation for this API???  Googling on "ConnectionManagerServiceClient" only returned this thread!!

You are right about the MySQL database. That is actually the use of the connection manager: it starts and close MySQL server as needed. This is why you need to have SImply open (or some other 3rd party connecting to simply) to make and ODBC or MySQL connection.

GetConnectionInfo only returns info if the connection is already made. That's what I understand from the SDK doc.

It has nothing to do with the installation. A data only installation installs only the connection manager, without the Simply Accounting client; and it's buggy in some scenario.

When calling StartClient, change the extension from SAI to SAJ. Do not past the host manually. StartClient will return the host in the last parameter you provide, eg:

 error = dbclient.StartClient("C:\Documents and Settings\All Users\Documents\Simply\2008\Samdata\Enterprise\Universl.SAI", host)

Then get the port in the same way usign GetConnection:

error = dbclient.GetConnection(sFileName, port, db_version)

db_version is "5.0.38"

This will start the MySQL server and you can then make an ODBC or MySQL connection.

The connection manager release the connection and closes the server automatically, when the (ODBC)connection is close for some time. You can also use CloseDBServer.

The documentation is available as part of the SDK. You can get it by becoming a Sage Development Partner for Simply Accounting.

Hope his helps.

 

 

 I solved this issue with the Simply that need to be open in order to acces data with third part application in a different way. I observed that if you are connected with to the database with  something   else than Simply when you close Simply the server is not stopping and you can continue to acces the database. So I design an application in Visual Foxpro that is starting when Windows is starting and is  waiting until Simply is starting and is connecting to the database and is keeping the connection open without doing nothing else. After that I can close Simply.  I work with Visual Fox but I guess you can use any language.

Ciprian 

We also created a windows service that will open any number of database making sure they are always available. Once the service is configured, there is nothing else to do; it will start the MySQL server and open a connection on each database. The configuration tool indicates the port number to use for each database, which should not change as their are always opened in the same order.

When the computer restart, the service starts and open the connection again. No need to open Simply at all. Saves time and ressources.

Thanks to Pierre's info I have been able to connect to the database without having to start Simply.  This is on the LocalHost, the next step is to try a remote connection from one of the client PC's.

As I understand each, each client PC will need ConnectionManager installed.  So, does installing ConnectionManager by itself take up a Simply license?  All I need is the ability to look up data in some of the tables.

This is great news for all you IT professionals.

It would really be nice if Sage or someone else could provide a service to keep the database open for the common, software challenged, users of Simply Accounting 2008. In this way those of us who aren't able to implement these solutions on their own could continue to use ODBC in the same way as in the past.

 

 

 

 

You need the connection manager on each PC only because you need the dll and the COM wrapper for those dll (to use them in VB for example). The connection manager on the computer itself does not need to be running, unless you are accessing local data.

I may be wrong on this, but your simply license is for the actual Simply Accounting client that are opened.

We did create a service that does just that and are using it ourself and with some of our client.

We'd be happy to share it for free if people are interested. However, there a no garantee. If there is a high demand, we could provide a support program of some sort.

Anyone interested, please post your though on this...