basgeneral.bas

運動會或各式活動秩序冊製作及檢錄表製作管理系統

Attribute VB_Name = "basGeneral"
Option Explicit
Public Enum eM_NodeTypes
    eM_Enterprise = 0
    eM_Group = 0
    eM_User = 1
    eM_Branch = 2
    eM_Folder = 3
    eM_Clip = 4
    eM_Sheet = 5
End Enum
Public Enum eM_DatabaseTypes
    eM_DatabaseTypes_Undefined = 0
    eM_DatabaseTypes_OracleMSDA = 1
    eM_DatabaseTypes_OracleODBC = 2
    eM_DatabaseTypes_SQLserver = 3
    eM_DatabaseTypes_MicrosoftJet = 4
    eM_DatabaseTypes_MicrosoftAccess97File = 5
    eM_DatabaseTypes_MicrosoftAccess2KFile = 6
    eM_DatabaseTypes_DSNFile = 7
    eM_DatabaseTypes_AccessFile = 99
End Enum

Public Enum eM_LastOpened
    eM_LastOpened_Access = 0
    eM_LastOpened_Network
End Enum

Public Enum eM_NodeProperty
   eM_NodeID = 0
   eM_NodeCaption = 1
   eM_NodeDescription = 2
   eM_ParentNodeID = 3
   eM_NodeFunction = 4
   eM_NodeMemo = 5
   eM_NodeBGPicture = 6
   eM_CreateDate = 7
   eM_UpdateDate = 8
   eM_ExpireDate = 9
End Enum

Public Enum eM_DataType
   eM_Error = -1 'Error data type
   eM_Bit = 0 'Integer data with either a 1 or 0 value.
   eM_int = 1 'Integer (whole number) data from -2^31
              '(-2,147,483,648) through 2^31 - 1 (2,147,483,647).
   eM_Smallint = 2 'Integer data from 2^15 (-32,768) through 2^15 - 1
                           '(32,767).
   eM_Tinyint = 3 'Integer data from 0 through 255.
   eM_Decimal = 4 'Fixed precision and scale numeric data from -10^38
               '-1 through 10^38 -1.
   eM_Money = 5 'Monetary data values from -2^63
                        '(-922,337,203,685,477.5808) through 2^63 - 1
                        '(+922,337,203,685,477.5807), with accuracy to a
                        'ten-thousandth of a monetary unit.
   eM_Smallmoney = 6 'Monetary data values from -214,748.3648 through
                  '+214,748.3647, with accuracy to a
                  'ten-thousandth of a monetary unit.
   eM_Float = 7 'Floating precision number data from -1.79E + 308
                        'through 1.79E + 308.
   eM_Real = 8 'Floating precision number data from -3.40E + 38
                       'through 3.40E + 38.
   eM_Datetime = 9 'Date and time data from January 1, 1753, to
                           'December 31, 9999, with an accuracy of
                           'three-hundredths of a second, or 3.33
                           'milliseconds.
   eM_Smalldatetime = 10 'Date and time data from January 1, 1900,
                                'through June 6, 2079, with an accuracy of
                                'one minute.
   eM_Timesstamp = 11 'A database-wide unique number.
   eM_Uniqueidentifier = 12 'A globally unique identifier (GUID).
   eM_Char = 13 'Fixed-length non-Unicode character data with a
                       'maximum length of 8,000 characters.
   eM_Varchar = 14 'Variable-length non-Unicode data with a maximum of
                          '8,000 characters.
   eM_Text = 15 'Variable-length non-Unicode data with a maximum
                       'length of 2^31 - 1 (2,147,483,647) characters.
   eM_NChar = 16 'Fixed-length Unicode data with a maximum length of
                        '4,000 characters.
   eMtNVarchar = 17 'Variable-length Unicode data with a maximum
                           'length of 4,000 characters. sysname is a
   eM_NText = 18 'Variable-length Unicode data with a maximum length
                        'of 2^30 - 1 (1,073,741,823) characters.
   eM_Binary = 19 'Fixed-length binary data with a maximum length of
                         '8,000 bytes.
   eM_Varbinary = 20 'Variable-length binary data with a maximum
                            'length of 8,000 bytes.
   eM_Image = 21 'Variable-length binary data with a maximum length of"
                        '2^31 - 1 (2,147,483,647) bytes.
End Enum

Enum eM_Rights
    perselect = 1
    perInsert = 2
    perUpdate = 4
    perdelete = 8
    perDownload = 16
    perPrint = 32
End Enum

Public Const DEF_CUSTOM_SQL As String = "Custom SQL"
Public Const DEF_REGISTRY_CONNECTIONS As String = "Connections"
Public Const DEF_REGISTRY_SETTINGS As String = "Settings"

Public Const DEF_ORACLE_CLIENT As String = "Oracle (Needs Client)"
Public Const DEF_ORACLE_ODBC As String = "Oracle (ODBC)"
Public Const DEF_SQL_SERVER As String = "SQL Server"
Public Const DEF_DSN_FILE As String = "DSN File"
Public Const DEF_ACCESS As String = "Access"

Public Const DEF_ACCESS97_OLEDB As String = "3.51"
Public Const DEF_ACCESS2K_OLEDB As String = "4.0"

Public dbObj As ADODB.Connection
Public dbPath As String
Public dbConnectionString As String
Public dbType As eM_DatabaseTypes
Public dbUser As String
Public dbPassword As String
Public LastOpenedType As eM_LastOpened

Public dbCompanyName As String
Public dbCompanyDescription As String

Public Function BuildConnectString(ByVal databaseType As eM_DatabaseTypes, ByVal serverOrFilename As String, Optional ByVal databaseName As String, Optional ByVal UserName As String, Optional ByVal Password As String) As String
    Select Case databaseType
        Case eM_DatabaseTypes_OracleMSDA
            BuildConnectString = "Provider=MSDAORA;Data Source=" & serverOrFilename & ";User ID=" & IIf(UserName <> "", UserName, "") & ";Password=" & IIf(Password <> "", Password, "") & ";" & IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
        Case eM_DatabaseTypes_OracleODBC
            BuildConnectString = "DRIVER={Microsoft ODBC for Oracle};SERVER=" & serverOrFilename & ";UID=" & UserName & ";PWD=" & Password & ";" & IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
        Case eM_DatabaseTypes_SQLserver
            BuildConnectString = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=" & serverOrFilename & ";User ID=" & IIf(UserName <> "", UserName, "") & ";Password=" & IIf(Password <> "", Password, "") & ";" & IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
        Case eM_DatabaseTypes_DSNFile
            BuildConnectString = "Provider=MSDASQL;DSN=" & serverOrFilename & ";UID=" & IIf(UserName <> "", UserName, "") & ";PWD=" & IIf(Password <> "", Password & ";", "") & ";" & IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
        Case eM_DatabaseTypes_MicrosoftAccess2KFile, eM_DatabaseTypes_MicrosoftAccess97File
            BuildConnectString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & serverOrFilename & ";DefaultDir=" & DetermineDirectory(serverOrFilename) & ";"
    End Select
End Function

Public Function ResolveTable(inputTable As String) As String
    ResolveTable = IIf(InStr(1, inputTable, " ") <> 0 Or IsNumeric(Left(inputTable, 1)), "[" & inputTable & "]", inputTable)
End Function
Public Function BuildDatabaseName(ByVal databaseType As eM_DatabaseTypes, ByVal databasePath As String, ByVal lastDatabaseOpenType As eM_LastOpened) As String
    If databaseType = eM_DatabaseTypes_MicrosoftAccess2KFile Then
        BuildDatabaseName = "Access 2000: " & DetermineFilename(databasePath)
    ElseIf databaseType = eM_DatabaseTypes_MicrosoftAccess97File Then
        BuildDatabaseName = "Access 97: " & DetermineFilename(databasePath)
    ElseIf databaseType = eM_DatabaseTypes_OracleMSDA Or databaseType = eM_DatabaseTypes_OracleODBC Then
        BuildDatabaseName = "Oracle: " & databasePath
    ElseIf databaseType = eM_DatabaseTypes_SQLserver Then
        BuildDatabaseName = "SQL Server: " & databasePath
    ElseIf databaseType = eM_DatabaseTypes_DSNFile Then
        BuildDatabaseName = "DSN Source: " & databasePath
    ElseIf databaseType = eM_DatabaseTypes_AccessFile Then
        BuildDatabaseName = "Access Database: " & DetermineFilename(databasePath)
    End If
    If lastDatabaseOpenType = eM_LastOpened_Network Then
        BuildDatabaseName = "Network - " & BuildDatabaseName
    End If
End Function

Public Function DetermineDirectory(inputString As String) As String
Dim pos As Integer
    pos = InStrRev(inputString, "\", , vbTextCompare)
    DetermineDirectory = Mid(inputString, 1, pos)
End Function
Public Function DetermineFilename(inputString As String) As String
Dim pos As Integer
    If InStr(1, inputString, "\") = 0 Then
        DetermineFilename = inputString
    Else
        pos = InStrRev(inputString, "\", , vbTextCompare)
        DetermineFilename = Mid(inputString, pos + 1, Len(inputString) - pos)
    End If
End Function