VBA Access ADO Sample

I've finally moved to using parametrized queries... this is a good reference with an actual example:

Active Server Pages Tutorial by MSFT

http://msdn.microsoft.com/en-us/library/ms972337(printer).aspx

Here's the key snippet for me:

'Connects to the Access driver and Access database in the Inetpub
'directory where the database is saved
strProvider = "Driver={Microsoft Access Driver (*.mdb)};
   DBQ=C:\Inetpub\Wwwroot\Tutorial\guestbook.mdb;"
'Creates an instance of an Active Server component
set objConn = server.createobject("ADODB.Connection")
'Opens the connection to the data store
objConn.Open strProvider
'Instantiate Command object and use ActiveConnection property to
'attach connection to Command object
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn
'Define SQL query
cm.CommandText ="INSERT INTO Guestbook (TB1,TB2,TB3,TB4,MB1)
   VALUES (?,?,?,?,?)"
'Define query parameter configuration information for guestbook fields
set objparam=cm.createparameter(, 200, , 255, strTB1)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB2)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB3)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB4)
cm.parameters.append objparam
set objparam=cm.createparameter(, 201, , iLenMB1, strMB1)
cm.parameters.append objparam
cm.execute
response.write("Thank you!")

------

My own example, largely based on the above - but better because it has a date example! This is from classic ASP into an MS Access MDB file.

' Dev data
    strSiteID = "134"
    intTarget = 560
    dateWorkDate = "4/23/2009"

' Define SQL query
    objCmd.CommandText ="INSERT INTO TargetSalesEmployee (SiteID, Target, WorkDate) VALUES (?,?,?)"

' Define query parameter configuration information for database insert

    ' CreateParameter()
    '   http://www.w3schools.com/ado/met_comm_createparameter.asp
    '   objCmd.CreateParameter (name, type, direction, size, value)

    ' Constants aren't defined by default --> 200 = adVarChar. 14 = Decimal. 13 = adDBDate

    SET objparam = objCmd.createparameter(, 200, , 3, strSiteID)
    objCmd.parameters.append objparam

    SET objparam = objCmd.createparameter(, 14, , , intTarget)
    objCmd.parameters.append objparam

    SET objparam = objCmd.createparameter(, 133, , , dateWorkDate)
    objCmd.parameters.append objparam

    objCmd.execute

------

Here is a simple example on how to create a recordset from a table in the current Microsoft Access database using VBA. (Unfortunately, chances are that the scribble web will eat some of the formatting and what not of this little Visual Basic snippet, so beware)

Function DoBulkGCRandoms() As Boolean
' chunks of code removed for simplicity...

Dim rs As Recordset
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient

strSQL = "SELECT GC FROM tblGCsToInsert"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
vValue = rs("Value")
rs.MoveNext
End If

DoBulkGCRandoms = True

End Function

tags: ado, adodb, asp, parameter, query