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