Home > Visual Basic > Membuat fungsi koneksi untuk beberapa database (Firebird, MySQL, Ms SQL Server dan Ms Access)

Membuat fungsi koneksi untuk beberapa database (Firebird, MySQL, Ms SQL Server dan Ms Access)

1. Tambahkan sebuah modul dan copy paste kode berikut :

Public conn As ADODB.Connection

Public Enum TIPE_DATABASE
   FIREBIRD = 1
   MYSQL = 2
   SQLSERVER = 3
   MSACCESS = 4
End Enum

Public Function connToDB(ByVal tipeDb As TIPE_DATABASE, Optional ByVal serverName As String = "", _
   Optional ByVal userName As String = "", Optional ByVal userPass As String = "", _
   Optional ByVal dbPath As String = "", Optional ByVal dbName As String = "") As Boolean

   Dim strCon As String

   On Error GoTo errHandle

   Select Case tipeDb
      Case FIREBIRD
         strCon = "DRIVER=Firebird/Interbase(r) Driver;UID=" & userName & ";PWD=" & userPass & ";" & _
         "DBNAME=" & serverName & ":" & dbPath & "\" & dbName & ""

      Case MYSQL
         strCon = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & serverName & ";DATABASE=" & dbName & ";" & _
         "UID=" & userName & ";PWD=" & userPass & ";OPTION=3"

      Case SQLSERVER
         strCon = "Provider=SQLOLEDB.1;User ID=" & userName & ";Pwd=" & userPass & ";" & _
         "Initial Catalog=" & dbName & ";Data Source=" & serverName & ""

      Case MSACCESS
        If Len(userPass) > 0 Then
           strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & dbPath & "\" & dbName & ";" & _
           "Jet OLEDB:Database Password=" & userPass & ""
        Else
           strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & dbPath & "\" & dbName & ""
        End If
   End Select

   Set conn = New ADODB.Connection
   conn.ConnectionString = strCon
   conn.Open

   connToDB = True

   Exit Function
errHandle:
   connToDB = False
End Function

2. Pada form tambahkan sebuah objek command dan set properties Name = cmdTesKoneksi

copy paste kode berikut :

Private Sub cmdTesKoneksi_Click()
   Dim result  As Boolean

   'Firebird
   result = connToDB(FIREBIRD, "127.0.0.1", "SYSDBA", "masterkey", "d:\data", "db_akademik.fdb")
   Debug.Print result

   'MySQL
   result = connToDB(MYSQL, "127.0.0.1", "root", "masterkey", , "db_akademik")
   Debug.Print result

   'SQL Server 2000
   result = connToDB(SQLSERVER, "127.0.0.1", "sa", "masterkey", , "db_akademik")
   Debug.Print result

   'Ms Access - tanpa password
   result = connToDB(MSACCESS, , , , "d:\data", "db_akademik.mdb")
   Debug.Print result

   'Ms Access - menggunakan password
   result = connToDB(MSACCESS, , , "masterkey", "d:\data", "db_akademik.mdb")
   Debug.Print result
End Sub

Untuk nilai semua parameter fungsi connToDB disesuaikan dengan kondisi komputer Anda. Selamat mencoba :)

