VBA Microsoft ActiveX Data Objects Library

The Microsoft ActiveX Data Objects library can be used to access various types of databases and execute queries using SQL. The ADODB.Stream class can be used to work with text streams.

Classes

Class Description
Command Defines a command to be executed against a database.
Connection Represents a connection to a database.
Error Represents an error generated by an operation on an ADO object.
Errors Collection containing Error objects. Errors are added to the Connection object's Errors collection property when the errors occur.
Parameter Represents a parameter of a parameterized query or stored procedure.
Parameters Collection containing all the Parameter objects of a Command object.
Properties Collection of Property objects for an ADO object.
Property Represents a property of an ADO object.
Record Represents a row of data. Can be a row from a Recordset.
Recordset Represents all records in a set of data. Can only point to one record at a time.
Stream Represents a stream of binary or text data.

Enums and Consts

ADCPROP_ASYNCTHREADPRIORITY_ENUM
  1. adPriorityAboveNormal = 4
  2. adPriorityBelowNormal = 2
  3. adPriorityHighest = 5
  4. adPriorityLowest = 1
  5. adPriorityNormal = 3
ADCPROP_AUTORECALC_ENUM
  1. adRecalcAlways = 1
  2. adRecalcUpFront = 0
ADCPROP_UPDATECRITERIA_ENUM
  1. adCriteriaAllCols = 1
  2. adCriteriaKey = 0
  3. adCriteriaTimeStamp = 3
  4. adCriteriaUpdCols = 2
ADCPROP_UPDATERESYNC_ENUM
  1. adResyncAll = 15
  2. adResyncAutoIncrement = 1
  3. adResyncConflicts = 2
  4. adResyncInserts = 8
  5. adResyncNone = 0
  6. adResyncUpdates = 4
AffectEnum
  1. adAffectAllChapters = 4
  2. adAffectCurrent = 1
  3. adAffectGroup = 2
BookmarkEnum
  1. adBookmarkCurrent = 0
  2. adBookmarkFirst = 1
  3. adBookmarkLast = 2
CommandTypeEnum
  1. adCmdFile = 256
  2. adCmdStoredProc = 4
  3. adCmdTable = 2
  4. adCmdTableDirect = 512
  5. adCmdText = 1
  6. adCmdUnknown = 8
CompareEnum
  1. adCompareEqual = 1
  2. adCompareGreaterThan = 2
  3. adCompareLessThan = 0
  4. adCompareNotComparable = 4
  5. adCompareNotEqual = 3
ConnectModeEnum
  1. adModeRead = 1
  2. adModeReadWrite = 3
  3. adModeRecursive = 4194304
  4. adModeShareDenyNone = 16
  5. adModeShareDenyRead = 4
  6. adModeShareDenyWrite = 8
  7. adModeShareExclusive = 12
  8. adModeUnknown = 0
  9. adModeWrite = 2
ConnectOptionEnum
  1. adAsyncConnect = 16
ConnectPromptEnum
  1. adPromptAlways = 1
  2. adPromptComplete = 2
  3. adPromptCompleteRequired = 3
  4. adPromptNever = 4
CopyRecordOptionsEnum
  1. adCopyAllowEmulation = 4
  2. adCopyNonRecursive = 2
  3. adCopyOverWrite = 1
  4. adCopyUnspecified = -1
CursorLocationEnum
  1. adUseClient = 3
  2. adUseServer = 2
CursorOptionEnum
  1. adAddNew = 16778240
  2. adApproxPosition = 16384
  3. adBookmark = 8192
  4. adDelete = 16779264
  5. adFind = 524288
  6. adHoldRecords = 256
  7. adIndex = 8388608
  8. adMovePrevious = 512
  9. adNotify = 262144
  10. adResync = 131072
  11. adSeek = 4194304
  12. adUpdate = 16809984
  13. adUpdateBatch = 65536
CursorTypeEnum
  1. adOpenDynamic = 2
  2. adOpenForwardOnly = 0
  3. adOpenKeyset = 1
  4. adOpenStatic = 3
DataTypeEnum
  1. adArray = 8192
  2. adBigInt = 20
  3. adBinary = 128
  4. adBoolean = 11
  5. adBSTR = 8
  6. adChapter = 136
  7. adChar = 129
  8. adCurrency = 6
  9. adDate = 7
  10. adDBDate = 133
  11. adDBTime = 134
  12. adDBTimeStamp = 135
  13. adDecimal = 14
  14. adDouble = 5
  15. adEmpty = 0
  16. adError = 10
  17. adFileTime = 64
  18. adGUID = 72
  19. adIDispatch = 9
  20. adInteger = 3
  21. adIUnknown = 13
  22. adLongVarBinary = 205
  23. adLongVarChar = 201
  24. adLongVarWChar = 203
  25. adNumeric = 131
  26. adPropVariant = 138
  27. adSingle = 4
  28. adSmallInt = 2
  29. adTinyInt = 16
  30. adUnsignedBigInt = 21
  31. adUnsignedInt = 19
  32. adUnsignedSmallInt = 18
  33. adUnsignedTinyInt = 17
  34. adUserDefined = 132
  35. adVarBinary = 204
  36. adVarChar = 200
  37. adVariant = 12
  38. adVarNumeric = 139
  39. adVarWChar = 202
  40. adWChar = 130
EditModeEnum
  1. adEditAdd = 2
  2. adEditDelete = 4
  3. adEditInProgress = 1
  4. adEditNone = 0
ErrorValueEnum
  1. adErrBoundToCommand = 3707
  2. adErrCannotComplete = 3732
  3. adErrCantChangeConnection = 3748
  4. adErrCantChangeProvider = 3220
  5. adErrCantConvertvalue = 3724
  6. adErrCantCreate = 3725
  7. adErrCatalogNotSet = 3747
  8. adErrColumnNotOnThisRow = 3726
  9. adErrConnectionStringTooLong = 3754
  10. adErrDataConversion = 3421
  11. adErrDataOverflow = 3721
  12. adErrDelResOutOfScope = 3738
  13. adErrDenyNotSupported = 3750
  14. adErrDenyTypeNotSupported = 3751
  15. adErrFeatureNotAvailable = 3251
  16. adErrFieldsUpdateFailed = 3749
  17. adErrIllegalOperation = 3219
  18. adErrIntegrityViolation = 3719
  19. adErrInTransaction = 3246
  20. adErrInvalidArgument = 3001
  21. adErrInvalidConnection = 3709
  22. adErrInvalidParamInfo = 3708
  23. adErrInvalidTransaction = 3714
  24. adErrInvalidURL = 3729
  25. adErrItemNotFound = 3265
  26. adErrNoCurrentRecord = 3021
  27. adErrNotReentrant = 3710
  28. adErrObjectClosed = 3704
  29. adErrObjectInCollection = 3367
  30. adErrObjectNotSet = 3420
  31. adErrObjectOpen = 3705
  32. adErrOpeningFile = 3002
  33. adErrOperationCancelled = 3712
  34. adErrOutOfSpace = 3734
  35. adErrPermissionDenied = 3720
  36. adErrPropConflicting = 3742
  37. adErrPropInvalidColumn = 3739
  38. adErrPropInvalidOption = 3740
  39. adErrPropInvalidValue = 3741
  40. adErrPropNotAllSettable = 3743
  41. adErrPropNotSet = 3744
  42. adErrPropNotSettable = 3745
  43. adErrPropNotSupported = 3746
  44. adErrProviderFailed = 3000
  45. adErrProviderNotFound = 3706
  46. adErrProviderNotSpecified = 3753
  47. adErrReadFile = 3003
  48. adErrResourceExists = 3731
  49. adErrResourceLocked = 3730
  50. adErrResourceOutOfScope = 3735
  51. adErrSchemaViolation = 3722
  52. adErrSignMismatch = 3723
  53. adErrStillConnecting = 3713
  54. adErrStillExecuting = 3711
  55. adErrTreePermissionDenied = 3728
  56. adErrUnavailable = 3736
  57. adErrUnsafeOperation = 3716
  58. adErrURLDoesNotExist = 3727
  59. adErrURLNamedRowDoesNotExist = 3737
  60. adErrVolumeNotFound = 3733
  61. adErrWriteFile = 3004
  62. adwrnSecurityDialog = 3717
  63. adwrnSecurityDialogHeader = 3718
EventReasonEnum
  1. adRsnAddNew = 1
  2. adRsnClose = 9
  3. adRsnDelete = 2
  4. adRsnFirstChange = 11
  5. adRsnMove = 10
  6. adRsnMoveFirst = 12
  7. adRsnMoveLast = 15
  8. adRsnMoveNext = 13
  9. adRsnMovePrevious = 14
  10. adRsnRequery = 7
  11. adRsnResynch = 8
  12. adRsnUndoAddNew = 5
  13. adRsnUndoDelete = 6
  14. adRsnUndoUpdate = 4
  15. adRsnUpdate = 3
EventStatusEnum
  1. adStatusCancel = 4
  2. adStatusCantDeny = 3
  3. adStatusErrorsOccurred = 2
  4. adStatusOK = 1
  5. adStatusUnwantedEvent = 5
ExecuteOptionEnum
  1. adAsyncExecute = 16
  2. adAsyncFetch = 32
  3. adAsyncFetchNonBlocking = 64
  4. adExecuteNoRecords = 128
  5. adExecuteStream = 1024
FieldAttributeEnum
  1. adFldCacheDeferred = 4096
  2. adFldFixed = 16
  3. adFldIsChapter = 8192
  4. adFldIsCollection = 262144
  5. adFldIsDefaultStream = 131072
  6. adFldIsNullable = 32
  7. adFldIsRowURL = 65536
  8. adFldKeyColumn = 32768
  9. adFldLong = 128
  10. adFldMayBeNull = 64
  11. adFldMayDefer = 2
  12. adFldNegativeScale = 16384
  13. adFldRowID = 256
  14. adFldRowVersion = 512
  15. adFldUnknownUpdatable = 8
  16. adFldUpdatable = 4
FieldEnum
  1. adDefaultStream = -1
  2. adRecordURL = -2
FieldStatusEnum
  1. adFieldAlreadyExists = 26
  2. adFieldBadStatus = 12
  3. adFieldCannotComplete = 20
  4. adFieldCannotDeleteSource = 23
  5. adFieldCantConvertValue = 2
  6. adFieldCantCreate = 7
  7. adFieldDataOverflow = 6
  8. adFieldDefault = 13
  9. adFieldDoesNotExist = 16
  10. adFieldIgnore = 15
  11. adFieldIntegrityViolation = 10
  12. adFieldInvalidURL = 17
  13. adFieldIsNull = 3
  14. adFieldOK = 0
  15. adFieldOutOfSpace = 22
  16. adFieldPendingChange = 262144
  17. adFieldPendingDelete = 131072
  18. adFieldPendingInsert = 65536
  19. adFieldPendingUnknown = 524288
  20. adFieldPendingUnknownDelete = 1048576
  21. adFieldPermissionDenied = 9
  22. adFieldReadOnly = 24
  23. adFieldResourceExists = 19
  24. adFieldResourceLocked = 18
  25. adFieldResourceOutOfScope = 25
  26. adFieldSchemaViolation = 11
  27. adFieldSignMismatch = 5
  28. adFieldTruncated = 4
  29. adFieldUnavailable = 8
  30. adFieldVolumeNotFound = 21
FilterGroupEnum
  1. adFilterAffectedRecords = 2
  2. adFilterConflictingRecords = 5
  3. adFilterFetchedRecords = 3
  4. adFilterNone = 0
  5. adFilterPendingRecords = 1
GetRowsOptionEnum
  1. adGetRowsRest = -1
IsolationLevelEnum
  1. adXactBrowse = 256
  2. adXactChaos = 16
  3. adXactCursorStability = 4096
  4. adXactIsolated = 1048576
  5. adXactReadCommitted = 4096
  6. adXactReadUncommitted = 256
  7. adXactRepeatableRead = 65536
  8. adXactSerializable = 1048576
  9. adXactUnspecified = -1
LineSeparatorEnum
  1. adCR = 13
  2. adCRLF = -1
  3. adLF = 10
LockTypeEnum
  1. adLockBatchOptimistic = 4
  2. adLockOptimistic = 3
  3. adLockPessimistic = 2
  4. adLockReadOnly = 1
