Skip to main content

Membuat Perangkat untuk Menguji Koneksi dari Access ke SQL Server

Labels:

Umumnya, rangkaian koneksi atau dalam bahasa pemrograman disebut connection string dibuat dengan menuliskan kode secara berurutan. Dalam Access, rangkaian koneksi itu ditulis secara berurutan di modul atau melalui VBA, sehingga cenderung statis, tidak bisa kita uji sesuai dengan keinginan. Untuk menguji rangkaian koneksi, kita harus memanggil fungsi atau prosedur di Imediate Window dan memastikan, apakah rangkaian koneksi sudah berjalan dengan sempurna. 

Walaupun demikian, kita bisa saja membuat alat uji untuk memastikan bahwa rangkaian koneksi telah berjalan dengan sempurna. Caranya sederhana saja, yaitu dengan membuat perangkat berupa form pengujian. Berikut ini adalah cara untuk membuat form perangkat pengujian connection string dari client ke server database SQL Server. Untuk membuat perangkat itu, ikuti langkah-langkah berikut ini:

Buatlah sebuah tabel dengan nama tblKoneksi. Untuk membuat field dalam tabel ini, ubahlah Views-nya ke Design View. Selanjutnya, isikan properti field sebagai berikut:

  1. Field Name : prefsName
    1. Data Type : Short Text
    2. Field Size : 100
    3. Caption : Nama Preferensi
    4. Indexed : Yes (No Duplicates)
    5. Atur field ini sebagai Primary Key
  2. Field Name : prefsDescription
    1. Data Type : Short Text
    2. Field Size : 200
    3. Caption : Deskripsi
  3. Field Name : prefsCaption
    1. Data Type : Short Text
    2. Field Size : 100
    3. Caption : Judul
  4. Field Name : prefsValue
    1. Data Type : Short Text
    2. Field Size : 200
    3. Caption : Nilai
  5. Field Name : prefsDataType
    1. Data Type : Short Text
    2. Field Size : 60
    3. Caption : Tipe Data

 Selanjutnya, ubahlah Views-nya ke Datasheet View untuk mengisikan data/record pada masing-masing field. Berikut ini adalah datasheet view dari tabel tblKoneksi

Nama Preferensi Deskripsi Judul Nilai Tipe Data
connAppProvider Application Provider (e.g: SQLOLEDB, SQLNCLI11, etc.) Application Provider
10
connDatabaseConnectionTest Database Connection Tes Result Result Test Database Connection
1
connDataSource Data Source or Server Name include TCP/IP Port (e.g: Server1, 1433) Data Source
10
connDriver Driver for SQL Server, must be installed first SQL Server Driver SQL Server 10
connInitialCatalog Initial Catalog/Database Name Database Name
10
connIntegratedSecurity Integrated Security (e.g: SSPI) Integrated Security
10
connNetworkLibrary Network Library (e.g: DBMSSOCN) Network Library (optional)
10
connPassword Password (password to access into the server) Password
10
connPort Port number to connect to the Server Port Number 1433 char
connServerConnectionTest Server Connection Tes Result Result Test Server Connection
1
connTrustedConnection Trusted Connection Status Trusted Connection Status Yes 10
connUserID User Id (login to server) User Id
10
suAdminUserId Administrator User (super user admin) Administrator User
10
suAdminUserPwd Administrator Password Administrator Password
10

Selanjutnya, buatlah sebuah form dengan menggunakan Blank Form. Simpan form ini dengan nama frmKoneksi. Isikan control berikut ini ke dalam form:

  1. Control Name : connAppProvider, Control Type : Text Box
  2. Control Name : connDataSource, Control Type : Text Box
  3. Control Name : connPort, Control Type : Text Box
  4. Control Name : connDriver, Control Type : Text Box
  5. Control Name : connNetworkLibrary, Control Type : Text Box
  6. Control Name : connIntegratedSecurity, Control Type : Text Box
  7. Control Name : connTrustedConnection, Control Type : Combo Box, Row Source Type : Value List, Row Source : “No”;”Yes”, Default Value : “No”, Control Name : connUserID, Control Type : Text Box.
  8. Control Name : connPassword, Control Type : Text Box
  9. Control Name : cmdTestServerConnection, Control Type : Command Button, Caption : &1. Test Server Connection
  10. Control Name : connPassword, Control Type : Text Box
  11. Control Name : connInitialCatalog, Control Type : Text Box
  12. Control Name : connDatabaseConnectionTest, Control Type : Check Box
  13. Control Name : connServerConnectionTest, Control Name : Check Box
  14. Control Name : cmdCreateDatabase, Control Type : Command Button, Caption : &2. Membuat Database
  15. Control Name : cmdTestDatabaseConnection, Control Type : Command Button, Caption : &3. Test Database Connection

Kecuali tipe kontrol (Control Type) Command Button, semua kontrol di atas diambil dari setiap nilai pada field prefsNama yang ada di tabel tblKoneksi. Jadi, di form frmKoneksi ini, kita tidak membuat kontrol yang diikat (bound) ke satu field tertentu dalam tabel, tapi kontrol yang namanya sama dengan nilai data pada field prefsNama dari tabel tblKoneksi. 

Tampilan form frmKoneksi beserta kontrol yang ada di dalamnya tampak seperti pada gambar di bawah ini. 


Pada gambar di atas, masing-masing nilai pada tabel tblKoneksi terikat pada satu kontrol dalam form frmKoneksi. Sedangkan tombol perintah (Command Button) diletakkan di bawah kontrol connPassword dan berada di bagian paling bawah dari form. 

Selanjutnya, kita menyisipkan kode VBA berikut ini:

Option Compare Database
'Aktifkan Microsoft ActiveX Data Object (versi minimum) 2.8 Library pada menu Tools References...
Sub testDatabaseConnectionString(Optional blTestOnly As Boolean = False) 
  Dim conn As ADODB.Connection
  Dim cs As String

On Error GoTo Err_Msg
  
  Set conn = New ADODB.Connection
  
  cs = "Provider=" & Me.connAppProvider & ";"
  cs = cs & "Data Source=" & Me.connDataSource & ";"
  cs = cs & "Initial Catalog=" & Me.connInitialCatalog & ";"
  If Me.connNetworkLibrary <> "" Then
    cs = cs & "Network Library=" & Me.connNetworkLibrary & ";"
  End If
  If (Me.connUserID <> "" And Me.connIntegratedSecurity <> "") _
    Or (Me.connUserID <> "" And Me.connTrustedConnection <> "No") _
    Or (Me.connIntegratedSecurity <> "" And Me.connTrustedConnection <> "No") _
    Then
      MsgBox "Pilihlah salah satu di antara isian berikut ini:" & vbNewLine & _
             "1. Integrated Security, ATAU" & vbNewLine & _
             "2. Trusted Connection, ATAU" & vbNewLine & _
             "3. User ID.", vbCritical + vbInformation
      Cancel = True
      Exit Sub
  End If
  If Me.connUserID <> "" Then
    cs = cs & "UID=" & Me.connUserID & ";"
    If Me.connPassword <> "" Then
      cs = cs & "PWD=" & Nz(Me.connPassword, "") & ";"
    End If
  End If
  If Me.connIntegratedSecurity <> "" Then
    cs = cs & "Integrated Security=" & Me.connIntegratedSecurity & ";"
  End If
  If Me.connTrustedConnection <> "No" Then
    cs = cs & "Trusted_Connection=" & Me.connTrustedConnection & ";"
  End If
  conn.Open cs, "", ""
  If blTestOnly And conn.State = adStateOpen Then
    MsgBox "Selamat, koneksi dari MS Access ke database SQL Server berhasil dilakukan. Tekan Next untuk proses berikutnya", vbInformation
    Me.connDatabaseConnectionTest = -1
    GoTo Exit_Sub
  Else
    
    MsgBox "Data koneksi sudah terpasang.", vbOKOnly + vbInformation
    boolAplikasiSudahTerpasang = True

  End If
Exit_Sub:
  conn.Close
  Set conn = Nothing
  Exit Sub
Err_Msg:
  If Err.Number = -2147217887 Or Err.Number = 3706 _
  Or Err.Number = -2147467259 Or Err.Number = -2147217843 Then
    MsgBox "Koneksi tidak tersambung. Mohon dicek kembali connection string dari MS Access ke SQL Server.", vbCritical + vbExclamation
    Me.connDatabaseConnectionTest = 0
    cmdSave_Click
    End
    DoCmd.Close acForm, CurrentObjectName
    
  Else
    MsgBox "Connection Error # " & CStr(Err.Number) & ", source: " & Err.Source & _
    Chr(13) & Err.Description
    End
  End If
  Resume Exit_Sub
End Sub
Private Sub testServerConnectionString(Optional blTestOnly As Boolean = False)
Dim conn As ADODB.Connection
Dim cs As String
Dim sqlcmd As String
Dim intCount As Integer

On Error GoTo Err_Msg
    
  Set conn = New ADODB.Connection
  cs = "Provider=" & Me.connAppProvider & ";"
  cs = cs & "Data Source=" & Me.connDataSource & ";"
  If Me.connNetworkLibrary <> "" Then
    cs = cs & "Network Library=" & Me.connNetworkLibrary & ";"
  End If
  If (Me.connUserID <> "" And Me.connIntegratedSecurity <> "") _
    Or (Me.connUserID <> "" And Me.connTrustedConnection <> "No") _
    Or (Me.connIntegratedSecurity <> "" And Me.connTrustedConnection <> "No") _
    Then
      MsgBox "Pilihlah salah satu di antara isian berikut ini:" & vbNewLine & _
             "1. Integrated Security, ATAU" & vbNewLine & _
             "2. Trusted Connection, ATAU" & vbNewLine & _
             "3. User ID.", vbCritical + vbInformation
      Cancel = True
      Exit Sub
  End If
  If Me.connUserID <> "" Then
    cs = cs & "UID=" & Me.connUserID & ";"
    If Me.connPassword <> "" Then
      cs = cs & "PWD=" & Nz(Me.connPassword, "") & ";"
    End If
  End If
  If Me.connIntegratedSecurity <> "" Then
    cs = cs & "Integrated Security=" & Me.connIntegratedSecurity & ";"
  End If
  If Me.connTrustedConnection <> "No" Then
    cs = cs & "Trusted_Connection=" & Me.connTrustedConnection
  End If
  conn.Open cs, "", ""
  Debug.Print cs
  If blTestOnly And conn.State = adStateOpen Then
    MsgBox "Selamat, koneksi dari MS Access ke Server bisa dilakukan", vbInformation
    Me.connServerConnectionTest = -1
    Me.cmdCreateDatabase.Enabled = True
  Else
    intCount = conn.Execute("SELECT COUNT(*) FROM sys.databases WHERE [Name] = '" & Me.connInitialCatalog & "'").Fields(0).Value
    If intCount <> 0 Then
      MsgBox "Maaf, database " & Me.connInitialCatalog & " Sudah dibuat sebelumnya. Silakan lanjut ke proses berikut: Test Database Connection"
      Me.cmdTestDatabaseConnection.Enabled = True
    Else
      sqlcmd = "CREATE DATABASE " & Me.connInitialCatalog & ";"
      conn.Execute sqlcmd
      MsgBox "Database " & Me.connInitialCatalog & " Sudah dibuat. Silakan lanjut ke proses berikut: Test Database Connection"
      Me.cmdTestDatabaseConnection.Enabled = True
    End If
  End If
  
  conn.Close
  Set conn = Nothing
Exit_Sub:
  Exit Sub
Err_Msg:
  If Err.Number = -2147217887 Or Err.Number = 3706 _
  Or Err.Number = -2147467259 Or Err.Number = -2147217843 Then
    MsgBox "Koneksi tidak tersambung. Mohon dicek kembali connection string dari MS Access ke SQL Server.", vbCritical + vbExclamation
    Me.connServerConnectionTest = 0
    cmdSave_Click
  Else
    MsgBox "Connection Error # " & CStr(Err.Number) & ", source: " & Err.Source & _
    Chr(13) & Err.Description
  End If
  Resume Exit_Sub
