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:

view plainprint?
  1. Option Compare Database  
  2. 'Aktifkan Microsoft ActiveX Data Object (versi minimum) 2.8 Library pada menu Tools References...  
  3. Sub testDatabaseConnectionString(Optional blTestOnly As Boolean = False)   
  4.   Dim conn As ADODB.Connection  
  5.   Dim cs As String  
  6.   
  7. On Error GoTo Err_Msg  
  8.     
  9.   Set conn = New ADODB.Connection  
  10.     
  11.   cs = "Provider=" & Me.connAppProvider & ";"  
  12.   cs = cs & "Data Source=" & Me.connDataSource & ";"  
  13.   cs = cs & "Initial Catalog=" & Me.connInitialCatalog & ";"  
  14.   If Me.connNetworkLibrary <> "" Then  
  15.     cs = cs & "Network Library=" & Me.connNetworkLibrary & ";"  
  16.   End If  
  17.   If (Me.connUserID <> "" And Me.connIntegratedSecurity <> "") _  
  18.     Or (Me.connUserID <> "" And Me.connTrustedConnection <> "No") _  
  19.     Or (Me.connIntegratedSecurity <> "" And Me.connTrustedConnection <> "No") _  
  20.     Then  
  21.       MsgBox "Pilihlah salah satu di antara isian berikut ini:" & vbNewLine & _  
  22.              "1. Integrated Security, ATAU" & vbNewLine & _  
  23.              "2. Trusted Connection, ATAU" & vbNewLine & _  
  24.              "3. User ID.", vbCritical + vbInformation  
  25.       Cancel = True  
  26.       Exit Sub  
  27.   End If  
  28.   If Me.connUserID <> "" Then  
  29.     cs = cs & "UID=" & Me.connUserID & ";"  
  30.     If Me.connPassword <> "" Then  
  31.       cs = cs & "PWD=" & Nz(Me.connPassword, "") & ";"  
  32.     End If  
  33.   End If  
  34.   If Me.connIntegratedSecurity <> "" Then  
  35.     cs = cs & "Integrated Security=" & Me.connIntegratedSecurity & ";"  
  36.   End If  
  37.   If Me.connTrustedConnection <> "No" Then  
  38.     cs = cs & "Trusted_Connection=" & Me.connTrustedConnection & ";"  
  39.   End If  
  40.   conn.Open cs, "", ""  
  41.   If blTestOnly And conn.State = adStateOpen Then  
  42.     MsgBox "Selamat, koneksi dari MS Access ke database SQL Server berhasil dilakukan. Tekan Next untuk proses berikutnya", vbInformation  
  43.     Me.connDatabaseConnectionTest = -1  
  44.     GoTo Exit_Sub  
  45.   Else  
  46.       
  47.     MsgBox "Data koneksi sudah terpasang.", vbOKOnly + vbInformation  
  48.     boolAplikasiSudahTerpasang = True  
  49.   
  50.   End If  
  51. Exit_Sub:  
  52.   conn.Close  
  53.   Set conn = Nothing  
  54.   Exit Sub  
  55. Err_Msg:  
  56.   If Err.Number = -2147217887 Or Err.Number = 3706 _  
  57.   Or Err.Number = -2147467259 Or Err.Number = -2147217843 Then  
  58.     MsgBox "Koneksi tidak tersambung. Mohon dicek kembali connection string dari MS Access ke SQL Server.", vbCritical + vbExclamation  
  59.     Me.connDatabaseConnectionTest = 0  
  60.     cmdSave_Click  
  61.     End  
  62.     DoCmd.Close acForm, CurrentObjectName  
  63.       
  64.   Else  
  65.     MsgBox "Connection Error # " & CStr(Err.Number) & ", source: " & Err.Source & _  
  66.     Chr(13) & Err.Description  
  67.     End  
  68.   End If  
  69.   Resume Exit_Sub  
  70. End Sub  
  71. Private Sub testServerConnectionString(Optional blTestOnly As Boolean = False)  
  72. Dim conn As ADODB.Connection  
  73. Dim cs As String  
  74. Dim sqlcmd As String  
  75. Dim intCount As Integer  
  76.   
  77. On Error GoTo Err_Msg  
  78.       
  79.   Set conn = New ADODB.Connection  
  80.   cs = "Provider=" & Me.connAppProvider & ";"  
  81.   cs = cs & "Data Source=" & Me.connDataSource & ";"  
  82.   If Me.connNetworkLibrary <> "" Then  
  83.     cs = cs & "Network Library=" & Me.connNetworkLibrary & ";"  
  84.   End If  
  85.   If (Me.connUserID <> "" And Me.connIntegratedSecurity <> "") _  
  86.     Or (Me.connUserID <> "" And Me.connTrustedConnection <> "No") _  
  87.     Or (Me.connIntegratedSecurity <> "" And Me.connTrustedConnection <> "No") _  
  88.     Then  
  89.       MsgBox "Pilihlah salah satu di antara isian berikut ini:" & vbNewLine & _  
  90.              "1. Integrated Security, ATAU" & vbNewLine & _  
  91.              "2. Trusted Connection, ATAU" & vbNewLine & _  
  92.              "3. User ID.", vbCritical + vbInformation  
  93.       Cancel = True  
  94.       Exit Sub  
  95.   End If  
  96.   If Me.connUserID <> "" Then  
  97.     cs = cs & "UID=" & Me.connUserID & ";"  
  98.     If Me.connPassword <> "" Then  
  99.       cs = cs & "PWD=" & Nz(Me.connPassword, "") & ";"  
  100.     End If  
  101.   End If  
  102.   If Me.connIntegratedSecurity <> "" Then  
  103.     cs = cs & "Integrated Security=" & Me.connIntegratedSecurity & ";"  
  104.   End If  
  105.   If Me.connTrustedConnection <> "No" Then  
  106.     cs = cs & "Trusted_Connection=" & Me.connTrustedConnection  
  107.   End If  
  108.   conn.Open cs, "", ""  
  109.   Debug.Print cs  
  110.   If blTestOnly And conn.State = adStateOpen Then  
  111.     MsgBox "Selamat, koneksi dari MS Access ke Server bisa dilakukan", vbInformation  
  112.     Me.connServerConnectionTest = -1  
  113.     Me.cmdCreateDatabase.Enabled = True  
  114.   Else  
  115.     intCount = conn.Execute("SELECT COUNT(*) FROM sys.databases WHERE [Name] = '" & Me.connInitialCatalog & "'").Fields(0).Value  
  116.     If intCount <> 0 Then  
  117.       MsgBox "Maaf, database " & Me.connInitialCatalog & " Sudah dibuat sebelumnya. Silakan lanjut ke proses berikut: Test Database Connection"  
  118.       Me.cmdTestDatabaseConnection.Enabled = True  
  119.     Else  
  120.       sqlcmd = "CREATE DATABASE " & Me.connInitialCatalog & ";"  
  121.       conn.Execute sqlcmd  
  122.       MsgBox "Database " & Me.connInitialCatalog & " Sudah dibuat. Silakan lanjut ke proses berikut: Test Database Connection"  
  123.       Me.cmdTestDatabaseConnection.Enabled = True  
  124.     End If  
  125.   End If  
  126.     
  127.   conn.Close  
  128.   Set conn = Nothing  
  129. Exit_Sub:  
  130.   Exit Sub  
  131. Err_Msg:  
  132.   If Err.Number = -2147217887 Or Err.Number = 3706 _  
  133.   Or Err.Number = -2147467259 Or Err.Number = -2147217843 Then  
  134.     MsgBox "Koneksi tidak tersambung. Mohon dicek kembali connection string dari MS Access ke SQL Server.", vbCritical + vbExclamation  
  135.     Me.connServerConnectionTest = 0  
  136.     cmdSave_Click  
  137.   Else  
  138.     MsgBox "Connection Error # " & CStr(Err.Number) & ", source: " & Err.Source & _  
  139.     Chr(13) & Err.Description  
  140.   End If  
  141.   Resume Exit_Sub  
  142. End Sub  
  143. Sub cmdSave_Click()   
  144.   Dim rs As DAO.Recordset  
  145.   Dim ctl As Control  
  146.       
  147.   Me.AllowAdditions = False  
  148.   Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblKoneksi", dbOpenDynaset)  
  149.   For Each ctl In Me.Detail.Controls  
  150.     Do While Not rs.EOF  
  151.       If Controls(ctl.Name).Name = rs.Fields("prefsName").Value Then updatePrefs ctl.Name, Nz(Controls(ctl.Name).Value, "")  
  152.       rs.MoveNext  
  153.     Loop  
  154.     rs.MoveFirst  
  155.   Next ctl  
  156.   Set rs = Nothing  
  157. End Sub  
  158.   
  159. Private Sub cmdCreateDatabase_Click()  
  160.   If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then  
  161.     MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."  
  162.     Me.connServerConnectionTest = 0  
  163.     Me.connAppProvider.SetFocus  
  164.     Exit Sub  
  165.   End If  
  166.     
  167.   If Me.connInitialCatalog = "" Or IsNull(Me.connInitialCatalog) Then  
  168.     MsgBox Me.connInitialCatalog.Controls(0).Caption & " wajib diisi."  
  169.     Me.connInitialCatalog.SetFocus  
  170.   Else  
  171.     testServerConnectionString  
  172.   End If  
  173.   
  174. End Sub  
  175.   
  176. Private Sub cmdTestDatabaseConnection_Click()  
  177.   If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then  
  178.     MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."  
  179.     Me.connServerConnectionTest = 0  
  180.     Me.connDatabaseConnectionTest = 0  
  181.     Me.connAppProvider.SetFocus  
  182.     Exit Sub  
  183.   End If  
  184.   If Me.connInitialCatalog = "" Or IsNull(Me.connInitialCatalog) Then  
  185.     MsgBox Me.connInitialCatalog.Controls(0).Caption & " wajib diisi."  
  186.     Me.connDatabaseConnectionTest = 0  
  187.     Me.connInitialCatalog.SetFocus  
  188.     Exit Sub  
  189.   End If  
  190.   testDatabaseConnectionString True  
  191.   cmdSave_Click  
  192. End Sub  
  193.   
  194. Private Sub cmdTestServerConnection_Click()  
  195.   If Me.connAppProvider = "" Or IsNull(Me.connAppProvider) Then  
  196.     MsgBox Me.connAppProvider.Controls(0).Caption & " wajib diisi."  
  197.     Me.connServerConnectionTest = 0  
  198.     Me.connAppProvider.SetFocus  
  199.   Else  
  200.     testServerConnectionString True  
  201.   End If  
  202.   cmdSave_Click  
  203. End Sub  
  204.   
  205. Private Sub Form_Open(Cancel As Integer)  
  206.   Dim rs As DAO.Recordset  
  207.   Dim ctl As Control  
  208.     
  209.   Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblKoneksi", dbOpenSnapshot)  
  210.   For Each ctl In Me.Detail.Controls  
  211.     Do While Not rs.EOF  
  212.       If Controls(ctl.Name).Name = rs.Fields("prefsName").Value Then  
  213.         Controls(ctl.Name).Value = DLookup("prefsValue", "tblKoneksi", "prefsName='" & ctl.Name & "'")  
  214.           Controls(ctl.Name).ControlTipText = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'") & _  
  215.                                               vbNewLine & DLookup("prefsDescription", "tblKoneksi", "prefsName='" & ctl.Name & "'")  
  216.           Controls(ctl.Name).StatusBarText = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'") & _  
  217.                                               ": " & DLookup("prefsDescription", "tblKoneksi", "prefsName='" & ctl.Name & "'")  
  218.           Controls(ctl.Name).Controls(0).Caption = DLookup("prefsCaption", "tblKoneksi", "prefsName='" & ctl.Name & "'")  
  219.       End If  
  220.       rs.MoveNext  
  221.     Loop  
  222.   rs.MoveFirst  
  223.   Next ctl  
  224.   Set rs = Nothing  
  225. End Sub  
  226. Function updatePrefs(strPrefName As String, varPrefValue As String) 'OK  
  227.   Dim strsql As String  
  228.   Dim dbs As DAO.Database  
  229.   
  230. On Error GoTo Err_Msg  
  231.   strsql = "UPDATE (SELECT * FROM tblKoneksi) tblKoneksi SET prefsValue = '" & Nz(varPrefValue, "") & _  
  232.             "' WHERE prefsName='" & strPrefName & "'"  
  233.   Set dbs = CurrentDb()  
  234.   dbs.Execute strsql  
  235.   dbs.Close  
  236.   Set dbs = Nothing  
  237. Exit_Function:  
  238.   Exit Function  
  239. Err_Msg:  
  240.   MsgBox "Function updatePrefs, Error # " & CStr(Err.Number) & ", source: " & Err.Source & _  
  241.   Chr(13) & Err.Description  
  242.   Resume Exit_Function  
  243. 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

Labels

Comments

  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.

    ReplyDelete

Post a Comment

Posting Terpopuler

Cara Mengatur dan Menggunakan ODBC untuk Mengakses Data Eksternal

4 comments
Read more

Memahami Properti Validation Rule dan Validation Text pada Tabel di Access

1 comment
Read more

Format Untuk Field Dengan Tipe Data Number dan Currency di MS Access

1 comment
Read more