Kategorie: Datenbank

' 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

• DBConnection muss vorher geöffnet worden sein (siehe ADODB Datenanbindung zu MS Access)
• DBRecordset öffnen

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 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