You can use ADO to execute SQL commands to manipulate data and manage your database. Using SQL requires that the data should be entered as per the table structure, the columns sequence and their data type. SQL, stands for Structured Query Language, is a computer language, and SQL statements are used to store, retrieve and manipulate data in a relational database ex. Access Database.

A relational database has multiple tables, each table comprising of columns (fields) and rows wherein data can be grouped using common attributes, and from which data can be easily retrieved. The multiple tables relate to and work with each other through certain key fields (like unique “ID Nos”) viz. a separate table is created for vendor-details and another table for purchase-orders wherein vendor details are not repeated each time a purchase order is made and they are related with each other through a unique “Vendor ID” column which is present in both tables. Whereas flat file databases contain a single table of data with fields representing all parameters viz. a single table will contain details of vendors and purchase orders made, and with each purchase order the vendor details also get repeated resulting in duplication. Flat file databases though easier to maintain, are far less robust and flexible.

A Database Management System (DBMS) is a program which manages data in databases, the manner in which data is created, stored, modified and accessed. A Relational Database Management System (RDBMS) is the DBMS in respect of a relational database. Microsoft Access is a popular RDBMS which supports creating relational databases.
Other examples of DBMS are MySQL, Microsoft SQL Server, Oracle, etc.

In SQL, you can use DDL or DML statements. DDL (stands for Data Definition Language) statements are used to make structural changes, to define the database structure and objects like tables and columns. Examples of some often used DDL commands are CREATE TABLE, DROP TABLE and ALTER TABLE. DML statements are used to managing and manipulating data within the database structure and deal with inserting, updating, deleteing and retrieving database information like records in tables. Examples of often used DML commands are include INSERT, SELECT, UPDATE and DELETE.

SQL Commands explained:

The SQL SELECT Statement (the most commonly used SQL command) selects data from a database, specifying column(s) headings, table(s) from which columns are to be selected, and the criteria for selection. SELECT * is used to select all columns from a table. The FROM clause in a SELECT statement specifies the Table from which columns are to be selected. The WHERE clause in a SELECT statement specifies the criteria for selection. Syntax: SELECT column_name FROM table_name [WHERE clause]. You can also add other optional clauses, viz. GROUP BY, HAVING and ORDER BY.

Use the INSERT command to insert a new row/record at the end of a table.

DELETE removes a specified row(s) from a table (in the DELETE statement, FROM clause is required and WHERE clause is optional).

TRUNCATE TABLE deletes all the rows from the table (does not have the WHERE clause), and the table structure remains.

UPDATE modifies values of records in a table.

CREATE TABLE creates a table with the specified fields.

DROP TABLE deletes all rows and the table structure is removed from the database.

ALTER TABLE is used to add, remove or modify columns in a table.

Using the ADO connection Execute method to execute the specified query and SQL statements

Execute Method (ADO Connection): The Execute method on the specified ADO connection object, executes the SQL statement passed in the CommandText argument. Note that Recordset object returned by the method is a read-only, forward-only cursor. Syntax: Set recordset = connection.Execute (CommandText, RecordsAffected, Options).

CommandText is a string value, and is an SQL statement, stored procedure, or provider-specific text. RecordsAffected is an optional Long variable which indicates the number of records affected by the method. Options is an optional Long value which determines how the CommandText argument is evaluated: adCmdUnspecified – indicates that the CommandText property is unspecified; adCmdText – evaluates CommandText as a textual definition of a command or stored procedure call; adCmdTable – evaluates CommandText as a table name whose columns are all returned by an SQL query; adCmdStoredProc – evaluates CommandText as a stored procedure name; adCmdUnknown (default) – type of command in the CommandText argument is not known; Values of adCmdFile or adCmdTableDirect are not to be used with the Execute Method.

Example 2: Use ADO with SQL statements.

Refer to Images 3a & 3b, as mentioned in the code.

Sub automateAccessADO_2()
‘In this example we show how to use ADO with SQL statements:
‘1. Add and delete records using the ADO connection Execute method to execute SQL statements;
‘2. Select Records/Fields conditionally, using the ADO Recordset Open Method with SQL statements.

‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft ActiveX Data Objects x.x Library from the list.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String
Dim strSQL As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘Connect to a data source:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
connDB.Open ConnectionString:=“Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘——————–
‘CREATE AND DELETE RECORDS

‘Besides using ADO by itself for entering data (viz. using the AddNew method of the Recordset object) as shown earlier, you can also use ADO with SQL statements.

‘Refer Image 3a for SalesManager Table in MS Access file SalesReport.accdb; Image 3b shows the SalesManager Table after adding new record (before it is deleted).

‘add new records in the SalesManager Table using SQL INSERT INTO statements, by specifying both the column names and the values to be inserted. The Execute method on the specified ADO connection object, executes the SQL statement passed in the CommandText argument:
connDB.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate) VALUES (2, ‘Mary’, ‘Smith’, ‘6/20/2009’)”
‘alternatively add a new record by specifying only the column values to be inserted:
‘connDB.Execute “INSERT INTO SalesManager VALUES (2, ‘Mary’, ‘Smith’, ‘6/20/2009’)”

‘delete records in the SalesManager Table:
strSQL = “DELETE FROM SalesManager WHERE FirstName = ‘Mary’”
connDB.Execute CommandText:=strSQL

‘Value of 0 will be returned which indicates that the recordset is closed, because it was never opened (value of 1 indicates that the recordset is open):
MsgBox adoRecSet.State

‘——————–
‘SELECT RECORDS / FIELDS CONDITIONALLY

‘Refer Image 3a for SalesManager Table in MS Access file SalesReport.accdb, for below codes.

‘select a specific field (FirstName), basis numeric reference:
strSQL = “SELECT FirstName FROM SalesManager WHERE EmployeeId = 18″
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
‘returns “Tracy”:
MsgBox adoRecSet.Fields(“FirstName”)
adoRecSet.Close

‘select a specific field (FirstName), basis text reference:
strSQL = “SELECT FirstName FROM SalesManager WHERE SurName = ‘Green’”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
‘returns “Sam”:
MsgBox adoRecSet.Fields(“FirstName”)
adoRecSet.Close

‘select multiple fields, basis numeric reference:
strSQL = “SELECT FirstName, SurName, JoinDate FROM SalesManager WHERE EmployeeId = 18″
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
‘returns “Tracy Murray 7/16/2011”:
MsgBox adoRecSet.Fields(“FirstName”) & ” ” & adoRecSet.Fields(“SurName”) & ” ” & adoRecSet.Fields(“JoinDate”)
adoRecSet.Close

