Kamis, 24 Mei 2012

[belajar-access] SQL Server 2005 Express [7/11]

 

SQL SERVER EXPRESS DAN ADO

Selain menggunakan link table, database SQL Server Express dapat pula diakses menggunakan ADO. Pada bagian ini, saya hanya akan menyampaikan contoh-contoh penggunaan ADO dan SQL Server Express. Seluruh Data Source yang saya gunakan di bawah ini harus disesuaikan lagi dengan nama SQL Server Express pada PC nya. Adapun nama di PC saya adalah 'MIS\SQLEXPRESS'.

CONTOH 1: MENAMPILKAN SELURUH RECORD PADA SUATU TABLE MENGGUNAKAN ADO

1. Buatlah sebuah module baru.

2. Buatlah procedure sbb:

Public Function Contoh1()
'MENAMPILKAN SELURUH RECORD PADA SUATU TABLE MENGGUNAKAN ADO
Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim strCn As String
Set Cn = New ADODB.Connection
strCn = "Provider='SQLOLEDB';Data Source='MIS\SQLEXPRESS';" & _
"Initial Catalog='Northwind';Integrated Security='SSPI';"
Cn.Open strCn
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "SELECT * FROM Customers;"
Cmd.CommandType = adCmdText
Set rs = Cmd.Execute
Debug.Print rs.Fields(0).Name & " | " & rs.Fields(1).Name & " | " & rs.Fields(2).Name & " | " & rs.Fields(3).Name & " | " & rs.Fields(4).Name & " | " & rs.Fields(5).Name & " | " & rs.Fields(6).Name & " | " & rs.Fields(7).Name & " | " & rs.Fields(8).Name & " | " & rs.Fields(9).Name & " | " & rs.Fields(10).Name
While Not rs.EOF
Debug.Print rs(0) & " | " & rs(1) & " | " & rs(2) & " | " & rs(3) & " | " & rs(4) & " | " & rs(5) & " | " & rs(6) & " | " & rs(7) & " | " & rs(8) & " | " & rs(9) & " | " & rs(10)
rs.MoveNext
Wend
Cn.Close
End Function

3. Pada immediate window VBA, ketikkan:
?contoh1()
Lalu tekan Enter. Perhatikanlah hasil yang muncul pada immediate window VBA.

CONTOH 2: MEMBUAT TABLE BARU MENGGUNAKAN ADO

1. Buatlah sebuah module baru.

2. Buatlah procedure sbb:

Public Function Contoh2()
'CONTOH 2: MEMBUAT TABLE BARU MENGGUNAKAN ADO
On Error GoTo AdoError
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim strCn As String
Set Cn = New ADODB.Connection
strCn = "Provider='SQLOLEDB';Data Source='MIS\SQLEXPRESS';" & _
"Initial Catalog='Northwind';Integrated Security='SSPI';"
Cn.Open strCn
Set Cmd.ActiveConnection = Cn
'Jika disana sudah ada table "TestTableBaru" maka dihapus dahulu
Cmd.CommandText = "drop table TestTableBaru"
Cmd.CommandType = adCmdText
Cmd.Execute
Proses:
Cmd.CommandText = "set nocount on"
Cmd.Execute
Cmd.CommandText = "create table TestTableBaru (id int, nama char(100))"
Cmd.Execute
Cmd.CommandText = "insert into TestTableBaru values(1, 'Sofyan Efendi')"
Cmd.Execute
Cn.Close
Exit Function
AdoError:
GoTo Proses
End Function

3. Pada immediate window VBA, ketikkan:
?contoh2()
Lalu tekan Enter. Maka pada database Northwind muncul table baru dengan nama TestTableBaru.

CONTOH 3: MENJALANKAN QUERY BERPARAMETER MENGGUNAKAN ADO

1. Buatlah sebuah module baru.

2. Buatlah procedure sbb:

Public Function Contoh3()
'CONTOH 3: MENJALANKAN QUERY BERPARAMETER MENGGUNAKAN ADO
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Dim strCn As String
Set Cn = New ADODB.Connection
strCn = "Provider='SQLOLEDB';Data Source='MIS\SQLEXPRESS';" & _
"Initial Catalog='Northwind';Integrated Security='SSPI';"
Cn.Open strCn
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "update Region set RegionDescription=? where RegionID=?"
Cmd.CommandType = adCmdText
Cmd.Prepared = True
Set prm1 = Cmd.CreateParameter("RegionDescription", adChar, adParamInput, 50)
Cmd.Parameters.Append prm1
Set prm2 = Cmd.CreateParameter("RegionID", adInteger, adParamInput, 4)
Cmd.Parameters.Append prm2
'Mengupdate dari "Eastern" menjadi "INDONESIA"
Cmd("RegionDescription") = "INDONESIA"
Cmd("RegionID") = 1
Cmd.Execute
Cn.Close
End Function

3. Pada immediate window VBA, ketikkan:
?contoh3()
Lalu tekan Enter. Maka data pada table Region sekarang sudah berubah.

CONTOH 4: MENJALANKAN STORED PROCEDURE BERPARAMETER MENGGUNAKAN ADO

1. Buatlah sebuah module baru.

2. Buatlah procedure sbb:

Public Function Contoh4()
'CONTOH 4: MENJALANKAN STORED PROCEDURE BERPARAMETER MENGGUNAKAN ADO
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
objCmd.CommandText = "CustOrdersOrders"
'Ket: Procedure "CustOrdersOrders" diatas isinya adalah:
'set ANSI_NULLS ON
'set QUOTED_IDENTIFIER ON
'GO
'ALTER PROCEDURE [dbo].[CustOrdersOrders] @CustomerID nchar(5)
'AS
'SELECT OrderID, OrderDate, RequiredDate, ShippedDate
'FROM Orders
'WHERE CustomerID = @CustomerID
'ORDER BY OrderID
objCmd.CommandType = adCmdStoredProc
Dim strCn As String
Set objConn = New ADODB.Connection
strCn = "Provider='SQLOLEDB';Data Source='MIS\SQLEXPRESS';" & _
"Initial Catalog='Northwind';Integrated Security='SSPI';"
objConn.Open strCn
Set objCmd.ActiveConnection = objConn
objCmd.Parameters.Refresh
objCmd(1) = "ALFKI"
Set objRs = objCmd.Execute
Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop
objCmd(1) = "CACTU"
Set objRs = objCmd.Execute
Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing
Exit Function
ErrHandler:
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Function

3. Pada immediate window VBA, ketikkan:
?contoh4()
Lalu tekan Enter. Perhatikanlah hasil yang muncul pada immediate window VBA.

 
Wassalaamu'alaikum Warahmatullahi Wabarakatuh,
Sofyan Efendi
http://imopi.wordpress.com/

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

Tidak ada komentar:

Posting Komentar