MarshalOptionsEnum
  1. adMarshalAll = 0
  2. adMarshalModifiedOnly = 1
MoveRecordOptionsEnum
  1. adMoveAllowEmulation = 4
  2. adMoveDontUpdateLinks = 2
  3. adMoveOverWrite = 1
  4. adMoveUnspecified = -1
ObjectStateEnum
  1. adStateClosed = 0
  2. adStateConnecting = 2
  3. adStateExecuting = 4
  4. adStateFetching = 8
  5. adStateOpen = 1
ParameterAttributesEnum
  1. adParamLong = 128
  2. adParamNullable = 64
  3. adParamSigned = 16
ParameterDirectionEnum
  1. adParamInput = 1
  2. adParamInputOutput = 3
  3. adParamOutput = 2
  4. adParamReturnValue = 4
  5. adParamUnknown = 0
PersistFormatEnum
  1. adPersistADTG = 0
  2. adPersistXML = 1
PositionEnum
  1. adPosBOF = -2
  2. adPosEOF = -3
  3. adPosUnknown = -1
PropertyAttributesEnum
  1. adPropNotSupported = 0
  2. adPropOptional = 2
  3. adPropRead = 512
  4. adPropRequired = 1
  5. adPropWrite = 1024
RecordCreateOptionsEnum
  1. adCreateCollection = 8192
  2. adCreateNonCollection = 0
  3. adCreateOverwrite = 67108864
  4. adCreateStructDoc = -2147483648
  5. adFailIfNotExists = -1
  6. adOpenIfExists = 33554432
RecordOpenOptionsEnum
  1. adDelayFetchFields = 32768
  2. adDelayFetchStream = 16384
  3. adOpenAsync = 4096
  4. adOpenExecuteCommand = 65536
  5. adOpenOutput = 8388608
  6. adOpenRecordUnspecified = -1
RecordStatusEnum
  1. adRecCanceled = 256
  2. adRecCantRelease = 1024
  3. adRecConcurrencyViolation = 2048
  4. adRecDBDeleted = 262144
  5. adRecDeleted = 4
  6. adRecIntegrityViolation = 4096
  7. adRecInvalid = 16
  8. adRecMaxChangesExceeded = 8192
  9. adRecModified = 2
  10. adRecMultipleChanges = 64
  11. adRecNew = 1
  12. adRecObjectOpen = 16384
  13. adRecOK = 0
  14. adRecOutOfMemory = 32768
  15. adRecPendingChanges = 128
  16. adRecPermissionDenied = 65536
  17. adRecSchemaViolation = 131072
  18. adRecUnmodified = 8
RecordTypeEnum
  1. adCollectionRecord = 1
  2. adSimpleRecord = 0
  3. adStructDoc = 2
ResyncEnum
  1. adResyncAllValues = 2
  2. adResyncUnderlyingValues = 1
SaveOptionsEnum
  1. adSaveCreateNotExist = 1
  2. adSaveCreateOverWrite = 2
SchemaEnum
  1. adSchemaActions = 41
  2. adSchemaAsserts = 0
  3. adSchemaCatalogs = 1
  4. adSchemaCharacterSets = 2
  5. adSchemaCheckConstraints = 5
  6. adSchemaCollations = 3
  7. adSchemaColumnPrivileges = 13
  8. adSchemaColumns = 4
  9. adSchemaColumnsDomainUsage = 11
  10. adSchemaCommands = 42
  11. adSchemaConstraintColumnUsage = 6
  12. adSchemaConstraintTableUsage = 7
  13. adSchemaCubes = 32
  14. adSchemaDBInfoKeywords = 30
  15. adSchemaDBInfoLiterals = 31
  16. adSchemaDimensions = 33
  17. adSchemaForeignKeys = 27
  18. adSchemaFunctions = 40
  19. adSchemaHierarchies = 34
  20. adSchemaIndexes = 12
  21. adSchemaKeyColumnUsage = 8
  22. adSchemaLevels = 35
  23. adSchemaMeasures = 36
  24. adSchemaMembers = 38
  25. adSchemaPrimaryKeys = 28
  26. adSchemaProcedureColumns = 29
  27. adSchemaProcedureParameters = 26
  28. adSchemaProcedures = 16
  29. adSchemaProperties = 37
  30. adSchemaProviderSpecific = -1
  31. adSchemaProviderTypes = 22
  32. adSchemaReferentialConstraints = 9
  33. adSchemaSchemata = 17
  34. adSchemaSets = 43
  35. adSchemaSQLLanguages = 18
  36. adSchemaStatistics = 19
  37. adSchemaTableConstraints = 10
  38. adSchemaTablePrivileges = 14
  39. adSchemaTables = 20
  40. adSchemaTranslations = 21
  41. adSchemaTrustees = 39
  42. adSchemaUsagePrivileges = 15
  43. adSchemaViewColumnUsage = 24
  44. adSchemaViews = 23
  45. adSchemaViewTableUsage = 25
SearchDirectionEnum
  1. adSearchBackward = -1
  2. adSearchForward = 1
SeekEnum
  1. adSeekAfter = 8
  2. adSeekAfterEQ = 4
  3. adSeekBefore = 32
  4. adSeekBeforeEQ = 16
  5. adSeekFirstEQ = 1
  6. adSeekLastEQ = 2
StreamOpenOptionsEnum
  1. adOpenStreamAsync = 1
  2. adOpenStreamFromRecord = 4
  3. adOpenStreamUnspecified = -1
StreamReadEnum
  1. adReadAll = -1
  2. adReadLine = -2
StreamTypeEnum
  1. adTypeBinary = 1
  2. adTypeText = 2
StreamWriteEnum
  1. adWriteChar = 0
  2. adWriteLine = 1
StringFormatEnum
  1. adClipString = 2
XactAttributeEnum
  1. adXactAbortRetaining = 262144
  2. adXactCommitRetaining = 131072

Use Cases

The ActiveX Data Objects library can be used to work with databases and streams.

Databases

The ADODB library can connect to various kinds of databases. SQL databases, Access databases, and Excel files are just some of the databases the ADODB library can work with. To connect to a particular type of data source use the appropriate connection string.

Public Sub Example()

    '''Get data from .xlsx file

    Dim SourcePath As String
    SourcePath = Environ$("USERPROFILE") & "\Desktop\TestSource.xlsx"

    Dim ConnectionString As String
    ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & SourcePath & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    Dim CommandText As String
    CommandText = "SELECT * FROM [Sheet1$]"

    Dim CN As ADODB.Connection
    Set CN = CreateObject("ADODB.Connection")

    Dim RS As ADODB.Recordset
    CN.Open ConnectionString
    Set RS = CN.Execute(CommandText)

    Dim WS As Worksheet
    Set WS = ThisWorkbook.Worksheets(1)
    WS.Range("A1").CopyFromRecordset RS

    RS.Close
    CN.Close

    Set RS = Nothing
    Set CN = Nothing

End Sub

Streams

The ADODB Stream class can be used to work with streams of binary or text data. The Stream class can be used to create UTF-8 text files.

Public Sub WriteToTextFileUTF8(FilePath As String, TextContent As String)

    Dim S As Object 'ADODB.Stream
    Set S = CreateObject("ADODB.Stream")

    Dim S1 As Object 'ADODB.Stream
    Set S1 = CreateObject("ADODB.Stream")

    With S
        .Type = 2 'adTypeText
        .Charset = "UTF-8"
        .Open
        .WriteText TextContent
        .Position = 3
        With S1
            .Type = 1 'adTypeBinary
            .Open
            S.CopyTo S1
            .SaveToFile FilePath, 2 'adSaveCreateOverWrite
            .Close
        End With
        .Close
    End With

End Sub
Public Function ReadFromTextFileUTF8(FilePath As String) As String

    Dim ADOStream As Object
    Dim OutputText As String

    Set ADOStream = CreateObject("ADODB.Stream")

    With ADOStream
        .Charset = "UTF-8"
        .Open
        .LoadFromFile FilePath
        OutputText = .ReadText()
    End With

    ADOStream.Close
    Set ADOStream = Nothing

    ReadFromTextFileUTF8 = OutputText

End Function