About these ads
  1. Yakin
    May 1, 2011 at 11:21 am | #1

    Thank’s banget ya bro, you’re the best

  2. afdoal
    May 10, 2012 at 4:44 pm | #3

    o’om……..
    mau tanya tanay tentang connectionstring mySQL ^_^

    strCon = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & serverName & ";DATABASE=" & dbName & ";" & _
             "UID=" & userName & ";PWD=" & userPass & ";OPTION=3"
    

    saya cari2 di google ada juga yang seperti ini

     strConn = "DRIVER={MySQL ODBC 3.51 Driver};" _
                    & "SERVER=" & strServer & ";" _
                    & "DATABASE=manajemen_sms;" _
                    & "UID=" & UNAME & "; PWD=" & UPASS & ";" _
                    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

    nah, yang jadi pertanyaan saya, maksud dari OPTION=3 atau OPTION= 1 + 2 + 8 + 32 + 2048 + 16384 itu apa kang??

    dan dari mana kita mengetahui parameter-parameter untuk OPTION ??
    apakah dari sini??
    http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

    terima kasih sebelumnya kang….

    • May 11, 2012 at 6:36 pm | #4

      Iya om linknya udah benar kok tentang penjelasan nilai dari atribut OPTIONnya, klo option=3 itu penjelasannya ada disini :D.

  3. May 11, 2012 at 7:07 pm | #5

    Admin :
    Iya om linknya udah benar kok tentang penjelasan nilai dari atribut OPTIONnya, klo option=3 itu penjelasannya ada disini .

    terima kasih kang…
    sudah tak buat yang kyk ginian dah buat option mySQL

    Public Enum MyOption
            'recommended option values for various configurations.
            optVB = 3                      'Microsoft Access, Visual Basic
            optLargeTables = 2049          'Large tables with too many rows
            optSybasePB = 135168           'sysbase powerbuilder
            optLT_nocache = 3145731         'Large tables with no-cache results
    
            'other Option Flags
            optFieldLength = 1              'FLAG_FIELD_LENGTH       'Don't Optimize Column Width
            optFoundRows = 2                'FLAG_FOUND_ROWS         'Return Matching Rows
            optDebug = 4                    'FLAG_DEBUG              'Trace Driver Calls To myoptbc.log
            optBigPacket = 8                'FLAG_BIG_PACKETS        'Allow Big Results
            optNoPrompt = 16                'FLAG_NO_PROMPT          'Don't Prompt Upon Connect
            optDynamicCursor = 32           'FLAG_DYNAMIC_CURSOR     'Enable Dynamic Cursor
            optNoSchema = 64                'FLAG_NO_SCHEMA          'Ignore # in Table Name
            optNoDefaultCursor = 128        'FLAG_NO_DEFAULT_CURSOR  'User Manager Cursors
            optNoLocale = 256               'FLAG_NO_LOCALE          'Don't Use Set Locale
            optPadSpace = 512               'FLAG_PAD_SPACE          'Pad Char To Full Length
            optFullColumnNames = 1024       'FLAG_FULL_COLUMN_NAMES  'Return Table Names for SQLDescribeCol
            optCompressedProto = 2048       'FLAG_COMPRESSED_PROTO   'Use Compressed Protocol
            optIgnoreSpace = 4096           'FLAG_IGNORE_SPACE       'Ignore Space After Function Names
            optNamedPipe = 8192             'FLAG_NAMED_PIPE         'Force Use of Named Pipes
            optNoBigInt = 16384             'FLAG_NO_BIGINT          'Change BIGINT Columns to Int
            optNoCatalog = 32768            'FLAG_NO_CATALOG         'No Catalog
            optUseMyCnf = 65536             'FLAG_USE_MYCNF          'Read Options From my.cnf
            optSafe = 131072                'FLAG_SAFE               'Safe
            optNoTransactions = 262144      'FLAG_NO_TRANSACTIONS    'Disable transactions
            optLogQuery = 524288            'FLAG_LOG_QUERY          'Save queries to myodbc.sql
            optNoCache = 1048576            'FLAG_NO_CACHE           'Don't Cache Result (forward only cursors)
            optForwardCursor = 2097152      'FLAG_FORWARD_CURSOR     'Force Use Of Forward Only Cursors
            optAutoReconnect = 4194304      'FLAG_AUTO_RECONNECT     'Enable auto-reconnect.
            optAutoIsNull = 8388608         'FLAG_AUTO_IS_NULL       'Flag Auto Is Null
            optZeroDateToMin = 16777216     'FLAG_ZERO_DATE_TO_MIN   'Flag Zero Date to Min
            optMinDateToZero = 33554432     'FLAG_MIN_DATE_TO_ZERO   'Flag Min Date to Zero
            optMultiStatements = 67108864   'FLAG_MULTI_STATEMENTS   'Allow multiple statements
            optColumnSizeS32 = 134217728    'FLAG_COLUMN_SIZE_S32    'Limit column size to 32-bit value
    End Enum
    
    Public Function KonekToServer(strServer As String, dBName As String, UNAME As String, UPASS) As Boolean
        On Error GoTo errHandle
        Dim MyOdbcOption
        MyOdbcOption = optFoundRows + optVB + optBigPacket + optDynamicCursor + _
                    optCompressedProto + optNoBigInt + optAutoReconnect
        
        strConn = "DRIVER={MySQL ODBC 3.51 Driver};" _
                    & "SERVER=" & strServer & ";" _
                    & "DATABASE=" & dBName & ";" _
                    & "UID=" & UNAME & "; PWD=" & UPASS & ";" _
                    & "OPTION=" & MyOdbcOption
                    
        Set KonekDB = New ADODB.Connection
        KonekDB.CursorLocation = adUseClient
        KonekDB.ConnectionString = strConn
        KonekDB.Open
        
        KonekToServer = True
        
        Exit Function
        
    errHandle:
        MsgBox Err.Description & vbCrLf & vbCrLf & strConn
        KonekToServer = False
    End Function
    
    • May 11, 2012 at 7:31 pm | #6

      Wah2x si om afdol tambah keren aja nih sekarang :D
      Btw thank udah share :)

  4. afdoal
    May 12, 2012 at 4:17 pm | #7

    Admin :
    Wah2x si om afdol tambah keren aja nih sekarang
    Btw thank udah share

    sekeren-kerenya murid, yang jelas lebih keren sang guru :D

  5. joens
    May 17, 2013 at 12:57 pm | #8

    BANG setelah saya copi -sesuai petunjuk- kemudian saya debug ternyata gak bisa jalan bang-berhienti di awal kode “Public conn As ADODB.Connection” , tolong bang solusinya, trim’s

    • June 25, 2013 at 12:47 pm | #9

      Mungkin referensi Microsoft ActiveX Data Objectnya belum ditambahkan.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: