Sofia,

First, I am going to assume that by "macros in Excel", you mean you are
working with Excel VBA code.

There are, generally, two main methods of retrieving data from an Access
database.

One is using a direct query from a worksheet. Once refreshed, this data
would then be available to Excel formulas and macros.

The other method is...generally... to create a Database object in your Excel
VBA code, open your Access db into that object and retrieve the data using
Access VBA code.

Setting Up the Data source:

For the direct query method, you would need to establish a data source that
points to your Access database. You will need to do this using the €œData
Sources (OBDC)€� tool. In Windows XP this is found in Control Panel |
Administrative Tools.

Click on this tool and you will get the €œODBC Data Source Administrator€�
with the User DSN tab showing; click the Add button on the right.

This will call a €˜create new data source dialog. You must first choose
€œMicrosoft Access Driver(*.mbd)€�, then click the €œFinish€� button below the
tab window.

You will then get an €˜ODBC Microsoft Access Setup dialog. In this dialog
you will supply a name for the data source that you will recognize and
description of the data source. This can be simply the name and description
of the database.

Below that you will be able to select the database from which you wish to
retrieve records. Click Select and navigate to the Access database and select
it, then click OK.
The full system address of the selected database will then appear in the
Database box of the €˜ODBC Microsoft Access Setup dialog. Click OK and the
new data source will appear in the User DSN tab window of the €œODBC Data
Source Administrator€�. This data source will be used in the next step. Close
the Data Source Administrator.

Create an Excel worksheet Query against an external database:

The next step is to create a worksheet query in Excel.
To see how it works, create a new empty worksheet in your workbook. In the
clean sheet, click on the top menu: €œData | Import External Data | New
Database Query...€�
This will call up a dialog box from an external program named Microsoft
Query. This is the Choose Data Source dialog box.

In the Databases tab, you will see the new data source that you created.
Select it and, below that, check the €œUse the Query Wizard to create/edit
queries€� box then click OK.

If all goes well, you will see a €œQuery Wizard €“ Choose Columns€� dialog. It
will show all of the Tables and Queries available in the database that you
connected this data source to in the previous steps.

There are two basic ways to go from here.

If your data needs are complex and spanning multiple tables, you can create
an Access query first and treat it as a single table.

If your data is from only one table and your filtering needs are simple,
double click the table name and choose the columns (field names) that you
want to retrieve data from as well as any fields you want to use to filter
the data, and then click next.

You will then see the next dialog in the wizard, the €œQuery Wizard €“ Filter
Data€� dialog.

Sofia, if you are familiar with Access, you probably already know how this
dialog works, you will have seen it while creating Reports in Access. Select
the column or columns on which you want to filter the data and select the
type of filtering and the information key to filter on.

When you are finished with this, click Next to go to the €œQuery Wizard €“
Sort Order€� dialog. This box is also familiar to Access users. Select the
columns to sort by and select Ascending or Descending for up to three
cascading sorts.

At the completion of the sort order dialog, click Next and you will be taken
to the Query Wizard €“ Finish dialog. Your choices here are to return the data
to Excel, look at the data in Microsoft Query, or create€¦well, we will not
worry about OLAP cubes tonight. You can also save the query to a separate
file.

Basically, just return the data to Excel so that you can see what you have
been creating.

The data can then be accessed from your VBA code and placed in text boxes or
from formulas in Excel.

Sofia, it is late tonight. I am an old guy and I need my less-ugly sleep.
Tomorrow evening I will cover the other way to get Access data into Excel,
the VBA route.


" wrote:

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye