Extracting Data From Access into Excel

[phendrena]

Hi there,

Following on from my thread in the excel forum : http://www.vbaexpress.com/forum/showthread.php?t=22628
I now come to doing data manipulation in Access.

I had an excel workbook setup that acted as a database. From this we pulled various reports. Now that I have setup and started to use Access as the database (but still using Excel as the front-end for data input) I now need to know how to setup these reports in Excel pulling the data from Access.

I know that i could setup the reports in Access but we do not want the end user to touch access at all.

The reports that i setup in excel used SUMPRODUCT formulas to get the correct data. Using VBA and an excel Userform how can i setup these reports now?

I've attached an example of how the report used to look like and a form showing what i would like the report to look like.
(I've posted this in the access forum as i'm pulling the data from access....)

Thanks,

EDIT : The Access database (CanxData) can be found in the linked thread.

[CreganTur]

The easiest way to get the data out of Access would be by using either ADO or DAO connections. I generally prefer ADO, but it's really personal preference, but all of my examples will be in ADO.

This example shows how to create an ADO connection to an external Access database. It then uses a SQL statement to create a recordset from that database:
Sub OpenADORst()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Data Source=C:\Acc07_ByExample\Northwind.mdb"
End With
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM Employees", conn
Debug.Print rst.Fields.count
.Close
End With
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub

To get the value of the fields in the recordset you will use the Recordset Bang(!) FieldName method. If you want the EmpID field you write: rst!EmpID.Value (there are other methods but this is the most explicit method, which is why I use it most often).

Using this you can query the Access DB for the records you want,and then pull the values from the recordset fields and assign their values to the objects on your UserForm.

I know this is an overview, but it will point you in the right direction. Let us know what other questions you have.

HTH:thumb

[phendrena]

Hi Randy,

To be honest, this really my first dabble with Access and i've never used SQL. Getting the data I need isn't going to be as straightforward as pulling the values and sticking them into the userform, it's going to need to do various calculations before sticking the data into the userform.

When it was a straightforward excel setup i used SUMPRODUCT formula to get the results, for example :
SUMPRODUCT(--(CanxData!S2:S3000=H18),--(CanxData!D2:D3000=E4),--(CanxData!F2:F3000="Yes"),--(CanxData!E2:E3000="Mid-Term"))

This will then give me a total based on the criteria, in this case H18 = Month, E4 = Username and 'Mid-Term' is the cancellation period.

How to actually set this kind of thing up in VBA extracting the data from Access, i'm clueless tbh and would appreciate any help.

Thanks,

[OBP]

phendrena, I can understand your (and your collegues) reluctance to embrace Access, when things seem so easy in Excel, which you are obviously very used to and pretty good at.
However Access is much better at controlling data input than Excel and is actually much more User friendly (NOT Programmer Friendly).
What you actually need to do is to generate Queries to get the data that you want in the Format that you want it before trying SQL or VBA to put it in to Excel.
The Query Wizard is very easy to use and with a little help from Randy or others you should very quickly be able to Extract your data. You can also view a Query in SQL mode so that at the same time you can learn the SQL as well.
You will also be quite amazed at what Access can do in terms of simple Arithmetic and data Collation using the built in Queries and PivotTable Forms.