Sunday, October 11, 2009
Wednesday, October 7, 2009
Access ADODB Bind Query Example
Reference: http://www.accessclub.jp/ado/createparameter.html
Option Compare Database
' current command, global object for status dumpimg
Dim g_cmd As New ADODB.Command
' dump record set of current command
Sub subDumpResult()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim fldLoop As ADODB.Field
rs.Open g_cmd
Do Until rs.EOF
Dim str As String
For Each fldLoop In rs.Fields
str = str & fldLoop.Value & Chr(9)
Next fldLoop
Debug.Print str
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
' init query, create command, setup sql and parameters
Function fncInitQuery() As String
Set g_cmd = New ADODB.Command
With g_cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "Select top 10 * from Dict where JP like ?"
.CommandType = adCmdText
.Prepared = True
.Parameters.Append .CreateParameter("JP", adVarChar, adParamInput, 10)
End With
End Function
' bind parameter and execute the query
Function fncDoQuery(sSource As String) As String
Dim rs As ADODB.Recordset
g_cmd.Parameters("JP") = sSource
Set rs = g_cmd.Execute
subDumpResult
Do Until rs.EOF
sTranslated = rs!En
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
fncDoQuery = sTranslated
End Function
' finish query, and release command
Function fncFinishQuery()
Set g_cmd = Nothing
End Function
' show the entire bind - query process
Sub subDebugQueryStatement()
Dim sSource As String
fncInitQuery
sSource = "506"
Debug.Print "Query for " & sSource & " Trnaslated to " & fncDoQuery(sSource)
sSource = "518"
Debug.Print "Query for " & sSource & " Trnaslated to " & fncDoQuery(sSource)
fncFinishQuery
End Sub
Option Compare Database
' current command, global object for status dumpimg
Dim g_cmd As New ADODB.Command
' dump record set of current command
Sub subDumpResult()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim fldLoop As ADODB.Field
rs.Open g_cmd
Do Until rs.EOF
Dim str As String
For Each fldLoop In rs.Fields
str = str & fldLoop.Value & Chr(9)
Next fldLoop
Debug.Print str
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
' init query, create command, setup sql and parameters
Function fncInitQuery() As String
Set g_cmd = New ADODB.Command
With g_cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "Select top 10 * from Dict where JP like ?"
.CommandType = adCmdText
.Prepared = True
.Parameters.Append .CreateParameter("JP", adVarChar, adParamInput, 10)
End With
End Function
' bind parameter and execute the query
Function fncDoQuery(sSource As String) As String
Dim rs As ADODB.Recordset
g_cmd.Parameters("JP") = sSource
Set rs = g_cmd.Execute
subDumpResult
Do Until rs.EOF
sTranslated = rs!En
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
fncDoQuery = sTranslated
End Function
' finish query, and release command
Function fncFinishQuery()
Set g_cmd = Nothing
End Function
' show the entire bind - query process
Sub subDebugQueryStatement()
Dim sSource As String
fncInitQuery
sSource = "506"
Debug.Print "Query for " & sSource & " Trnaslated to " & fncDoQuery(sSource)
sSource = "518"
Debug.Print "Query for " & sSource & " Trnaslated to " & fncDoQuery(sSource)
fncFinishQuery
End Sub
Subscribe to:
Posts (Atom)