Consulting

View RSS Feed

ExcelAndAccess

Should you integrate and automate Microsoft Excel and Access?

Rate this Entry
If you are like the majority of users of Vbaexpress.Com, you work with Microsoft Excel. At any moment in time there are far more people in the Excel forum on this site than all other forums combined, and for good reason, the world runs on Microsoft Excel. Excel is the most common and most used desktop productivity tool.



Click image for larger version. 

Name:	vbaexpress-integration-automation-spreadsheet.jpg 
Views:	82 
Size:	20.4 KB 
ID:	19125


At least half the people in the Excel forum are here to learn. They may want to know what a Vlookup is and how they are to best use it, or they are here to learn how to extract online data via Excel VBA. It does not really matter what they are here to learn, there is an amazing group of dedicated individuals answering questions and sharing the knowledge they have acquired over the years/decades. Many of those experts are some of the sharpest minds in the Microsoft applications. Some are Microsoft MVPs, some are authors, many run websites and the list goes on and on. The nice thing is they are here and they have helped millions over the years.

Many of the people that work in Microsoft Excel do so at the professional level, meaning they use it to provide themselves an income. That does not necessarily mean that they are full-time Excel contractors, trainers, or authors. They may work in a finance department as a financial analyst. They make work for Microsoft on the Excel team, writing help files, or they may work for an online retailer. What it does mean is that they do work with Excel on a regular basis in some capacity to do the work they are paid for.

The days where a financial analyst works strictly with Excel and not with any of the other Microsoft data driven applications are more and more becoming a thing of the past. Why is that? Well, one reason is what people in the field call BIG data. The amount of data that people work with has increased over the years, as you would probably have expected. So for many of us that work in Excel that means Excel is housing more data than ever before. True, Excel will now allow you to have over one million rows, but how many of us are able to get Excel to work properly with half that? If you have 500,000 rows, 100 columns, and each cell having complex formulas what happens to Excel when you try to open it, when you try to update it, and when you want it to calculate? If you have not tried it I will tell you what happens, you wait and you wait. And if you are lucky, after a whole lot of waiting you have a workbook that did what was asked. But should it be that way? Not at all, and that is the reason for this post, to show you that there is a better way, a way taken by millions of people around the globe. That way is to integrate Microsoft Excel with Microsoft Access.


Click here to read about our Integrating Excel and Access solutions.


Many of those that integrate Excel and Access use Excel VBA Code that is similar to the code below.

sample-vba-excel-and-access-vbaexpress.txt

This was provided by one of our members out of the UK.
Attribute VB_Name = "mSample"
Option Explicit
Public adoConn As ADODB.Connection
Public Sub sRunAll()
Dim sDBName As String
Dim sFilter1 As String
Dim rOutputRange As Range
sDBName = Range("rDBName").Value
sFilter1 = Range("rFilter").Value
Set rOutputRange = Range("rDataHere").Cells
Call SbReturnDatafromDB(sDBName, sFilter1, rOutputRange)
End Sub
Public Sub sOpenGenericConnection(sDBName As String)
Dim sConnector As String
sConnector = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDBName
If adoConn Is Nothing Then
Set adoConn = CreateObject("ADODB.connection")
With adoConn
.CursorLocation = adUseClient
.ConnectionTimeout = 0
.ConnectionString = sConnector
.Open
End With
Else
If adoConn.State = adStateClosed Then
adoConn.Open
End If
End If
End Sub
Public Sub SbReturnDatafromDB(sDBName As String, sFilter1 As String, rOutputRange As Range)
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim j As Long
On Error GoTo errout
strSQL = "Select * from [tblNames] where [NameType] = '" & sFilter1 & "'"
Call sOpenGenericConnection(sDBName)
Set adoRS = New ADODB.Recordset
With adoRS
.ActiveConnection = adoConn
.Open strSQL
If Not .EOF Then
For j = 0 To .Fields.Count - 1
rOutputRange(j + 1, 1).Value = .Fields(j).Name
Next j
rOutputRange(2, 1).CopyFromRecordset adoRS
End If
.Close
End With
adoConn.Close
errout:
Set adoRS = Nothing
Set adoConn = Nothing
End Sub
Tags: None Add / Edit Tags
Categories
Uncategorized

Comments

  1. snb's Avatar
    I definitely would write this thus:

    Sub Access_query_snb() 
        c00="G:\OF\fiets.mdb" '   Access file
        c01 ="Q_test" ' Querytable
         
        With CreateObject("ADODB.recordset") 
            .Open "SELECT * FROM " & c01, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & c00 
            For j = 0 To .Fields.Count - 1 
                c02 = c02 & "|" & .Fields(j).Name 
            Next 
             
            sheet1.Cells(1).Resize(, .Fields.Count) =Split(Mid(c02, 2), "|") 
            sheet1.Cells(2, 1).CopyFromRecordset .DataSource 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007