End Sub
Sub cmdSave_Click() 
  Dim rs As DAO.Recordset
  Dim ctl As Control
    
  Me.AllowAdditions = False
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblKoneksi", dbOpenDynaset)
  For Each ctl In Me.Detail.Controls
    Do While Not rs.EOF
      If Controls(ctl.Name).Name = rs.Fields("prefsName").Value Then updatePrefs ctl.Name, Nz(Controls(ctl.Name).Value, "")
      rs.MoveNext
    Loop
    rs.MoveFirst
  Next ctl
  Set rs = Nothing
End Sub

Private Sub cmdCreateDatabase_Click()
  If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then
    MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."
    Me.connServerConnectionTest = 0
    Me.connAppProvider.SetFocus
    Exit Sub
  End If
  
  If Me.connInitialCatalog = "" Or IsNull(Me.connInitialCatalog) Then
    MsgBox Me.connInitialCatalog.Controls(0).Caption & " wajib diisi."
    Me.connInitialCatalog.SetFocus
  Else
    testServerConnectionString
  End If

End Sub

Private Sub cmdTestDatabaseConnection_Click()
  If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then
    MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."
    Me.connServerConnectionTest = 0
    Me.connDatabaseConnectionTest = 0
    Me.connAppProvider.SetFocus
    Exit Sub
  End If
  If Me.connInitialCatalog = "" Or IsNull(Me.connInitialCatalog) Then
    MsgBox Me.connInitialCatalog.Controls(0).Caption & " wajib diisi."
    Me.connDatabaseConnectionTest = 0
    Me.connInitialCatalog.SetFocus
    Exit Sub
  End If
  testDatabaseConnectionString True
  cmdSave_Click
End Sub

Private Sub cmdTestServerConnection_Click()
  If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then
    MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."
    Me.connServerConnectionTest = 0
    Me.connAppProvider.SetFocus
  Else
    testServerConnectionString True
  End If
  cmdSave_Click
End Sub

Private Sub Form_Open(Cancel As Integer)
  Dim rs As DAO.Recordset
  Dim ctl As Control
  
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblKoneksi", dbOpenSnapshot)
  For Each ctl In Me.Detail.Controls
    Do While Not rs.EOF
      If Controls(ctl.Name).Name = rs.Fields("prefsName").Value Then
        Controls(ctl.Name).Value = DLookup("prefsValue", "tblKoneksi", "prefsName='" & ctl.Name & "'")
          Controls(ctl.Name).ControlTipText = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'") & _
                                              vbNewLine & DLookup("prefsDescription", "tblKoneksi", "prefsName='" & ctl.Name & "'")
          Controls(ctl.Name).StatusBarText = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'") & _
                                              ": " & DLookup("prefsDescription", "tblKoneksi", "prefsName='" & ctl.Name & "'")
          Controls(ctl.Name).Controls(0).Caption = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'")
      End If
      rs.MoveNext
    Loop
  rs.MoveFirst
  Next ctl
  Set rs = Nothing
End Sub
Function updatePrefs(strPrefName As String, varPrefValue As String) 'OK
  Dim strsql As String
  Dim dbs As DAO.Database

On Error GoTo Err_Msg
  strsql = "UPDATE (SELECT * FROM tblKoneksi) tblKoneksi SET prefsValue = '" & Nz(varPrefValue, "") & _
            "' WHERE prefsName='" & strPrefName & "'"
  Set dbs = CurrentDb()
  dbs.Execute strsql
  dbs.Close
  Set dbs = Nothing
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function updatePrefs, Error # " & CStr(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.Description
  Resume Exit_Function
End Function

