ADO VBA Programming in Access ADO DB in

ADO VBA Programming in Access ADO DB in Access VBA © Walter Milner 2005 Slide: 1

Why its not simple 1 • MS Access has a 'built-in' database engine called Jet – which you might use • But you might instead use a separate data server • Like MS SQLServer, Oracle or My. SQL • These work slightly differently ADO DB in Access VBA © Walter Milner 2005 Slide: 2

Why its not simple 2 • The actual data (and server) might be on the same machine that Access is running on • But it might not ADO DB in Access VBA © Walter Milner 2005 Slide: 3

Why its not simple 3 • The actual data (and server) might not be a relational database • Could be a web page or spreadsheet ADO DB in Access VBA © Walter Milner 2005 Slide: 4

So • ADO = Active Data Objects is a single object model to cover all cases • therefore pretty intricate (but can be simple) • Here we only cover – – running from VBA in Access – using a local Access database ADO DB in Access VBA © Walter Milner 2005 Slide: 5

Fundamental objects • Connection • Recordset ADO DB in Access VBA © Walter Milner 2005 Slide: 6

Connection • Represents a single session with a data provider. The sequence is – • Set up connection • Open connection • Do things with the data • Close the connection ADO DB in Access VBA © Walter Milner 2005 Slide: 7

Recordset • • A recordset is just a set of records (rows) Open a recordset (through a connection) Do something with the rows Close the recordset ADO DB in Access VBA © Walter Milner 2005 Slide: 8

Simple example • An Access database has a table called my. Table and a key field called ID • The following code (in a button on a form) goes through the table and displays all teh IDs ADO DB in Access VBA © Walter Milner 2005 Slide: 9

Simple example : part 1 'declare conn to be a Connection Dim conn As ADODB. Connection ' make a connection object Set conn = New ADODB. Connection ' specify what kind of data provider it is conn. Provider = "Microsoft. Jet. OLEDB. 4. 0" ' open the connection on one database conn. Open "c: /walter/ass 21. mdb" ' declare a recordset Dim my. Table. RS As ADODB. Recordset ' make one Set my. Table. RS = New ADODB. Recordset ' open it using a table in the database, and the connection my. Table. RS. Open "my. Table", conn, ad. Open. Dynamic, ad. Lock. Pessimistic ADO DB in Access VBA © Walter Milner 2005 Slide: 10

Simple example : 2 ' go to start of recordset my. Table. RS. Move. First ' until we reach the end. . Do Until my. Table. RS. EOF ' display the ID field in current row Msg. Box (my. Table. RS. Fields("ID")) ' move next row my. Table. RS. Move. Next Loop 'close the recordset my. Table. RS. Close Set my. Table. RS. Active. Connection = Nothing ' and the connection conn. Close Set conn = Nothing ADO DB in Access VBA © Walter Milner 2005 Slide: 11

Reading a table • Make a database and a table with a numeric field and a text field. Put in a few rows. • Write a routine like the above example, to total the numeric field and display it with a Msg. Box ADO DB in Access VBA © Walter Milner 2005 Slide: 12

Find Method (from Microsoft Help file. . ) Searches a Recordset for the row that satisfies the specified criteria. Optionally, the direction of the search, starting row, and offset from the starting row may be specified. If the criteria is met, the current row position is set on the found record; otherwise, the position is set to the end (or start) of the Recordset. (works matching one field only) ADO DB in Access VBA © Walter Milner 2005 Slide: 13

Finding a record - example Dim conn As ADODB. Connection Dim my. Table. RS As ADODB. Recordset Set conn = New ADODB. Connection Find a row with a certain key Set my. Table. RS = New ADODB. Recordset field value and display other conn. Provider = "Microsoft. Jet. OLEDB. 4. 0" field conn. Open "c: /walter/ass 21. mdb" my. Table. RS. Open "my. Table", conn, ad. Open. Static, ad. Lock. Optimistic Dim wanted As String Text 5. Set. Focus wanted = Text 5. Text Get required value from a text box my. Table. RS. Find "ID = " & wanted If Not my. Table. RS. EOF Then Label 8. Caption = my. Table. RS. Fields("Name") Else Label 8. Caption = "Not found" End If Do the Find Display result ADO DB in Access VBA © Walter Milner 2005 Slide: 14

Find record exercise • Use the above to find and display values ADO DB in Access VBA © Walter Milner 2005 Slide: 15

Altering data - up. Date Dim conn As ADODB. Connection Dim my. Table. RS As ADODB. Recordset Set conn = New ADODB. Connection Set my. Table. RS = New ADODB. Recordset conn. Provider = "Microsoft. Jet. OLEDB. 4. 0" conn. Open "c: /walter/ass 21. mdb" my. Table. RS. Open "my. Table", conn, ad. Open. Static, ad. Lock. Optimistic my. Table. RS. Move. First Do While Not my. Table. RS. EOF my. Table. RS. Fields("Phone. Number") = my. Table. RS. Fields("Phone. Number") + 1 my. Table. RS. Update my. Table. RS. Move. Next Loop my. Table. RS. Close Set my. Table. RS. Active. Connection = Nothing conn. Close ADO DB in Access VBA © Walter Milner 2005 Slide: 16

Update. Batch my. Table. RS. Open "my. Table", conn, ad. Open. Static, ad. Lock. Optimistic my. Table. RS. Move. First Do While Not my. Table. RS. EOF my. Table. RS. Fields("Phone. Number") = my. Table. RS. Fields("Phone. Number") + 1 my. Table. RS. Move. Next Loop my. Table. RS. Update. Batch ADO DB in Access VBA © Walter Milner 2005 Slide: 17

