Per VBScript und SQL auf Access-DB zugreifen

Option Explicit

Const dbPath = "F:\Test\Test.mdb"  

Const adModeReadWrite = 3

Const adUseClient = 3
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim dbCon, dbRec, Data, i

'Main Begin  

   'Datenbank öffnen  
    Call OpenRecordset("SELECT * FROM [Tabelle1]")  
    
   'Datensatz  
    Data = Array("Dummy AutoWert", "feld 2b", "feld 3b", "feld 4b", "feld 5b", "feld 6b")  

    With dbRec 'Datensatz Data in Feld2 bis Feld6 hinzufügen (Feld1 = AutoWert)  
       .AddNew
        For i = 1 To .Fields.Count - 1:  .Fields(i) = Data(i):  Next
       .Update
    End With

    With dbRec 'Alle Datensätze nacheinander auslesen  
      .Sort = "Feld2"  
      .MoveFirst
        Do Until .EOF
            For i = 0 To .Fields.Count - 1:  Data(i) = .Fields(i):  Next
            MsgBox Join(Data, ";")  
           .MoveNext
        Loop
    End With

    With dbRec 'Den Eintrag 'feld 3c' in Feld3 suchen  
        .MoveFirst
        .Find = "feld3 = 'feld 3c'"  
         If .EOF Then
            MsgBox "Nicht gefunden."  
         Else
            MsgBox "Gefunden Zeile: " & .AbsolutePosition  
         End If
    End With
         
   'Alternativ direkte SQL-Anweisungen ausführen  
    dbCon.Execute "INSERT INTO Tabelle1 (Feld2) VALUES ('Hallo')"  
    dbCon.Execute "DELETE FROM Tabelle1 WHERE Feld2 = 'Hallo'"  
    dbCon.Execute "SELECT Tabelle1.* FROM Tabelle1 ORDER BY Tabelle1.feld2;"  

   'Datenbank und Recordset schließen  
    dbRec.Close:   dbCon.Close

   'WScript.Quit  
    
'Main End  


Private Sub OpenRecordset(ByRef dbSql)
    
    Set dbCon = CreateObject("ADODB.Connection")  
    Set dbRec = CreateObject("ADODB.Recordset")  
    
    With dbCon 'Verbindung zur Datenbank herstellen  
        .Mode = adModeReadWrite
        .CursorLocation = adUseClient
        .Provider = "Microsoft.Jet.OLEDB.4.0"  '*.mdb  
       '.Provider = "Microsoft.ACE.OLEDB.12.0" '*.accdb ACCESS 2007  
        .Properties("Data Source") = dbPath  
        .Properties("Persist Security Info") = False  
        .Open
    End With
    
    With dbRec 'Einen Recordset für die Datensätze erstellen  
        .Source = dbSql
        .ActiveConnection = dbCon
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
End Sub

With dbRec 'Datensatz hinzufügen   
     .AddNew
     .Fields("Feld1") = "ab"  
     .Fields("Feld2") = "xy"  
     ..........
    .Update
End With

Pos = dbRec.AbsolutePosition 'Zeilen-Position lesen/setzen  
Cnt = dbRec.RecordCount 'Anzahl Datensätze  

    .AddNew