Bila Form View dari frmKoneksi dibuka, tampilannya seperti di bawah ini. 


 Selanjutnya, untuk menguji koneksi, kita masukkan nilai pada baris berikut ini:

  1. Pada baris Application Provider:
    1. bila databasenya adalah SQL Server Express, isikan SQLNCLI11
    2. bila databasenya adalah SQL Server Standard Edition, isikan SQLOLEDB
  2. Pada baris Data Source, isikan nama komputer atau alamat IP, bisa juga disertakan nama model aplikasi server database. Misalnya:
    1. Nama komputer adalah SERVER01, maka data source: SERVER01
    2. Nama komputer adalah SERVER01 dan aplikasi SQL Server edisi Express, maka data source: SERVER01\SQLExpress.
    3. Alamat IP 192.168.1.1. maka data source: 192.168.1.1
  3. Pada baris Network Library, isikan DBMSSOCN
  4. Pada baris Integrated Security, Trusted Connection Status, dan User Id, kita harus memilih salah satu:
    1. Bila menggunakan Integrated Security, isikan SSPI.
    2. Bila menggunakan Trusted Connection Status, pilih Yes.
    3. Bila menggunakan SQL Server Authentication, pada user ID, isikan nama login SQL Server yang sudah terotentikasi dan password yang menyertainya di baris Password.
  5. Untuk menguji koneksi dari client ke server, tekan tombol Test Server Connection. Bila berhasil, Result Test Server Connection bernilai True, ditunjukkan dengan tanda centang. Selain itu, ada pesan “Selamat, koneksi dari MS Access ke Server bisa dilakukan”, seperti pada gambar di atas. Tekan OK.
  6. Selanjutnya, bila sudah berhasil terhubung dengan server, kita melakukan koneksi dengan database yang ada dalam SQL Server. Pada baris Database Name, isikan nama database yang ada dalam Node Database di SQL Server. Lalu, tekan tombol 2. Membuat Database untuk membuat database. Pada contoh ini, kita menggunakan AdventureWorks2014 sebagai databasenya. Bila belum ada, saat database berhasil dibuat, muncul pesan seperti ini:

 


Tetapi, bila database sudah ada di SQL Server, pesan yang ditampilkan akan seperti ini:


Selesai membuat database, kita mencoba tes koneksi ke database. Caranya, tekan tombol 3. Test Database Connection. Bila koneksi ke database berhasil dilakukan, Access menampilkan pesan seperti ini:


Bila pesan seperti gambar berhasil ditampilkan, maka kita bisa melanjutkan proses untuk menyusun program atau aplikasi Access, seperti membuat query, memasukkan dan menampilkan data menggunakan form, menampilkan data di report, dan menuliskan berbagai macam modul yang berisi fungsi atau subrutin yang dibutuhkan

Comments

  1. Rasel21 March, 2021 12:53

    Mas bagaimana membuat function save universal untuk semua form, sehingga kita memanggil nama form dan nama tabel aja di fungsi itu, untuk text box dan nama field ditabel otomatis terdeteksi dari function yg kita buat

    ReplyDelete
    Replies
    1. Yanto Subroto24 March, 2021 22:20

      Secara ringkas, bisa dijelaskan sebagai berikut:

      Nama control di form, yang terkait dengan tabel tertentu, harus sama dengan nama field dari tabel itu.

      Buat loop untuk membaca nama control (ctl) di form, yang terkait dengan tabel tertentu (tbl).

      Buat loop untuk membaca nama field (fld) dari tabel tbl.
      Jadi ada dua loop, urutannya terserah, bisa ctl lebih dulu atau bisa fld lebih dulu.

      Selanjutnya, jika ctl.name sama dengan fld.name, maka periksa, apakah fld.value<>ctl.value. Jika tidak sama, simpan datanya. Jika sama, lewatkan ke proses looping berikutnya.

      Dengan cara ini, kita hanya perlu menyimpan (update) field (fld) yang nilainya berbeda dengan control (ctl).

      Saya biasanya memang membuat fungsi seperti yang kamu bilang itu, fungsi universal, yang bisa diaplikasikan ke berbagai macam form dan berbagai macam table. Praktis dan nggak capek.

      Syaratnya: tipe data field harus salah satu di antara ketiga ini: text (char), numeric, atau date. Kalau binary data nggak bisa.

      Delete

Post a Comment

Posting Terpopuler

Membuat Fungsi Untuk Menghitung Pajak Penghasilan PPh 21 di MS Access

1 comment
Read more

Normalisasi, Denormalisasi, dan Anomali Database

1 comment
Read more

Membuat Form dan Subform Menggunakan Form Wizard

11 comments
Read more