Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    113

    Unanswered: Using Excel and Access

    Is it possible to connect to an access database through Excel using VBA? If so, how would you do that? Do you need to have installed access to extract data from the database?

    Thanks in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I usually just "link" the excel file into MSAccess and design code around the linked excel file.

    If you want to do it the other way, there's some nifty code in the codebank to populate excel sheets based on an MSAccess table/query and vba code. This way is often done to produce excel graphs (which can also easily be done via an embedded excel graph into an MSAccess report.) You may be able to do it as a mailmerge type way or another. I honesly don't know if there's a way to "Get Linked Table" type command in Excel (perhaps 2007 version.) I'd question the need to do this and why if you could explain further.

    Not sure on your last question regarding needing MSAccess. I'm assuming you have MSAccess. If you don't.....I'm not sure but I was confused with that remark.
    Last edited by pkstormy; 09-16-09 at 04:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Inside Excel you can write a function ("macro" in Excel terminology) that will open a recordset and use this recordset to populate a sheet. Ex:
    Code:
    Function ImportAccessTable(MDBFile As String, Tablename As String)
    
        Dim dbe As DAO.DBEngine
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim TableRows As Variant
        Dim i As Integer
        Dim j As Integer
        
        Set dbe = New DAO.DBEngine
        Set dbs = dbe.OpenDatabase(MDBFile)
        Set rst = dbs.OpenRecordset(Tablename, dbOpenSnapshot)
        With rst
            Do Until .EOF
                TableRows = .GetRows
                For i = 0 To UBound(TableRows)
                    Worksheets(1).Cells(j + 1, i + 1).Value = TableRows(i, 0)
                Next i
                j = j + 1
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        dbs.Close
        Set dbs = Nothing
        Set dbe = Nothing
            
    End Function
    You can then call this function, for instance like this:
    Code:
    ImportAccessTable "C:\Documents and Settings\SinnDHo\My documents\Access\Invoicing\Version_3\Invoicing.mdb", "Customers"
    This is only a short example: I'm not a specialist of Excel and there probably are better ways to perform this kind of operation.

    Other than that, you can create an instance of Excel in a module in Access and work with this instance:
    Code:
        Dim AppXL As Excel.Application
        Set AppXL = CreateObject("Excel.Application")
        etc...
    Have a nice day!

  4. #4
    Join Date
    Nov 2005
    Posts
    113
    Thank you very much, i'm so impressed by your effort. My strange question about Access is due to that not all computers in a network have Access installed and i wonder if it still is possible to get the data from the databas through a macro in Excel.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Yes it is possible to use this kind of function, even if Access is not installed on every computer, provided the file daoxxx.dll (dao360.dll for DAO version 3.6) is present on the computer where the macro is to be executed (it's normally the case).

    When you create the macro in Visual Basic Editor, click on Tools in the menu, then select References and check Microsoft DAO 3.6 Object Library (can be DAO 3.51 or another depending of which version of MS Office is installed).
    Have a nice day!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Chalk another one up for Sinndho!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2005
    Posts
    113
    Just wanted to say thanks and that it works excellent to connect to and access database through Excel without having access installed.

    This was what I did:

    Code:
    Sub GetMyData()
    
    Dim dbs As Database
    Dim i As Integer
    Dim strSQL As String
    
    i = 1
    
    Set dbs = DBEngine.Workspaces(0).OpenDatabase("H:\Dagbok\Dagbok_1_07_Standard_Testversion.mdb")
    strSQL = "SELECT * FROM tblLogg"
    
    Set Rst = dbs.OpenRecordset(strSQL)
    
       With Rst
          Do While Not .EOF
             Range("A" & i).Value = Rst.Fritext
                i = i + 1
                .MoveNext
          Loop
       End With
    End Sub

Posting Permissions

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