Pulling in data from Access in excel (VBA)

[jerredjohnson]

I am trying to understand how to query an access database by using VBA. Does anyone know any tutorials or advice? I just want to be able to return from a table a list of customers based on a salesmen.

please help/give guidence!

Thanks,

Jerred

 

[Dan Waterloo]

You might need VBA and you might not. You can use MS Query from within Excel to extract data from an MS Access database.

Select the cell where you want the data extract to start. Then select
/Data/Import External Data/New Database Query. Pick MS Access Database and then navigate to the database you want to use. The query wizard guides you through a process to pick your columns, sort order, etc. There's also a way to set up a paramter to the query at a later point so you can extract the data just for a particular saleperson. But it really helps if you know SQL to start with.

 

[jerredjohnson]

I understand how to pull data that way, but I am looking to make a dynamic report instead of making a report for each salesmen (there are over 50 salesmen)

 

[Dan Waterloo]

By parameter I meant a dynamic variable you could change that would allow you to retrieve a different subset of data.

Once you have the query working you can add a line to your SQL WHERE clause that would be something like

WHERE Salesperson = [Enter Salesperson ID:]

The square brackets casue MS Query to ask you for this ID while it's running. You can also link the parameter to a particular cell on the worksheet and have the dataset update whenever the value in the cell is modified.

 

[jerredjohnson]

EXACTLY what i was looking for, thanks a bunch