‘select a specific field, basis multiple criteria (delimit date values by the datetime delimiter “#”):
‘to select a range between two dates, the BETWEEN operator has been used in the WHERE clause.
strSQL = “SELECT EmployeeId FROM SalesManager WHERE SurName = ‘Green’ and JoinDate between #08/3/2012# and #10/3/2013#”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
‘returns “56”:
MsgBox adoRecSet.Fields(“EmployeeId”)
adoRecSet.Close

‘select all fields, basis text reference:
strSQL = “SELECT * FROM SalesManager WHERE SurName = ‘Kelly’”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
‘returns “35 David Kelly 1/24/2010”:
MsgBox adoRecSet.Fields(“EmployeeId”) & ” ” & adoRecSet.Fields(“FirstName”) & ” ” & adoRecSet.Fields(“SurName”) & ” ” & adoRecSet.Fields(“JoinDate”)
adoRecSet.Close

‘select all fields, basis criteria specified using the Like operator:
‘The Like operator compares two strings, if the value in the string matches the pattern, it returns True. The percent sign (%) indicates zero or more characters after J ie. the criteria is if the first name starts with J:
‘Note that in the WHERE clause of SQL statement, percent (%) is used as a wild character instead of an asterisk (*), indicating zero or more characters after J. In the WHERE clause of SQL statement, use underscore (_) to indicate a single character.
strSQL = “SELECT * FROM SalesManager WHERE FirstName Like ‘J%'”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

‘return all instances where criteria (WHERE FirstName Like ‘J%’) is met – “Mason” & “Davis”:
Do While Not adoRecSet.EOF

MsgBox adoRecSet.Fields(“Surname”)

adoRecSet.MoveNext

Loop

adoRecSet.Close

‘——————
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Example 3: Edit Records, using ADO with SQL statements.

Refer to Images 4a & 4b, as mentioned in the code.

Sub automateAccessADO_3()
‘In this example we show how to Edit Records, using ADO with SQL statements:
‘Refer Image 4a for SalesManager Table in MS Access file SalesReport.accdb before edit, and Refer Image 4b after edit.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String
Dim strSQL As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘SEARCH RECORDS BASIS SPECIFIED CRITERIA AND EDIT THEM

‘select all fields, basis JoinDate criteria:
strSQL = “SELECT * FROM SalesManager WHERE JoinDate >= #01/01/2010#”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

‘return all instances where criteria (WHERE JoinDate >= #01/01/2010#) is met, and marks their FirstName & Surname with “M”:
Do While Not adoRecSet.EOF

adoRecSet.Fields(“FirstName”) = adoRecSet.Fields(“FirstName”) & ” – M”
adoRecSet.Fields(“Surname”) = adoRecSet.Fields(“Surname”) & ” – M”

adoRecSet.MoveNext

Loop

‘——————
‘close the objects
adoRecSet.Close
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Use the OpenSchema Method to access information about database tables and columns

The OpenSchema Method returns a Recordset object containing schema (structure) information about the database, like the tables and columns in the database and the supported data types. Syntax: Set recordset = connection.OpenSchema(QueryType, Criteria, SchemaID).

The first argument in the OpenSchema Method which is a must, is the type of schema required which is a SchemaEnum value (ie. an enumerated value) which specifies the type of Recordset returned by the method. Examples include adSchemaTables which returns the tables defined in the catalog, and adSchemaColumns which returns the columns of tables defined in the catalog.

To limit the results of the schema query, use the Criteria argument (this is the second parameter in the OpenSchema Method, and is optional) which specifies an array of values (ie. query constraints).
Query constraints (ie. criteria) in the SchemaEnum of adSchemaTables are: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE.
Query constraints (ie. criteria) in the SchemaEnum of adSchemaColumns are: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME.

The third argument of SchemaID is used only if the first argument is set to adSchemaProviderSpecific.

Example 4: Use the OpenSchema Method to access information about the database tables and columns.

Refer to Image 5, as mentioned in the code.

Sub automateAccessADO_4()
‘Use the OpenSchema Method to access information about the database tables and columns.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘ACCESS INFORMATION ABOUT THE DATABASE TABLES AND COLUMNS

‘Refer Image 5 for SalesManager Table in MS Access file SalesReport.accdb which also contains another Table named Performance.

‘code to get names of all tables in a database – returns table names ‘Performance’ & ‘SalesManager’:
‘query constraint of TABLE_TYPE is specified as “TABLE”
Set adoRecSet = connDB.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, “TABLE”))

Do While Not adoRecSet.EOF

MsgBox adoRecSet.Fields(“TABLE_NAME”).Value

adoRecSet.MoveNext

Loop

adoRecSet.Close

‘alternate code to get names of all tables in a database – returns table names ‘Performance’ & ‘SalesManager’:

Set adoRecSet = connDB.OpenSchema(adSchemaTables)

Do While Not adoRecSet.EOF

If adoRecSet.Fields(“TABLE_TYPE”) = “TABLE” Then

MsgBox adoRecSet.Fields(“TABLE_NAME”).Value

End If

adoRecSet.MoveNext

Loop

adoRecSet.Close

‘to get names of all fields of a specified table (“SalesManager”):
‘Field Names returned are: EmployeeId, FirstName, Surname & JoinDate.

Set adoRecSet = connDB.OpenSchema(adSchemaColumns, Array(Empty, Empty, “SalesManager”, Empty))

Do While Not adoRecSet.EOF

MsgBox adoRecSet.Fields(“COLUMN_NAME”).Value

adoRecSet.MoveNext

Loop

adoRecSet.Close

‘————–
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Create a database table using ADO, with SQL statements

Note that ADO does not by itself support creating database tables, which is actually done with ADOX. However, you can create a database table in ADO using SQL.

SQL CREATE TABLE statement creates a table with the specified fields. SQL INSERT INTO statement inserts a new row/record at the end of the table.

While using the SQL CREATE TABLE statement, you specify the Table Name, Field Names and their Data Types. Commonly used data types for fields include: TEXT (SIZE) for Text Field; CHAR (SIZE) for Text Field; SHORT for Numbers-Integer; LONG for Numbers-Long; SINGLE for Numbers-Single; DOUBLE for Numbers-Double; CURRENCY for Currency; DATE for Date/Time; DATETIME for Date/Time; YESNO for Boolean values; and so on.

Use the CONSTRAINT clause in SQL statements, to create a constraint on one or more fields:

Specifying the PRIMARY KEY (can be only one in a Table) reserved word designates a field(s) as a primary field whose values will be unique and cannot be Null.

Specifying NOT NULL for a field will necessitate the new record to have valid data in that field. A table column, by default, can have a NULL value ie. a field can contain no value.

Specifying the UNIQUE reserved word will not allow a same value in that field, for two records in the table.

Example 5: Create a New Database Table using ADO, with SQL statements.

Refer to Image 6, as mentioned in the code.

Sub automateAccessADO_5()
‘Create a new database table using ADO with SQL statements.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘CHECK IF THE NEW TABLE NAME IS PRE-EXISTING IN DATABASE, AND DELETE IF FOUND

‘check if the table named SalesManager exists in database, and delete if found:

Set adoRecSet = connDB.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, “TABLE”))

Do While Not adoRecSet.EOF

If adoRecSet.Fields(“TABLE_NAME”).Value = “SalesManager” Then

connDB.Execute “DROP TABLE SalesManager”

End If

adoRecSet.MoveNext

Loop

adoRecSet.Close

‘————–
‘CREATE A NEW TABLE IN DATABASE
‘Refer Image 6

‘Create a table named SalesManager, with field names and their data types (viz. the field named FirstName is of Text data type and can hold 40 characters).

‘add a PRIMARY KEY CONSTRAINT named ‘pk_EI’ for the column ‘EmployeeID’ of the ‘SalesManager’ table; also add a UNIQUE CONSTRAINT named ‘un_FN’ for the column ‘FirstName’ of the ‘SalesManager’ table.

connDB.Execute “CREATE TABLE SalesManager(EmployeeId LONG, FirstName Text(40), Surname Char(50) NOT NULL, JoinDate Date, Sales Double, CONSTRAINT pk_EI PRIMARY KEY (EmployeeID), CONSTRAINT un_FN UNIQUE (FirstName))”

‘Populate the table using SQL INSERT INTO statements, without specifying the column names but only their values:
connDB.Execute “INSERT INTO SalesManager VALUES (256, ‘Mary’, ‘Lange’, ‘7/24/2008’, 15678.58)”
connDB.Execute “INSERT INTO SalesManager VALUES (587, ‘Harry’, ‘Davis’, ‘7/16/2011’, 14673.26)”
connDB.Execute “INSERT INTO SalesManager VALUES (01, ‘James’, ‘Bond’, ‘3/11/2009’, 12589)”

‘Populate the table using SQL INSERT INTO statements, by specifying both the column names and the values to be inserted:
connDB.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate, Sales) VALUES (445, ‘John’, ‘Morgan’, ‘1/24/2010’, 12432.20)”
connDB.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate, Sales) VALUES (25, ‘Dane’, ‘Large’, ’10/3/2012′, 9876.5)”

‘————–
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

ADO Find Method – Find or Locate a specific Record

Find Method (ADO) searches for a row, in a set of records, basis specified criteria. Syntax: Find (Criteria, SkipRows, SearchDirection, Start).

Criteria specifies the criteria for finding the record. This argument is rquired whle all others are optional. Note that only one criteria is supported by the ADO Find method.

SkipRows specifies the number of records to be skipped, where the deafult value is 0 wherein the current record is not skipped.

Use the default SearchDirection value of adSearchForward to search forward from the current record, and if a match is not found the position of the record pointer is at EOF ie. after the last record in the set of records. To search backward, use the value of adSearchBackward and if a match is not found the position of the record pointer is at BOF ie. before the first record in the set of records.

Start argument sets a current row position before starting a search. Default value of adBookmarkCurrent indicates the current record, value of adBookmarkFirst indicates the first record and value of adBookmarkLast indicates the last record.

Note: On a match being found, the found record becomes the the current row position, and because the search starts from the current row, you must either skip a record OR move to the next record to do a new find for the next match.

Example 6: Using the Find method to find or locate a specific record(s).

Refer to Image 7, as mentioned in the code.

Sub automateAccessADO_6()
‘Using the Find method to find or locate a specific record(s).

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘USE FIND METHOD
‘Refer Image 7 to view the SalesManager Table in MS Access file “SalesReport.accdb”

‘open Recordset ie. SalesManager Table:
adoRecSet.Open Source:=“SalesManager”, ActiveConnection:=connDB, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

‘note that only one criteria is supported by the ADO Find method:
adoRecSet.Find “EmployeeID > 8”, , adSearchForward

Do While Not adoRecSet.EOF

‘using an IF statement, for search with an additional criteria of FirstName starting with J within the Search criteria of EmployeeID > 8:
‘Note that the J* indicates all words starting with the letter J followed by zero or more characters in the IF statement, but in the WHERE clause of an SQL statement it will be used as J% (percent is used as a wild character instead of an asterisk, indicating zero or more characters after J) viz. “FirstName LIKE ‘J%'”.

If adoRecSet.Fields(“FirstName”) Like “J*” Then

‘2 records are found using the Find Method: John Mason, Employee Id:12; Jim Davis, Employee Id:21.

MsgBox adoRecSet.Fields(“FirstName”) & ” ” & adoRecSet.Fields(“Surname”) & “, Employee Id:” & adoRecSet.Fields(“EmployeeId”)

i = i + 1

End If

‘on a match being found, the found record becomes the the current row position, and because the search starts from the current row, you must either skip a record OR move to the next record to do a new find for the next match:
adoRecSet.MoveNext
adoRecSet.Find “EmployeeID > 8”, , adSearchForward
‘OR skip 1 row:
‘adoRecSet.Find “EmployeeID > 8”, 1, adSearchForward

Loop

‘2 records are found
MsgBox “Records found: ” & i

‘————–
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

ADO Filter Property to FilterRecords

Filter database table records, using the Filter Property (ADO) on a recordset. Use a filter when you want to screen out selective records in a table. Syntax: recordset.Filter = Criteria.

The Criteria string contains clauses in the format of “FirstName = ‘Jim’” where FirstName is the Column or Field Name, = is the Operator, and Jim is the Field Value. The Criteria string can have multiple conditions or clauses.

The following operators can be used: <, >, <=, >=, <>, =, or LIKE.

For field values: use single quotes for strings; “#” for date values; you can use wild cards asterisk (*) and percent (%), provided they are the last character in the string, for the LIKE operator.

Example 7: Filter Records using the ADO Filter Property.

Refer to Image 7, as mentioned in the code.

Sub automateAccessADO_7()
‘Filter database table records, using the Filter Property (ADO) on a recordset. Use a filter when you want to screen out selective records in a table.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘FILTER RECORDS
‘Refer Image 7 to view the SalesManager Table in MS Access file “SalesReport.accdb”, used for below filter criteria.

‘Place the filter BEFORE opening the recordset.
‘You can use wild cards asterisk (*) and percent (%), provided they are the last character in the string, for the LIKE operator.
‘Examples of 5 valid filter criteria have been given below.

‘adoRecSet.Filter = “FirstName = ‘Jim’ OR EmployeeID > 35″
‘adoRecSet.Filter = “FirstName LIKE ‘J*'”
‘adoRecSet.Filter = “Surname LIKE ‘%a%'”
‘adoRecSet.Filter = “(FirstName = ‘Jim’ AND EmployeeID > 18) OR (Surname = ‘Green’ AND EmployeeID > 35)”
adoRecSet.Filter = “(FirstName = ‘Jim’) OR (Surname = ‘Green’ AND EmployeeID > 35)”

‘Incorrect use of OR: you are not allowed to group clauses by the OR operator and then group these to another clause with the AND operator, in the following manner:
‘adoRecSet.Filter = “(FirstName = ‘Sam’) AND (FirstName = ‘Jim’ OR EmployeeID > 35)”

adoRecSet.Open Source:=“SalesManager”, ActiveConnection:=connDB, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

Do While Not adoRecSet.EOF

