° glokal Iconwect Excel and VBA Solutions Contact (/excel/index.php? ‘option=com_contact&view=contact&id=114ltemid= My Account (/excel/index php? ‘option=com_content&view=article&id=3608itemi VBA Tutorial Login Hi prakash, Excel VBA Tutorialk® Excel VBA Basics (lexcel/index.php? ‘option=com_content&iview=article&id=1908itemid= Excel VBA Objects (/excellindex.php? ‘option=com_content8view=article&id=2768itemi Working with Variables (excelfindex.php? ‘option=com_contentview=article&id=2778ltemid Conditional Statements (excelfindex php? ‘option=com_content8view=article&id=3288itemid= Excel VBA Loops (/excel/index.php? ‘option=com_content&view=article&id=3268itemi Excel Application Object (excelfindex.php? ‘option=com_content€view=article&id=3208ltemid=! Workbooks & Worksheets (excelfindex php? ‘option=com_content&iview=article&id=3378itemid Excel VBA Cells & Ranges (excel/index.php? ‘option=com_contont8viow=articlo&id=2878itemi Text, String & Date Functions (excelfindex.php? ‘option=com_content&view=article&id=2928ltemid Excel VBA Procedures (excel/index.php? ‘option=com_content&view=article&id=3398itemid Excel VBA Arrays (/excel/index.php? ‘option=com_content8view=article&id=3318itemi Error Handling & Debugging (excelfindex php? ‘option=com_content&iview=article&id=3508itemid=542) Excel VBA Events (/excel/index.php? ‘option=com_content8view=article&id=3038itemid=544) Custom Classes & Objects (excelfindex.php? excell) (Search‘option=com_content8view=article&id=3418itemi ‘ActiveX & Form Controls, AutoShapes (excelfindex.php? ‘option=com_content&iview=article&id=3628itemid=615) UserForms in Excel VBA (excelfindex.php? ‘option=com_content8view=article&id=3078itemi 50) Charts in Excel VBA (Jexcel/index-php? ‘option=com_content&view=article&id=3538itemi Excel Pivot Tables with VBA. (oxcelfindex.php? ‘option=com_content&view=article&id=3328itemid=! ‘Automation with VBA (Vexcelfindex.php? ‘option=com_content8view=article&id=2498itemi Connect with Databases (ADO) (excelfindex.php? ‘option=com_contentview=article&id=2568itemi Microsoft Access: ADO Library (Jexcellindex.php?option=com_content&view=article&id=2568 temid=582) Execute SQL statements - ADO (/excelindex phpoption=com_content&view=artcle&id=2574ltemid=583) m_content&view=articleBid=3468 tami 84) Import / Export Data, Access to Excel (/excelindex. hp?optio Connect to Access from Excel - ADOX (/excel/index. php7option=com_content&view=article&id=2598!temid=585) Connect with Databases (DAO) (excelfindex php? ‘option=com_content8view=articte&id=2608itemi 7) Connect with Databases (ADO) (/excellindex.phpoption=com_content&view=article&id=2568Iterid=582) » Execute SQL statements - ADO tototiok ae Vote 5 + [Rate Microsoft Access: Use ADO to Execute SQL statements Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA. Part 2 of 4 Use ADO to Execute SQL statements to Manage your Database 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 (ike unique "ID Nos") viz. a separate table is created for vendordetails and another table for purchase-orders ein vendor details are not repeated each time a purchase order is made and they are related with each other through a unique "Vendor 1D" column which is present in both tables. Whereas flatfile 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 fle databases though easier to maintain, are far less robust and flexible, ‘A Database Management System (DBMS) is @ 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 Ike 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) hheagings, 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 tho INSERT command to insert a new rowlrecord 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 withthe specified feds, DROP TABLE deletes all rows and the table structure is remaved from the dataase. 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 abject retuned 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 adCmaFile 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. All Tables =« SalesManager 1 Employeeld « FirstName ~ Surname - |JoinDate « 12 John Mason 7/28/2008 Performance im 18 Tracy Murray 7/16/2001 FE) pertormance: Table 2a sim Davis 3/11/2003 35 David Kelly 24/2010 56 Sam Green 10/3/2012 Image 3a ‘SalesManager | Employeeld + FirstName ~ Surname ~ | JoinDate ~ 5 Stemmetie 2May Smt shoo a a die hen ee EE] Performance : Table 18 Tracy Murray 7/16/2011 21m Davis 3/11/2009 35 David Kelly 1/28/2010 56 Sam Green 10/3/2012 Image 3b ‘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 lst ‘DIM STATEMENTS Dim strMyPath As String, strDBName As String, strOB 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 ‘stiDBName = "SalesReport.acedb' strMyPath = ThisWorkbook.Path ‘iDB = 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.¢.0". For Access 2007 ( acedb database) use the ACE Provider: "Microsoft, ACE. OLEDB. 12.0" ‘The ACE Provider can be used for both the Access .mdo & .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 ‘eatier, 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 itis 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 (Employeeld, FirstName, Sumame, JoinDate) VALUES (2, Mary’, “Smith, ‘6/20/2008 ‘altematively add @ 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: stiSQL = "DELETE FROM SalesManager WHERE FirstName = Mary" ‘connDB, Execute CommandText:=strSQL "Value of O 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 flle SalesReport accdb, for below codes. ‘select a specific field (FirstName), basis numeric reference: stiSQL = "SELECT FirstName FROM SalesManager WHERE Employeeld = 18° ‘adoRecSet, Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLackOptimistic "rotums "Tracy MsgBox adoReoSet Fields("FirstName") ‘adoRecSet. Close ‘select a specific field (FirstName), basis text reference: sliSQL = "SELECT FirstName FROM SalesManager WHERE SucName = Green” ‘adoRecSet. Open Source:=strSQL,, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adl ockOptimistic ‘retums "Sam": MsgBox adoRecSet Fields("FirstName") ‘adoRecSet.Close ‘select multiple fields, basis numeric reference: strSQL = "SELECT FirstName, SutNamo, JoinDate FROM SalesManager WHERE Employee ‘adoReeSet. Open Source:=strSQL, ActiveConnection:=conaDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic‘rotums “Tracy Murray 7/16/2011”: MsgBox adoRecSet,Fields("FirstName") & "* & adoRecSet Fields(*SurName") & "" & adoRecSet Fields(*JoinDato") adoRecSet.Close ‘select a specific field, basis multiple criteria (delimit date values by the datetime delimiter“) ‘Wo select a range between two dates, the BETWEEN operator has been used in the WHERE clause. strSQL = "SELECT Employeeld FROM SalesManager WHERE SurName = ‘Green’ and JoinDate between #08/3/2012# ‘and #10/3/2013#" ‘adoRecSet, Open Source:=strSQL, ActiveConnection:=conaDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic "rotums "56" MsgBox adoReoSet Fields("Employeeld) ‘adoRecSet. Close ‘select all fields, basis text reference: stiSQL = "SELECT * FROM SalesManager WHERE SurName = Kelly" adoRecSet. Open Source:=strSQL, ActiveConnection:=connDB, CursorType: LockType:=adLock Optimistic ‘retums "35 David Kelly 1/24/2010" MsgBox adoRecSet,Fields("Employeeld) & ~" & adoRecSel Fields("FitstName") & *" & adoRecSet Fields("SurName") &" " & adoRecSet Fields(‘JoinDate") ‘adoRecSet Close ‘select all fields, basis criteria specified using the Like operator: “The Like operator compares two strings, ifthe value in the string matches the pattom, it returns True. The percent sign (%) indicates zero or more characters after J ie. the criteria i 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: LockType:=adl ockOptimistic ‘retum all instances where criteria (WHERE FirstName Like 'J%’) is met - "Mason" & "Davis" Do While Not adoRecSet. EOF MsgBox adoRecSel Fields("Sumame") adoRecSet. MoveNext Loop ‘adoRecSet. Close 1dOpenDynamic, ‘close the objects cconnDB. 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. All Tables = 4 |] Sieemanager ‘| employed ~ Firstvame + [Surname ~ [JoInbate ~ | selesmanager:tabie 12 John Mason 7/24/2008. Performance a 38 Tracy Murray 7/t0/2001 ED pettormance: Table 21 Lim Davis 3/11/2009 a 35 David Kelly 1/24/2010 56 Sam Groen 10/3/2012 mage 4a ‘All Tables : ‘SolesManager | “Employeeld + FirstName ~ Surname JoinDate + 12 John Mason 7/24/2008 Performance A 48 Tracy-M — Murray-M_ 7/16/2011 ©) Perrormance: Table 21 tim Davis 3/11/2009 35 David-M —_kelly-M__ 4/24/2010 56Sam-M Green-M 10/2/2012, Image 4‘Sub automateAccessADO_3() “In this example we show how to Eat Records, using ADO with SQL statements: ‘Refer Image 4a for SalesManager Table in MS Access file SalesReport.accdo before edit, and Refer Image 4b after elt. ‘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 lst ‘DIM STATEMENTS Dim strMyPath As String, strDBName As String, strOB As String Dim strSQL As String ‘instantiate an ADO object using Dim with the New keywor: Dim adoReeSet As New ADODB Recordset Dim connDB As New ADODB.Connection “THE CONNECTION OBJECT ‘stiDBName = "SalesReport.acedb' ThisWorkbook Path trMyPath & "\" & strDBName ‘Connect to a data source: ‘For pre - MS Access 2007, .mab files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft Jet. OLEDB.4.0". For Access 2007 ( acedb databaso) 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 sourc astro "SEARCH RECORDS BASIS SPECIFIED CRITERIA AND EDIT THEM ‘select all fields, basis JoinDate criteria: stiSQL = "SELECT * FROM SalesManager WHERE JoinDate >= #01/01/2010#" ‘adoRecSet. Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic ‘retum all instances where crteria (WHERE JoinDa with "Mt Do While Not adoRecSet.EOF adoReeSet.Fields("FirstName") = adoRecSet Fields("FirstName") ‘adoRecSet Fields("Sumame") & 101/01/2010#) is met, and marks their FirstName & Sumame oe adoRecSet MoveNext Loop ‘close the objects adoRecSet.Close ‘connDB, Close “destroy the variables Sot adoRecSet = Nothing Set connDB = Nothing End Sub Use the OpenSchema Method to access information about database tables and columns ‘The OpenSchema Method retums a Recordset object containing schema (structure) information about the database, lke the tables and columns in the database and the supported data types. Syntax: Set recordset = connection OpenSchema(QueryType, Criteria, SchemalD), ‘The first argument in the OpenSchema Method which is a must, is the type of schema required which is a SchemaEnum value (le. an enumerated value) which specifies the type of Recordset returned by the method. Examples include adSchemaTables which retums the tables defined in the catalog, and adSchemaColumns which retums 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 ((e. query constraints) Query constraints (e. criteria) in the SchemaEnum of adSchemaTables are: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE. Query constraints (ie. cfiteia) in the SchemaEnum of adSchemaColumns are: TABLE_CATALOG, TABLE_SCHEMA, ‘TABLE_NAME, COLUMN_NAME, The third argument of SchemalD is used only ifthe first argument is set to adSchemaProviderSpeciticExample 4: Use the OpenSchema Method to access information about the database tables and columns. Refer to Image 5, as mentioned in the code. AlN Tables SalesManager | Employeetd ~ FirstName + /surname ~ | Joinbate ~ 22 Jebr Mason 7/24/2008 Performance 4B Tracy Muay 7/16/20 I) estormance Table 21 sim Davis 3/11/2009 | 35 David kelly 4/24/2010 55 Sam Green 10/3/2012 image 5 ‘Sub automateAccessADO_4() "Use the OpenSchema Method to access information about the database tables and columns, ‘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 lst ‘DIM STATEMENTS Dim strMyPath As String, sttDBName As String, strOB As String ‘instantiate an ADO object using Dim with the New keywor: Dim adoReeSet As New ADODB Recordset Dim connDB As New ADODB.Connection “THE CONNECTION OBJECT ‘stiDBName = "SalesReport.acedb" strMyPath = ThisWorkbook Path ‘tiDB = strMyPath & "\" & strDBName ‘Connect to a data source: ‘For pre - MS Access 2007, .mab files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft Jet. OLEDB.4.0" For Access 2007 ( acedb databaso) use the ACE Provider: "Microsoft, ACE. OLEDB. 12.0" ‘The ACE Provider can be used for both the Access mda & .accdb files. ‘connDB. Open ConnectionString:="Provider = Microsoft. ACE-OLEDB. 12.0; data sourc astroB "ACCESS INFORMATION ABOUT THE DATABASE TABLES AND COLUMNS ‘Refer Image 5 for SalesManager Table in MS Access file SalesReport.acedb which also contains another Table named Performance. ‘code to get names of all tables in a database - retums table names ‘Performance’ & 'SalesManager: ‘quary constraint of TABLE_TYPE is specified as “TABLE” Set adoRecSet = connDB. GpenSchema(adSchemaTables, Array(Empty, Emply, Empty, "TABLE")) Do While Not adoRecSet. EOF MsgBox adoReoSel.Fields("TABLE_NAME").Value adoReeSet. MoveNext Loop adoRecSet.Close “allemate code to get names of all tables in a database - 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 adoReeSet. MoveNext Loop ‘adoRecSet. Close tums table names Performance’ & 'SalesManager’ ‘to get names of all fields of a specified table (‘SalesManager"): ‘Field Names returned are: Employeeld, FirstName, Surame & JoinDate. Set adoRecSet = connDB. OpenSchemaladSchemaColumns, Array(Empty, Empty, “SalesManager’, Empty)) Do While Not adoRecSet. EOF MsgBox adoRecSel.Fields("COLUMN_NAME") Value adoRecSet. MoveNextLoop ‘adoRecSet. Close ‘elose 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 now rowirecord 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 0 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 willbe unique and cannot be Null ‘Specifying NOT NULL fora field will necessitate the new record to have valid data in that field. A table column, by defaut, ‘can have a NULL value ie. afield can contain no value. ‘Specifying the UNIQUE reserved word wil 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. All Tables =) @)[ ES Stesnanager SalesManager | Employeeid + FirstName ~ |Surname - |JoinDate ~ | Sales - 1 James Bond a/t1/2009 12589 Performance 4 25 Dane Large 10/3/2012, _ 9876.5 Perrormance: Table 256 Mary Lange 7/24/2008, 15678.58 445 John Morgan 1/24/2010 12432.2 587 Horry Davis 7/36/2011, 14873.26 Image 6 ‘Sub automateAccessADO_5() ‘Create anew database table using ADO 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 lst ‘DIM STATEMENTS Dim strMyPath As String, sttDBName As String, st 1B As String ‘instantiate an ADO object using Dim with the New keyword! Dim adoRacSet As New ADODB Recordset Dim connDB As New ADODB.Connection “THE CONNECTION OBJECT ‘stiDBName = "SalesReport.acedb strMyPath = ThisWorkbook.Path ‘tiDB = strMyPath & "\" & strDBName ‘Connect to a data source: ‘For pro - MS Access 2007, mab files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft Jet. OLEDB.4.0", For Access 2007 ( acedb databaso) use the ACE Provider: "Microsoft, ACE. OLEDB. 12.0",‘The ACE Provider can be used for both the Access .mdo & .accdb files. ‘connDB. Open ConnectionSiring:="Provider = Microsoft. ACE.OLEDB. 12.0; data sourc astroB ‘CHECK IF THE NEW TABLE NAME IS PRE-EXISTING IN DATABASE, AND DELETE IF FOUND ‘check ifthe table named SalesManager exists in database, and delete if found: Set adoRecSet = connDB. OpenSchemaladSchemaTables, Array(Emply, Emply, Emply, "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(Employesid LONG, FirstName Text(40), Sumame Char(50) NOT NULL, JoinDate Date, Sales Double, CONSTRAINT pk_E! PRIMARY KEY (EmployoelD), CONSTRAINT un_FN UNIQUE (FirstName)}" ‘Populate the table using SQL INSERT INTO statements, without specifying the column names but only thelr values: ‘connDB. Execute “INSERT INTO SalesManager VALUES (256, Mary’, Lange’, "7/24/2008", 15678.58)" ‘connDB Execute “INSERT INTO SalesManager VALUES (687, ‘Harry, Davis’ "7/16/2011", 14673.26)" ‘connDB Execute “INSERT INTO SalesManager VALUES (01, 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 (Employeeld, FirstName, Sumame, JoinDate, Sales) VALUES (445, “John, ‘Morgan, 1/24/2010, 1242.20)" ‘connDB. Execute “INSERT INTO SalesManager (Employeeld, FirstName, Sumame, 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 \d Method - ind or Locate a specific Record Find Method (ADO) searches for a row, in a set of records, basis specified criteria, Syntax: Find (Cr SearchDirection, Star} SkipRows, Criteria specifies the criteria for finding the record. This argument is rquired whle ll athers 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 O 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 net found the position ofthe 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 ofthe record pointer is at BOF ie. before the first record inthe 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 racord 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.all Tables SI seemanacer |“ Employeeld - FirstName ~ [Surname - | JoinDate in 32.J0hn mason 7/26/2008 Perea a 1B Tracy Murray 7/16/2011 1) Pemonmance: rape 2iim Devis 3/11/2009 z 35 David kelly 4/24/2010 55 Sam Green 10/3/2012 Image 7 ‘Sub automateAccessADO_6() ‘Using the Find method to find or locate a specific record(s). "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 stMyPath As String, sttDBName As String, sttDB As String ‘instantiate an ADO object using Dim wit the New keywor Dim adoRecSet As New ADODB Recordset Dim connDB As New ADODB.Connection “THE CONNECTION OBJECT ‘stiDBName = "SalesReport.acedb" ThisWorkbook Path trMyPath & "\" & strDBName ‘Connect to a data source: ‘For pre - MS Access 2007, mab 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 sourc & strOB "USE FIND METHOD ‘Refer Image 7 to view the SalesManager Table in MS Access file "SalesReport acca" “open Recordset ie. SalesManagor Table: ‘adoReeSet. Open Source:="SalesManager’, ActiveConnection: LockType:=adLockOptimistic sonnDB, CursorType:=adOpenkeyset, ‘note that only one criteria is supported by the ADO Find method: ‘adoRecSet.Find "EmployeelD > 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 eriteria of EmployesiD > 8: Note that the J* indicates all words starting withthe 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 1d:12; Jim Davis, Employee ld:21 MsgBox adoReoSot Fields('FirstName") & "" & adoRecSet,Fields("Sumame") & ", Employee Id" & adoRecSet. Fields("Employeela") isiet End It ‘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 "EmployeelD > 8", , adSearchForward ‘OR skip 1 row: ‘adoRecSet.Find "EmployeelD > 8", 1, adSearchFonward Loop "2 records are found MsgBox "Records found: * & i ‘close the objects ‘connDB. Close“destroy the variables Sot adoRecSet = Nothing Sot connDB = Nothing End Sub ADO Filter Property to FilterRecords Filter database table records, using the Filter Property (ADO) on a recordset. Use a fiter 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, forthe LIKE operator. Example 7: Filter Records using the ADO Filter Property. efor 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. ‘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 lst ‘DIM STATEMENTS Dim strMyPath As String, strDBName As String, strOB 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 ‘stiDBName = "SalesReport acedb" strMyPath = ThisWorkbook.Path ‘tsDB = 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.¢.0". For Access 2007 ( acedb databaso) use the ACE Provider: "Microsoft, ACE. OLEDB. 12.0", ‘The ACE Provider can be used for both the Access mda & .accdb files. ‘connDB. Open ConnectionString:="Provider = Microsoft. ACE OLEDB. 12.0; data sourc aston ‘FILTER RECORDS ‘Refer Image 7 to view the SalesManager Table in MS Access file "SalesRepor. acca", used for below filter criteria. ‘Place the filter BEFORE opening the recordset. “You can use wild cards asterisk (*) and percent (2), provided they are the last character in the string, for the LIKE operator. "Examples of 5 valid fiter criteria have been given below. "FirstName = ‘Jim’ OR EmployaelD > 3¢ FirstName LIKE J" Kate Jim’ AND EmployeeID > 18) OR (Sumame = 'Green’ AND EmployeeID > 35)" adoRecSet Filter = "(FirstName = ‘Jim) OR (Sumame = ‘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 Filler = "(FirstName = 'Sam)) AND (FirstName im’ OR Employeel0 > 36)" ‘adoRecSet Open Source:="SalesManager’, ActiveConnection:=connDB, CursorType:=adOpenkeyset LockType:=adLockOptimisticDo While Not adoRecSet. EOF MsgBox adoRecSet Fields(‘FirstName") & " * & adoRecSet Fields("Sumame") & *, Employee id" & ‘adoRecSet Fielas("Employeeld”) isiet “on a match being found, the found record becomes the the current row position, and because the search starts {rom the current row, yau must mave to the next record to find the next match: adoRecSet MoveNext Loop MsgBox "Records found: * & i ‘close the objects ‘connDB, Close ‘destroy the variables. Sot 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. efor to Imagos 8a & 8b, as mentioned in the code. = seesmanacer | cmployeeld ~ FirstName ~ Surname - JoinDate - i 18 Tracy Murray 7/16/2011 a 22 John Mason 7/24/2008 EE Performance: Table | 21 sim Davis 3/11/2009, | 56 Sam Green 10/3/2012 i 35 David Kelly 1/24/2010 image 83 | tmployeeld - [FirstName - Sumeme » JoinDate + [Telephone -| City. «zipcode + 7 stones I 18 Trecy Murrey 7/16/2012 | 12 John Mason 7/24/2008 421-1234567 New York 10453 SB Pcttopecntc ase a 21 Jim Davis 3/11/2008 | 56 Sam. Green 10/3/2012 I 35 David Kelly 1/24/2010 Image ab ‘Sub automateAccossADO_8() ‘Using ADO with SQL Statements to: Add, Delete and Modify Columns in an Existing Table; Add and Delete Constraints on Columns. "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, sttDBName As String, strDB As String Dim strSQL As String ‘instantiate an ADO object using Dim with the New keywor Dim adoRecSet As New ADODB Recordset Dim connDB As New ADODB.Connection "THE CONNECTION OBJECT ‘stiDBName ‘alesReport.accdb* ThisWorkbook Path trMyPath & °\" & strDBName ‘Connect to @ 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 sourc & strOB 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 anew column at the end of the existing columns - in below code we are adding 3 columns named City & ZipCode. \LTER TABLE SelesManager ADD COLUMN Telephone char(15), COLUMN City Text(30), COLUMN. ZipCode char(8) ‘modify/enter column values after opening recordset - refer Image 8b which shows table after entering values as below: strSQL = "SELECT * FROM SalesManager WHERE Employeeld = 12° ‘adoRecSet, Open Source:=strSQL, ActiveConnection:=connDB, CursorTyps LockType:=adLock Optimistic sdOpenDynamic, With adoRecSet Fielas("Telephone Fielas(‘City” Fields("ZipCode” Update End With 421-1234567" ‘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: stiSQL = "ALTER TABLE SalesManager ALTER COLUMN ZipCode Long" ‘connDB. Execute CommandText:=strSQL. ‘delete the City & ZipCode columns in the SalesManager Table: stiSQL = "ALTER TABLE SalesManager DROP COLUMN City, ZipCode" ‘connDB, Execute CommandText:=strSQL. "ADD AND DELETE CONSTRAINTS ON COLUMNS ‘add a PRIMARY KEY CONSTRAINT named 'pk_El' for the column ‘EmployeeID’ of the 'SalesManager table: stiSQL = "ALTER TABLE SalesManager ADD CONSTRAINT pk_E! PRIMARY KEY (EmployeelD)" ‘connDB. Execute CommandText:=strSQL. “drop the existing PRIMARY KEY CONSTRAINT named ‘pk_EI' from the ‘SalesManager table sliSQL = "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 stiSQL. ‘connDB. Execute CommandTex isting CONSTRAINT named ‘un_FN' from the ’SalesManager table: TER TABLE SalesManager DROP CONSTRAINT un_FN" SOL, ‘close the objects cconnDB. Close “destroy the variables ‘Set adoRecSet = Nothing ‘Set connDB = Nothing End Sub Prev (fexcellindex php?option=com_content&vie 56 microsoft-access-ado-library&calid= 1038 ltemid=582)Next > lexcelindex.php?option=com_content&view=artclo&id=346:import-export-data-rom-accoss-to-excelLusing- ado&cati 84) (038ltemi A.new generation = ofmobility.is. helping our business PRIMacINE © 2015 Giobaliconnect.com (hitp:/iwww.globaliconnect.com/excel_new/) All Right Reserved, Back to Top