Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: Query in user defined functions (VBA)

    Hello to all.
    I've been googling around to find some code examples of how to query Access from Excel but all i found included the connection to the database. What i was thinking is to connect Excel to Access while opening the worksheet and then build my own functions without having to connect to the database again. Some like this:

    Code:
    Private Sub Workbook_Open()
    'code to connect to the database
    End Sub
    Then, on the code modules i'll write my functions to get the data.
    Does someone know if this is possible? If yes, can someone give a code example? I'm using Access and Excel 2007.
    Thanks a lot in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Depenging on how you intend to use the data from the database, there are several possibilities. Here's one:
    Code:
    Sub ConnectToAccDb()
    
        Dim dbe As DAO.DBEngine
        Dim wsp As DAO.Workspace
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim varData As Variant
        
        Set dbe = New DBEngine
        Set wsp = dbe.Workspaces(0)
        Set dbs = wsp.OpenDatabase("C:\Documents and Settings\SinnDHo\My documents\Access\extdb.mdb")
        Set qdf = dbs.CreateQueryDef
        qdf.Name = ""
        qdf.Sql = "SELECT RowID, Field1, Field1, Status FROM Mytable;"
        Set rst = qdf.OpenRecordset(dbOpenSnapshot)
        varData = rst.GetRows(rst.RecordCount)
        rst.Close
        qdf.Close
        dbs.Close
        wsp.Close
        Set rst = Nothing
        Set qdf = Nothing
        Set dbs = Nothing
        Set wsp = Nothing
        Set dbe = Nothing
            
    End Sub
    Note: You need to create a reference to the Microsoft DAO 3.x Object Library (where x can be 5 or 6) to use this code: in the VBA Editor, open the Tools menu, select References and add the reference to DAO from the list.
    Have a nice day!

  3. #3
    Join Date
    Jun 2007
    Posts
    65
    Hello.
    Sinndho, thank you for your answer. However, as i said on my post, your example code contains the connection to the database. If i want to execute another query i'll need to repeat all the code and change the sql (and other things). What i would like to do is to have a piece of code that connects to the database (just once) and make all the queries i want without having to always repeat the connection code.

    Thank you.
    Octavio

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then you can:
    1. create a class module in your project:
    Code:
    '
    ' Class Module: Cls_DbConnect
    '
    
    Private m_dbe As DAO.DBEngine
    Private m_dbs As DAO.Database
    
    Private Sub Class_Initialize()
    
        Set m_dbe = New DBEngine
    
    End Sub
    
    Private Sub Class_Terminate()
    
        If Not m_dbs Is Nothing Then m_dbs.Close
        Set m_dbs = Nothing
        Set m_dbe = Nothing
        
    End Sub
    
    Public Sub CloseDb()
    
        m_dbs.Close
        Set m_dbs = Nothing
        
    End Sub
    
    Public Property Get Connect() As String
    
        If Not m_dbs Is Nothing Then Connect = m_dbs.Name
        
    End Property
    
    Public Property Let Connect(ByVal DatabaseName As String)
       
        If m_dbs Is Nothing Then
            If Len(Dir(DatabaseName)) > 0 Then
                Set m_dbs = m_dbe.OpenDatabase(DatabaseName)
            Else
                MsgBox DatabaseName & vbNewLine & "was not found.", vbExclamation, "Connect: File not found."
            End If
        Else
            MsgBox "Cls_DbConnect already has an open database:" & vbNewLine & m_dbs.Name, vbInformation, "Connect: Db already open."
        End If
        
    End Property
    
    Public Function QueryDb(ByVal SQL As String, ByVal ReturnsData As Boolean) As Variant
    
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        
        If Not m_dbs Is Nothing Then
            Set qdf = m_dbs.CreateQueryDef
            With qdf
                .Name = ""
                .SQL = SQL
                If ReturnsData = False Then
                    .Execute dbFailOnError
                Else
                    Set rst = .OpenRecordset(dbOpenSnapshot)
                    If Not rst.EOF Then
                        rst.MoveLast
                        rst.MoveFirst
                        QueryDb = rst.GetRows(rst.RecordCount)
                    End If
                    rst.Close
                End If
                .Close
            End With
        Else
            MsgBox "The database is closed." & vbNewLine & "Open the database using the Connect property first.", _
                   vbExclamation, "QueryDb: Database closed."
        End If
        Set rst = Nothing
        Set qdf = Nothing
        
    End Function
    2. declare a public instance of the class Cls_DbConnect:
    Code:
    Public dbConnect As Cls_DbConnect
    3. Open the database by using the Connect property of the class, then use the instance wherever you want:
    Code:
    Sub TestDbConnect()
    
        Dim var As Variant
        Dim strSQL As String
        Dim i As Long, j As Long
        
        Set dbConnect = New Cls_DbConnect
        dbConnect.Connect = "C:\Documents and Settings\rene.francois\Mes documents\Access\extdb.mdb"
        TestSelect
        TestExecute
        TestSelect
        dbConnect.CloseDb
        Set dbConnect = Nothing
        
    End Sub
    
    Sub TestExecute()
    
        Dim strSQL As String
        
        strSQL = "INSERT INTO Mytable ( Field1, Field2, Status ) VALUES ( 'Some text', 123456, False );"
        dbConnect.QueryDb strSQL, False
    
    End Sub
    
    Sub TestSelect()
    
        Dim var As Variant
        Dim strSQL As String
        Dim i As Long, j As Long
        
        strSQL = "SELECT RowID, Field1, Field2, Status FROM Mytable;"
        var = dbConnect.QueryDb(strSQL, True)
        For i = 0 To UBound(var)
            Debug.Print i,
            For j = 0 To UBound(var, 2)
                Debug.Print var(i, j),
            Next j
            Debug.Print
        Next i
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jun 2007
    Posts
    65
    AhAh! Excellent. That's exactly waht i was looking for.
    Thanks a million!

    Octavio

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •