[OleDB] Datenanbindung zu MS Access

' Connection String für MS Access
Dim sConnectionString = sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)

DB_Conn.Open()

...

DB_Conn.Close()
DB_Conn.Dispose()
DB_Conn = Nothing

[ADODB] Datenzugriff

Dim DBRecordset As New ADODB.Recordset

DBRecordset.Open("Gebaeude", DBConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

oder

With DBRecordset
     .ActiveConnection = DBConnection
     .CursorLocation = ADODB.CursorLocationEnum.adUseClient
     .LockType = ADODB.LockTypeEnum.adLockOptimistic
     .Open("SELECT ID, Bezeichnung FROM Gebaeude")
End With
  • allg. Zugriff auf Feldelemente
Dim h As Integer
h = DBRecordset.Fields!ID.Value 'unsauber
h = DBRecordset.Fields.Item(0).Value ' per Index
h = DBRecordset.Fields("ID").Value 'Spaltenname
  • Daten per Schleife aus dem DBRecordset auslesen
While (Not DBRecordset.EOF)
   sXMLResult = DBRecordset.Fields("ID").Value
   DBRecordset.MoveNext()
End While

oder
   
For i = 0 To DBRecordset.RecordCount - 1
   sXMLResult = DBRecordset.Fields("ID").Value
   DBRecordset.MoveNext()
Next i
  • und das Schließen des DBRecordset nicht vergessen
DBRecordset.Close()

[ADODB] Datenanbindung zu MS Access

  • ADODB Klassen zum Projekt hinzufügen durch Klick im Projektbaum->Verweise->Verweis hinzufügen …->.Net->adodb wählen
  • Connection herstellen:
Dim DBConnection As New ADODB.Connection

DBConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & OpenFileDialog.FileName)
...
DBConnection.Close()

oder

With DBConnection
    .Mode = ADODB.ConnectModeEnum.adModeShareDenyNone
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & sDBFileName
    .Open()
End With