Practice with update • Try using update as above • Try updatebatch • Combine find with update to change selected records only – – in a loop have a sequence of – find – update ADO DB in Access VBA © Walter Milner 2005 Slide: 18

Inserting new rows. . my. Table. RS. Open "my. Table", conn, ad. Open. Dynamic, ad. Lock. Pessimistic my. Table. RS. Add. New name. Txt. Box. Set. Focus my. Table. RS. Fields("Name") = name. Txt. Box. Text phone. Txt. Box. Set. Focus my. Table. RS. Fields("Phone. Number") = phone. Txt. Box. Text my. Table. RS. Update my. Table. RS. Close. . New record is added at the end of the table In a relational database, record order has no significance Try this out Try using ad. Lock. Read. Only as the lock type ADO DB in Access VBA © Walter Milner 2005 Slide: 19

Deleting records. . IDTxt. Box. Set. Focus my. Table. RS. Find "ID = " & IDTxt. Box. Text If Not my. Table. RS. EOF Then This deletes a row (first one ) whose my. Table. RS. Delete ID field matches text box input my. Table. RS. Update. deletes current row Msg. Box ("Record deleted") after update Else Msg. Box ("No matching record") Try adapting to code to delete all matching records End If my. Table. RS. Close. . ADO DB in Access VBA © Walter Milner 2005 Slide: 20

Using SQL as recordset source my. Table. RS. Open "Select ID, name From my. Table", conn, ad. Open. Dynamic, ad. Lock. Pessimistic Do While Not my. Table. RS. EOF For i = 1 To my. Table. RS. Fields. Count Debug. Print my. Table. RS. Fields(i - 1), Next Debug. Print my. Table. RS. Move. Next Loop ADO DB in Access VBA © Walter Milner 2005 Slide: 21

SQL practice • Use the above approach to debug. print data from 2 JOINed tables ADO DB in Access VBA © Walter Milner 2005 Slide: 22

Command object Dim conn As ADODB. Connection Set conn = New ADODB. Connection conn. Provider = "Microsoft. Jet. OLEDB. 4. 0" conn. Open "c: /walter/ass 21. mdb" Dim my. Command As ADODB. command Set my. Command = New ADODB. command my. Command. Active. Connection = conn my. Command. Text = "Update my. Table set phonenumber=phonenumber + 2" my. Command. Execute conn. Close Set conn = Nothing Here commandtext is SQL update statement No recordset needed Try it ADO DB in Access VBA © Walter Milner 2005 Slide: 23

Command, Recordset and Flex. Grid MS Flex. Grid not standard control Get it by 'More controls' on toolbox ADO DB in Access VBA © Walter Milner 2005 Slide: 24

Flex. Grid 1 – get the data Dim conn As ADODB. Connection Set conn = New ADODB. Connection conn. Provider = "Microsoft. Jet. OLEDB. 4. 0" conn. Open "c: /walter/ass 21. mdb" Dim my. Command As ADODB. command Set my. Command = New ADODB. command my. Command. Active. Connection = conn my. Command. Text = "select * from my. Table" Dim rs As ADODB. Recordset Set rs = my. Command. Execute command returns a recordset ADO DB in Access VBA © Walter Milner 2005 Slide: 25

Flex. Grid 2 – setting it up Dim field. Count As Integer field. Count = rs. Fields. count MSFlex. Grid 1. Cols = field. Count + 1 MSFlex. Grid 1. Allow. User. Resizing = flex. Resize. Columns MSFlex. Grid 1. Rows = 50 For i = 0 To field. Count - 1 MSFlex. Grid 1. Text. Matrix(0, i + 1) = rs. Fields(i). Name Next set number of columns – 1 more than field count put fieldnames into top row ADO DB in Access VBA © Walter Milner 2005 Slide: 26

Flex. Grid 3 – recordset -> grid rs. Move. First count = 1 for each record. . Do While Not rs. EOF put record number at left. MSFlex. Grid 1. Text. Matrix(count, 0) = count for each field in row. For i = 0 To field. Count - 1 MSFlex. Grid 1. Text. Matrix(count, i + 1) = rs. Fields(i) Next place field value in grid count = count + 1 rs. Move. Next Loop rs. Close ADO DB in Access VBA © Walter Milner 2005 Slide: 27

Record. Set object things - cursor • The cursor is the 'current row' • There are different kinds of cursors with different effects • You select the cursor type before opening the recordset ADO DB in Access VBA © Walter Milner 2005 Slide: 28

Cursor types • • • Static. Is snapshot – changes by other users are invisible. ad. Open. Static Forward. Only. Like the above but you can only move forward through rows – more efficient. ad. Open. Forward. Only Dynamic. Changes by others seen, move anywhere. ad. Open. Dynamic Keyset. Like dynamic, but can't see rows added by others. ad. Open. Keyset (but you don't always get this – it depends on the way the recordset is generated) ADO DB in Access VBA © Walter Milner 2005 Slide: 29

Data Locking • Danger – 2 users processing the same data at the same time might over-write each others work • Solution – the first user puts a 'lock' on the data which prevents others using it at the same time ADO DB in Access VBA © Walter Milner 2005 Slide: 30

Types of lock • ad. Lock. Read. Only - you are only reading records so they are not locked • ad. Lock. Pessimistic – record locked when you access it, released when finished • ad. Lock. Optimistic – record only locked when you update it – might go wrong • ad. Lock. Batch. Optimistic - only locked when do batch update ADO DB in Access VBA © Walter Milner 2005 Slide: 31