|Icrosoft Access: A0D ProgrammIng Code Examples
ProvIded by Allen 8rowne, |arch 2007. Updated |ay 2009.
A0D ProgrammIng Code Examples
ThIs page Is a reference for developers, demonstratIng how to use the A0D lIbrary to lIst and manIpulate the objects In Access.
A0D (ActIveX 0ata Dbjects) Is more generIc than 0AD (the one desIgned to handle the objects In Access), so supports features of
databases other than Access. n the wIder world beyond Access, A0D has largely been replaced by the quIte dIfferent [Link]
lIbrary.
n general, 0AD Is preferred over A0D, but there are some operatIons that work under A0D only. n general, these work In code
only. They wIll not work If you try them In the Query wIndow, sInce Access Itself uses 0AD. They also requIre JET 4 (Access 2000 or
later.)
A0D provIdes only lImIted ways to manIpulate the data structure (typIcally vIa 00L query statements), unless you also use the A0DX
lIbrary whIch provIdes the extensIons to get to the database catalog.
To use the A0D LIbrary, choose Feferences on the Tools menu In the code wIndow, and check the box besIde:
Microsoft ActiveX Data Objects 2.x Library
There Is no explanatIon of the code beyond InlIne comments, and no error handlIng In most examples.
Index of FunctIons 0escrIptIon
ShowSchema() LIst the tables
AdoFecordsetExample() Dpen a recordset
Create7IewAdo() Create a new query
|odIfy7IewAdo() |odIfy a query
Show8and() llustrate the 8AN0 operator wIth lIterals. (A0D only.)
Test8not() llustrate 8NDT (bInary NDT) operator (A0D only.)
Test8and() llustrate 8AN0 (bInary AN0) operator. (A0D only.)
ShowUserFoster|ultIpleUsers() LIst the users currently connected to the database.
UserCount() Count the number of dIstInct users connected to the database.
ExecuteA0D() Execute an actIon query wIth A0D, and know how many records were Inserted/deleted/changed.
Option Compare Database
Option Explicit
Function ShowSchema()
'Purpose: List the tables, using ADO.
Dim cn As [Link]
Dim rs As [Link]
Dim i As Integer
Set cn = [Link]
Set rs = [Link](adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
' For i = 0 To [Link] - 1
' [Link] [Link](i).Name
' Next
Do While Not [Link]
[Link] [Link]("TABLE_NAME").Value
[Link]
Loop
[Link]
Set rs = Nothing
Set cn = Nothing
End Function
Function AdoRecordsetExample()
'Purpose: Open a recordset using ADO.
Dim rs As New [Link]
Dim strSql As String
strSql = "SELECT MyField FROM MyTable;"
[Link] strSql, [Link]
Do While Not [Link]
[Link] rs!MyField
[Link]
Loop
[Link]
Set rs = Nothing
End Function
Function CreateViewAdo()
'Purpose: Create a new query using ADO.
Dim cn As [Link]
Dim strSql As String
strSql = "CREATE VIEW MyTableView AS SELECT MyTable.* FROM MyTable;"
Set cn = [Link]
[Link] strSql
[Link] "MyTableView created"
Set cn = Nothing
End Function
Function ModifyViewAdo()
'Purpose: Modify a query using ADO.
Dim cn As [Link]
Dim strSql As String
strSql = "ALTER TABLE Query1 AS SELECT MyTable.* FROM MyTable;"
Set cn = [Link]
[Link] strSql
[Link] "MyTableView modified"
Set cn = Nothing
End Function
Function ShowBand()
Dim rs As New [Link]
[Link] "SELECT (2 BAND 4) AS Result;", [Link]
ShowBand = rs!Result
[Link]
Set rs = Nothing
End Function
Function TestBnot()
'Purpose: Illustrate BNOT (binary NOT) operator (ADO only.)
Dim cn As [Link]
Dim strSql As String
Dim lngKt As Long
Set cn = [Link]
strSql = "UPDATE MyTable SET MyIntFlip = BNOT MyInt WHERE MyIntFlip Is Not Null;"
[Link] strSql, lngKt
Set cn = Nothing
TestBnot = lngKt
End Function
Function TestBand()
'Purpose: Illustrate BAND (binary AND) operator. (ADO only.)
Dim rs As New [Link]
Dim strSql As String
strSql = "SELECT MyBitField, (MyBitField BAND 2) <> 0 As MyResult FROM MyTable;"
[Link] strSql, [Link]
Do While Not [Link]
[Link] rs!MyBitfield, rs!MyResult
[Link]
Loop
[Link]
Set rs = Nothing
End Function
Function ShowUserRosterMultipleUsers()
'Source: kb 198755.
Dim cn As New [Link]
'Dim cn2 As New [Link]
Dim rs As New [Link]
Dim i, j As Long
[Link] = "[Link].4.0"
[Link] "Data Source=C:\Data\[Link]"
'[Link] "Provider=[Link].4.0;" & "Data Source=C:\Data\[Link]"
' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
Set rs = [Link](adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
'Output the list of all users in the current database.
[Link] [Link](0).Name, "", [Link](1).Name, "", [Link](2).Name, [Link](3).Name
While Not [Link]
[Link] [Link](0), [Link](1), [Link](2), [Link](3)
[Link]
Wend
End Function
Function UserCount() As Long
Dim cnLocal As [Link] 'Current project connection.
Dim cnBackEnd As New [Link] 'Connection to back end database.
Dim rsBEUserRoster As New [Link] 'JET User Roster for back end database.
Dim rsTarget As New [Link] 'Temp table to record users and de-dupe.
Dim strPath As String 'Full path to back end.
Dim strSql As String 'SQL string.
Dim lngKt As Long 'Loop controller.
Dim dtEnteredOn As Date 'Current date and time.
'Set this to the full path of your back end database.
strPath = "C:\Data\[Link]"
'Open the JET User Roster for the back end.
[Link] = "[Link].4.0"
[Link] "Data Source=" & strPath
Set rsBEUserRoster = [Link](adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")
'Clear temp table, and copy the user roster in.
dtEnteredOn = Now()
Set cnLocal = [Link]
[Link] "DELETE FROM tzJetUserRoster;"
[Link] "tzJetUserRoster", cnLocal, adOpenDynamic, adLockOptimistic
Do While Not [Link]
[Link]
For lngKt = 0 To 3
rsTarget(lngKt) = rsBEUserRoster(lngKt)
rsTarget!EnteredOn = dtEnteredOn
Next
[Link]
[Link]
Loop
[Link]
[Link]
[Link]
'Get the count of the number of distinct users who are connected.
strSql = "SELECT DISTINCT Computer_Name FROM tzJetUserRoster WHERE Connected = True;"
Set rsTarget = New [Link]
[Link] strSql, cnLocal, adOpenKeyset
If Not ([Link] And [Link]) Then
[Link]
UserCount = [Link]
End If
[Link]
'Dereference objects
Set rsTarget = Nothing
Set rsBEUserRoster = Nothing
Set cnLocal = Nothing
Set cnBackEnd = Nothing
End Function
Function ExecuteADO() As Long
'Purpose: How to execute an action query with ADO.
'Return: Number of records affected by action query.
Dim strSql As String
Dim lngKt As Long
strSql = "INSERT INTO tblClient (Surname, FirstName ) " & _
"SELECT 'Smith' AS Surname, 'Jim' AS FirstName;"
[Link] strSql, lngKt
ExecuteADO = lngKt
End Function
Home ndex of tIps Top