10. VBA: recordsets

Met VBA kan je formulieren en rapporten manipuleren, maar je kan ook de data zelf rechtstreeks aanspreken.

10.1. DAO

DAO, Data Access Objects is de handigste manier om vanuit VBA databases aan te spreken, zowel die van Access zelf als ODBC (Open DataBase Connectivity) databanken en ISAM (Indexed Sequential Access Method) bestanden met gegevens, bv. uit Excel.

Object DBEngine is het hoogste niveau van het DAO-model. Het bevat twee collecties: Workspaces en Errors.

Per Workspace kan je één of meer databases aanspreken, met of zonder gebruikersnaam en paswoord.
VBA creërt automatisch de standaard workspace; gebruik je die, dan wordt de instructie: Set werkruimte = DBEngine(0).

Je kan acties op gegevens één voor één uitvoeren of verzamelen in een transactie.
Een transactie is een verzameling acties op gegevens uit één of meer databases.

  1. De acties in de transactie worden in de cache onthouden, niet rechtstreeks op schijf opgeslagen.
  2. Alle acties in een transactie zijn ofwel geslaagd ofwel allemaal mislukt. In het eerste geval schrijf je de gegevens op schijf met CommitTrans, in het tweede geval maak je ze allemaal ongedaan met Rollback.

Voorbeeld van een transactie:

Public Sub Overschrijving()
  Dim werkruimte As DAO.Workspace
  Dim dbIntern As DAO.Database 'databank waar het geld afgaat
  Dim dbExtern As DAO.Database 'databank waar het geld naartoe gaat
  Set werkruimte = DBEngine(0)
  Set dbIntern = CurrentDb
  Set dbExtern = werkruimte.OpenDatabase("C:\pad\dbNaam.accdb")
  On Error GoTo transferError

  werkruimte.BeginTrans
  dbIntern.Execute "UPDATE tabel enz.", dbFailOnError 'SQL-instructie om geld van rekening te halen
  dbExtern.Execute "INSERT INTO tabel enz.", dbFailOnError 'SQL-instructie om geld op andere rekening te zetten
  werkruimte.CommitTrans dbForceOSFlush 'met Flush, zodat het zeker op dat moment op schijf gezet wordt

transferExit: 'opruimen
  werkruimte.Close
  Set dbIntern = Nothing
  Set dbExtern = Nothing
  Set werkruimte = Nothing
  Exit Sub

transferError: 'transactie annuleren
  werkruimte.Rollback
  Resume transferExit
End Sub

10.2. Werken met recordsets

Om de records van een tabel of query aan te spreken, gebruik je in VBA-code een recordset.
Een voorbeeld:
Dim db As DAO.Database
Dim rsTabel As DAO.Recordset
Dim rsQuery As DAO.Recordset
Set db = CurrentDb
Set rsTabel = db.OpenRecordset("tabelNaam", dbOpenTable)
Set rsQuery = db.OpenRecordset("queryNaam", dbOpenDynaset)

Je kan ook in de code een SQL-query opstellen en die als dynamische recordset gebruiken:
Dim rsSQL As DAO.Recordset
Dim sqltekst As String
sqltekst = "SELECT enz"
Set rsSQL = db.OpenRecordset(sqltekst, dbOpenDynaset)

Je kan probleemloos verscheidene recordsets tegelijk open hebben; Access behandelt ze volledig onafhankelijk van elkaar.

In een recordset zal je geregeld de cursor aan het juiste record moeten zetten (rs = de recordset):

Houd er rekening mee dat je binnen de recordset moet blijven bewegen, anders krijg je een fout:
If Not rs.BOF Then rs.MovePrevious 'BOF = begin of file
If Not rs.EOF Then rs.MoveNext 'EOF = end of file

Ook moet je dikwijls het aantal records kennen, bv. om een lus erover te laten gaan. Dan moet de set eerst volledig geladen zijn:
Dim aantal As Integer
rs.MoveLast
aantal = rs.RecordCount

10.3. Gegevens aanpassen

Wil je records toevoegen, aanpassen of wissen, dan gebruik je telkens dezelfde methode: zeg wat je wil doen, pas aan en update de recordset.

Stel dat we werken met een heel eenvoudige tabel KLANT: klantID (autonummering), voornaam, familienaam.

Een record toevoegen:
Set rs = db.OpenRecordset("KLANT", dbOpenDynaset)
With rs
  .AddNew
  !voornaam = "An"
  !familienaam = "Claes"
  .Update
End With

Een record aanpassen:
Set rs = db.OpenRecordset("KLANT", dbOpenDynaset)
With rs
  .FindFirst "[klantID] = 23"
  If Not .NoMatch Then
    .Edit
    !voornaam = "Annie"
    .Update
  Else
    MsgBox "record niet gevonden"
  End If
End With

Een record wissen:
Set rs = db.OpenRecordset("KLANT", dbOpenDynaset)
With rs
  .FindFirst "[klantID] = 23"
  If Not .NoMatch Then
    .Delete
  Else
    MsgBox "record niet gevonden"
  End If
End With

In plaats van rs!veldnaam kan je ook rs.Fields("veldnaam") gebruiken.