mike g said:
i've been playing around with msaccess for a while, but many of the things
i
want to do require more discrete control, and it would be much safer to
the
database if i could touch the data inside vba code instead of using forms
that are linked to tables.
i have not yet found any straight forward samples of simple
open-read-add-change-delete-close operations.
There are a couple of ways to do this. You can update tables by executing
SQL statements that insert, update, and delete records, or by working via a
Recordset object. You can read data by using a Recordset object, or by
using the built-in domain aggregate functions such as DLookup, DSum, DMax,
and the like.
Recordset objects are made available by either the Data Access Objects (DAO)
library -- or its Access 2007 equivalent -- or by the ActiveX Data Object
library (ADO). To use them effectively, you'll want to set a reference to
the appropriate library. You do that in the VB Editor by clicking Tools ->
References..., locating the library in the list, and putting a check mark in
the box next to it. Depending on your version of Access, you will probably
find a reference already set to one of these libraries. I recommend that
you use DAO instead of ADO, if you're using a regular Access database (.mdb
or .accdb file) to hold your data.
You can execute SQL statements by using either the built-in RunSQL method,
which generally wants you to confirm everything you do, or by using the DAO
Execute method. Here are some examples:
Dim strSQL As String
strSQL = _
"UPDATE MyTable SET MyField = 1234 WHERE IDField = 1"
' Use RunSQL to execute the statement.
' This will result in a user confirmation dialog.
DoCmd.RunSQL strSQL
' Use DAO to execute the statement -- no dialog.
CurrentDb.Execute strSQL, dbFailOnError
Working with recordsets is more object-oriented, but usually less efficient
than just executing SQL statements. Here are some examples:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
' Read a value from a specific record, ID known in advance:
Set rst = db.OpenRecordset( _
"SELECT MyField FROM MyTable WHERE IDField = 1")
MsgBox "The value is" & rst!MyField
rst.Close
' Open a recordset and loop through records, editing some of them:
Set rst = db.OpenRecordset("MyTable")
With rst
Do Until .EOF
' Let's update those records where MyField is
' evenly divisible by 5
If !MyField Mod 5 = 0 Then
.Edit
!MyField = !MyField / 5
.Update
End If
.MoveNext
Loop
' Let's find a particular record.
.FindFirst "IDField = 123"
' Now let's delete the record we found.
.Delete
' And now, let's add a new record.
.AddNew
!MyField = 10101
!SomeTextField = "foo"
' Note: I'm assuming primary key is autonumber,
' so we don't need to -- and can't -- set it.
.Update
' Always close the recordsets you open.
.Close
End With
