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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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