Silahkan dicopy paste kode di bawah ini ke module baru.
Sesuaikan strConnect dengan kondisi tempat anda.
Ganti strSQL sesuai dengan store procedure yang hendak di execute, lengkap
dengan parameternya.
strSQL = "EXEC DBO.[TEN MOST EXPENSIVE PRODUCTS]"
'strSQL = "SELECT * FROM CUSTOMERS"
'strSQL="EXEC DBO.[SALES BY YEAR] '1-1-2000','1-1-2011'"
Salam, MBA
'-------start module------------------
Option Compare Database
Option Explicit
Private Sub TEST_EXECUTE_SP()
Dim strConnect As String, strQuery As String, strSQL As String
strConnect = "ODBC;Driver={SQL
Server};Server=MACHINEXXX;Database=NORTHWIND;Uid=sa;Pwd=12121212"
strQuery = "tempsp"
strSQL = "EXEC DBO.[TEN MOST EXPENSIVE PRODUCTS]"
'strSQL = "SELECT * FROM CUSTOMERS"
'strSQL="EXEC DBO.[SALES BY YEAR] '1-1-2000','1-1-2011'"
fExecuteSP strConnect, strQuery, strSQL
DoCmd.OpenQuery strQuery
End Sub
Function fExecuteSP(ByVal strConnect As String, ByVal strQuery As String,
ByVal strSQL As String)
On Error GoTo Err_fExecuteSP
With CurrentDb
.CreateQueryDef strQuery, strSQL
.QueryDefs(strQuery).Connect = strConnect
.QueryDefs(strQuery).SQL = strSQL
End With
Exit_fExecuteSP:
Exit Function
Err_fExecuteSP:
Select Case Err.Number
Case 7874, 3129 'BELUM ADA
CurrentDb.CreateQueryDef strQuery, "SELECT * FROM TEMP"
Resume Next
Case 3012 'SDH ADA
Resume Next
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_fExecuteSP
End Select
End Function
_____
From: [email protected] [mailto:[email protected]]
On Behalf Of Izar Daffa
Sent: Tuesday, March 02, 2010 13:01
To: [email protected]
Subject: [belajar-access] Store Prosedur
Mohon maaf sebelumnya jika topik ini pernah dibahas.
Rekan-rekan master access, mohon bantuannya bagaimana menjalankan store
prosedur pada SQL Server dari Ms. Access, kebetulan dikantor saya ada
Aplikasi yang belum optimal, saya bermaksud untuk membackupnya biar bisa
jalan dengan memanfaatkan store prosedur yang sudah ada. saya mengunakan
Access 2003, terima kasih sebelumnya.
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2717 - Release Date: 03/02/10
02:34:00
