Using Excel as the interface for an Access database

[Guest]

Is there a way where I can store information in Access and use Excel and
create a spreadsheet and be able to pull data from the Access database? If
so, if the information in the database is changed, would it automtically
update the linked excel file?

Thanks,

 

[SusanV]

Why would you want to do this?

 

[Guest]

I have an excel workbook that I have setup that when I pick something from a
drop down box, a field would automatically be pulled. For example when I
chose David from a drop down box, the cell next to it would bring my date of
birth. Now, I have the lists for the drop down boxes on another worksheet
within the file and it is slowing down the program a lot because of all of
the data that I am using. I am looking to see if there is a way that I can
put in excel a formula where it would pull the information from an access
database and not excel and just use excel as the "Face"
Thanks

 

[Guest]

Yeah In excel goto Data > Import External Data > New Database Query
Select Microsoft Access and then the location of your file. Then you will
be able to pull the data.

Once you have the link set up you can only add/update/edit data in Access.

Excel will update the data when you open the excel file but if both access
and excel is open you will have to manually update Excel via Refresh Data.


And

 

[SusanV]

You can certainly do this by setting an Access db as the datasource, but it
seems to me that's kind of like using a sledge hammer to hang a picture in
your condo.

Also keep in mind that puling data from an external source is also going to
take time.

 

[Guest]

Thanks for your help. Now, can I create a drop down box in the excel sheet
that pulls the data from the access database and how do I do it?

THanks again for your help

 

[Guest]

For that I am afraid you will have to seek someone on the excel forum. I
never got into this but had some co-workers that would do something similar
to this by creating an excel query (which would be linked to the Access
table) and use whatever's in the dropdown as an condition.

 

[Guest]

how so i set access db as the data source?

thanks

 

[SusanV]

I'm sure the Excel folks would be better able to help you - generally
speaking I only use Excel for data output from Access. The Help in Excel
(F1) is pretty good too:

<copied from Excel Help>
Ways to retrieve data from an external database

You can retrieve external data from databases in the following ways:

Using Microsoft Query
Using Microsoft Visual Basic
Using Web queries
<snipped>



Good luck!

SusanV

 

[dbahooker]

I disagree.

using Access whenever possible and excel as little as possible.

it's not a sledgehammer.

it's a graceful swan

-Aaron

 

[dbahooker]

you really should

keep the data in access
and
display the data in Access

you can bind a simple query to do things like this; in excel it would
be impossible / impractical to do this.

-Aaron