Access - Tabellen - Spalten - auslesen!

Aus einer wählbaren Accessdatei sollen die Tabellen- und Spaltennamen ausgelesen werden. Code im Link ist "Early Binding" - deshalb unten nochmal mit "Late Binding" - also ohne Verweise.

Access - Tabellen - Spalten - auslesen...

Option Explicit
Public Sub Test()
Dim objCatalog As Object
Dim objConn As Object
Dim objTable As Object
Dim intCount As Integer
Dim varFile As Variant
Dim lngCount As Long
On Error GoTo Fin
lngCount = 1
Set objConn = CreateObject("ADODB.Connection")
Set objCatalog = CreateObject("ADOX.Catalog")
varFile = Application.GetOpenFilename("Access,*.mdb")
If varFile = False Then Exit Sub
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & varFile
objCatalog.ActiveConnection = objConn
For Each objTable In objCatalog.Tables
If objTable.Type = "TABLE" Then
Cells(lngCount, intCount + 1).Value = objTable.Name
Cells(lngCount, intCount + 1).Font.Bold = True
For intCount = 0 To objTable.Columns.Count - 1
Cells(lngCount, intCount + 2).Value = _
objTable.Columns.Item(intCount).Name
Next intCount
lngCount = lngCount + 1
intCount = 0
End If
Next objTable
Fin:
If Err.Number <> 0 Then MsgBox "Error " & _
Err.Number & " (" & Err.Description & ")"
If Not objConn Is Nothing Then
If objConn.State = 1 Then objConn.Close
End If
Set objCatalog = Nothing
Set objConn = Nothing
Columns.AutoFit
End Sub

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...