Membuat Perangkat untuk Menguji Koneksi dari Access ke SQL Server

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