Private conn As ADODB.Connection

Private mstrMYOBDatabase As String
Private mstrMYOBHostEXEPath As String
Private mintDefaultBalanceDueDays As Integer
Private mintDefaultPaymentDueDays As Integer
Private mstrDefaultDeliveryStatus As String
Private mintDefaultPaymentIsDue As Integer

Public Function CreateConnection() As ADODB.Connection
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    On Error GoTo Proc_Err
    
    If conn Is Nothing Then
        '=============================================
        'Get the MYOB connection string variables
        '=============================================
        Set db = CurrentDb
        
        strSQL = "SELECT MYOB_Database, " & _
                        "MYOB_HostExePath, " & _
                        "MYOB_DefaultBalanceDueDays, " & _
                        "MYOB_DefaultpaymentDueDays, " & _
                        "MYOB_DefaultDeliveryStatus, " & _
                        "MYOB_DefaultPaymentIsDue " & _
                 "FROM Company"
        
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        
        With rs
            mstrMYOBDatabase = Nz(!MYOB_Database, "")
            mstrMYOBHostEXEPath = Nz(!MYOB_HostExePath, "")
            mintDefaultBalanceDueDays = rs!MYOB_DefaultBalanceDueDays
            mintDefaultPaymentDueDays = rs!MYOB_DefaultPaymentDueDays
            mstrDefaultDeliveryStatus = rs!MYOB_DefaultDeliveryStatus
            mintDefaultPaymentIsDue = rs!MYOB_DefaultPaymentIsDue
        End With
        
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        
        '=============================================
        'Create the connection
        '=============================================
        Set conn = New ADODB.Connection
        With conn
            .ConnectionString = "Driver={MYOB_ODBC_AU7};" & _
                                "TYPE=MYOB;" & _
                                "ACCESS_TYPE=READ_WRITE;" & _
                                "HOST_EXE_PATH=" & mstrMYOBHostEXEPath & ";" & _
                                "DATABASE=" & mstrMYOBDatabase & ";" & _
                                "UID=" & gstrMYOBUID & ";" & _
                                "PWD=" & gstrMYOBPwd & ";" & _
                                "KEY=" & MYOB_KEY & ";" & _
                                "DRIVER_COMPLETION=DRIVER_PROMPT;" & _
                                "NETWORK_PROTOCOL=NONET;" & _
                                "SQL_LOGIN_TIMEOUT=40;" & _
                                "SQL_ATTR_AUTOCOMMIT=0;"
            
            .CursorLocation = adUseClient
            .Open
            
            Set CreateConnection = conn
        End With
    Else
        Set CreateConnection = conn
    End If
    
Proc_Exit:
    Err.Clear
    Exit Function
    
Proc_Err:
    DoCmd.Beep
    MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Error"
    
    Resume Proc_Exit
    Resume
End Function

Public Property Get cn() As ADODB.Connection
    Set cn = conn
End Property

Private Sub Class_Terminate()
    Set conn = Nothing
End Sub

Public Property Get DefaultBalanceDueDays() As Integer
    DefaultBalanceDueDays = mintDefaultBalanceDueDays
End Property

Public Property Get DefaultPaymentDueDays() As Integer
    DefaultPaymentDueDays = mintDefaultPaymentDueDays
End Property

Public Property Get DefaultDeliveryStatus() As String
    DefaultDeliveryStatus = mstrDefaultDeliveryStatus
End Property

Public Property Get DefaultPaymentIsDue() As Integer
    DefaultPaymentIsDue = mintDefaultPaymentIsDue
End Property