MsgBox adoRecSet.Fields(“FirstName”) & ” ” & adoRecSet.Fields(“Surname”) & “, Employee Id:” & adoRecSet.Fields(“EmployeeId”)
i = i + 1
‘on a match being found, the found record becomes the the current row position, and because the search starts from the current row, you must move to the next record to find the next match:
adoRecSet.MoveNext

Loop

MsgBox “Records found: ” & i

‘————–
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Example 8: Using ADO with SQL Statements to: Add, Delete and Modify Columns in an Existing Table; Add and Delete Constraints on Columns.

Refer to Images 8a & 8b, as mentioned in the code.

Sub automateAccessADO_8()
‘Using ADO with SQL Statements to: Add, Delete and Modify Columns in an Existing Table; Add and Delete Constraints on Columns.

‘————–
‘DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String
Dim strSQL As String

‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

‘————–
‘THE CONNECTION OBJECT

strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName

‘————–
‘ADD, DELETE OR MODIFY COLUMNS IN AN EXISTING TABLE USING THE SQL ‘ALTER TABLE’ STATEMENT.
‘Refer Image 8a to view the SalesManager Table in MS Access file “SalesReport.accdb” before running below code

‘Run the ADO connection Execute method, to ALTER TABLE, without opening the recordset. SQL command ADD COLUMN adds a new column at the end of the existing columns – in below code we are adding 3 columns named Telephone, City & ZipCode.
strSQL = “ALTER TABLE SalesManager ADD COLUMN Telephone char(15), COLUMN City Text(30), COLUMN ZipCode char(6)”
connDB.Execute CommandText:=strSQL

‘modify/enter column values after opening recordset – refer Image 8b which shows table after entering values as below:
strSQL = “SELECT * FROM SalesManager WHERE EmployeeId = 12″
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

With adoRecSet

.Fields(“Telephone”) = “421-1234567”
.Fields(“City”) = “New York”
.Fields(“ZipCode”) = “10453”

.Update

End With

adoRecSet.Close

‘Run the ADO connection Execute method, to ALTER TABLE, without opening the recordset.

‘delete the Telephone column in the SalesManager Table:
strSQL = “ALTER TABLE SalesManager DROP COLUMN Telephone”
connDB.Execute CommandText:=strSQL

‘change the data type of the ZipCode column in the SalesManager Table:
strSQL = “ALTER TABLE SalesManager ALTER COLUMN ZipCode Long”
connDB.Execute CommandText:=strSQL

‘delete the City & ZipCode columns in the SalesManager Table:
strSQL = “ALTER TABLE SalesManager DROP COLUMN City, ZipCode”
connDB.Execute CommandText:=strSQL

‘————-
‘ADD AND DELETE CONSTRAINTS ON COLUMNS

‘add a PRIMARY KEY CONSTRAINT named ‘pk_EI’ for the column ‘EmployeeID’ of the ‘SalesManager’ table:
strSQL = “ALTER TABLE SalesManager ADD CONSTRAINT pk_EI PRIMARY KEY (EmployeeID)”
connDB.Execute CommandText:=strSQL

‘drop the existing PRIMARY KEY CONSTRAINT named ‘pk_EI’ from the ‘SalesManager’ table:
strSQL = “ALTER TABLE SalesManager DROP CONSTRAINT pk_EI”
connDB.Execute CommandText:=strSQL

‘add a UNIQUE CONSTRAINT named ‘un_FN’ for the column ‘FirstName’ of the ‘SalesManager’ table:
strSQL = “ALTER TABLE SalesManager ADD CONSTRAINT un_FN UNIQUE (FirstName)”
connDB.Execute CommandText:=strSQL

‘drop the existing CONSTRAINT named ‘un_FN’ from the ‘SalesManager’ table:
strSQL = “ALTER TABLE SalesManager DROP CONSTRAINT un_FN”
connDB.Execute CommandText:=strSQL

‘————–
‘close the objects
connDB.Close

‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Microsoft Access: Use ADO to Execute SQL statements

Microsoft Access: Use ADO to Execute SQL statements

Microsoft Access: Use ADO to Execute SQL statements

VBA TUTORIAL LOGIN

Cond Statements in VBA Test

Text Strings in VBA

Custom Number Formats

Excel VBA Built-in Events

ActiveX & Form Controls

Application.OnTime Method

MsgBox & InputBox in VBA

Leave a Reply Cancel reply