Public objConn As New Connection
Public objRs As Recordset
 
'Verbindung zur Datenbank aufbauen
Public Sub OpenConn(FileName As String)
    With objConn
        If .State = adStateOpen Then .Close
        .Provider = "Microsoft.Jet.OLEDB.4.0"			'Access 2000/XP
        .Properties("Data Source") = FileName			'Access 2000/XP
 
	'.Provider = "SQLOLEDB;Data Source=" & strServer & ";" & _
		     "Initial Catalog=" & strDatabase & ";" & _
		     "User ID=" & strUserName & ";" & _
		     "Password=" & strPassword & ";"		'SQL-Server 7.0
 
        .CursorLocation = adUseClient
        .Open 'Connection öffnen
    End With
End Sub
 
'Ab hier nur für Access (analog für SQL-Server Schema anpassen)
'Tabellen auslesen
Public Sub Table()
    'Recordset über Tabellen-Schema erstellen
    Set objRs = objConn.OpenSchema(adSchemaTables)
 
    'Tabellenauflistung clearen
    MeineForm.lstTable.Clear
 
    'Recordset durchlaufen und alle Tabellen auflisten
    Do While Not objRs.EOF
        If objRs("TABLE_TYPE") = "TABLE" Then
            MeineForm.lstTable.AddItem objRs("TABLE_NAME")
        End If
        objRs.MoveNext
    Loop
    objRs.Close
End Sub
 
'Tabellendaten auslesen
Public Sub OpenTable(TableName As String) 'Tabelle öffnen
    With objRs
        Set objRs.ActiveConnection = objConn 'aktive Verbindung zuweisen
        .ActiveConnection = objConn
        If .State = adStateOpen Then .Close
        .LockType = adLockOptimistic
        .Source = "Select * From " & TableName 'ganze Tabelle
        On Error Resume Next
        .Open 'Tabelle öffnen
    End With
End Sub
 
'Verbindung schließen
Public Sub CloseConn()
    If objRs.State = adStateOpen Then
        objRs.Close
        Set objRs = Nothing
    End If
    If objConn.State = adStateOpen Then
        objConn.Close
        Set objConn = Nothing
    End If
End Sub

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. (Brian W. Kernighan)