Tag Archives: VBA

Uncategorized

VBA: Read DB Schema incl. all Tables and Columns from Access mdb Database

Leave a comment

Paste the following VBA Code to a new Module in an empty Excel Worksheet and execute the public sub after editing the File Path and the Password in the Connection String.

Following Reference is required:
Microsoft Office 1[4567].0 Access database engine Object Library

Public Sub ReadAllTablesAndColumnsFromAccessDatabase()
    Dim db As Database, td As TableDef
    Dim fld As Field
    
    Set db = OpenDatabase("C:\Temp\yourAccessDB.mdb", False, True, "MS Access;PWD=PAAAAAASSSSWOOOORRRRDDD")
    
    ' Trap for any errors.
On Error Resume Next
    Dim idxTable As Integer
    Dim rowIdx As Integer
    
    rowIdx = 1
    Dim targetRange As Range
    ' *** Zieltabelle festlegen...
    Set targetRange = ThisWorkbook.Worksheets(1).Cells(1, 1)
    
    For idxTable = 0 To db.TableDefs.Count - 1
        Set td = db.TableDefs(idxTable)
        If Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
            For Each fld In td.Fields
                targetRange.Cells(rowIdx + 1, 1).Value = td.Name
                targetRange.Cells(rowIdx + 1, 2).Value = fld.Name
                targetRange.Cells(rowIdx + 1, 3).Value = FieldType(fld.Type)
                targetRange.Cells(rowIdx + 1, 4).Value = fld.Size
                targetRange.Cells(rowIdx + 1, 5).Value = fld.Attributes
                targetRange.Cells(rowIdx + 1, 6).Value = fld.Properties("description")

                rowIdx = rowIdx + 1
            Next fld
            
        End If
    Next idxTable

    db.Close
End Sub

Function FieldType(intType As Integer) As String
    
    Select Case intType
        Case dbBoolean
            FieldType = "dbBoolean"    '1
        Case dbByte
            FieldType = "dbByte"       '2
        Case dbInteger
            FieldType = "dbInteger"    '3
        Case dbLong
            FieldType = "dbLong"       '4
        Case dbCurrency
            FieldType = "dbCurrency"   '5
        Case dbSingle
            FieldType = "dbSingle"     '6
        Case dbDouble
            FieldType = "dbDouble"     '7
        Case dbDate
            FieldType = "dbDate"       '8
        Case dbBinary
            FieldType = "dbBinary"     '9
        Case dbText
            FieldType = "dbText"       '10
        Case dbLongBinary
            FieldType = "dbLongBinary" '11
        Case dbMemo
            FieldType = "dbMemo"       '12
        Case dbGUID
            FieldType = "dbGUID"       '15
    End Select

End Function
Business Intelligence, Performance, Uncategorized

Fastest Way to retrieve Data from Data Warehouse in Excel via VBA Macro

Leave a comment

Use a code similar to this one in VBA:

Include following References: Microsoft ActiveX Data Objects 6.* Library

Be sure to return values as “datetime” instead of “date” or “datetime2” to get recognized as a date value in Excel.

Public Sub GetDwhData()
    Dim wksTarget As Worksheet: Set wksTarget = Worksheets(1)
    Dim cellAddressStart As String: cellAddressStart = "B2"
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    On Error GoTo ErrHandling
    
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=tcp:localhost,1344;Initial Catalog=DWH;Integrated Security=SSPI"
    objMyConn.Open

    strSQL = "select TOP 1000 * from dbo.Datasource"

    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL

    'Copy Data to Excel...
    wksTarget.Range(cellAddressStart).CopyFromRecordset (objMyRecordset)
        
        
    objMyRecordset.Close
    objMyConn.Close
    
    wksTarget.Calculate 'Formeln neu berechnen
        
ErrHandling:
    Application.Calculation = xlCalculationSemiautomatic
    Application.ScreenUpdating = True
    
End Sub