Pulling in data from Access in excel (VBA)

jerredjohnson

New Member
Joined
Mar 7, 2006
Messages
22
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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

New Member
Joined
Mar 7, 2006
Messages
22
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

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,238
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top