Accessing MS Access DB via Excel

[John P]

I have been told that it is possible to build an Excel F/E to allow access to a MS Access database for users that do not have MS Access. Can someone please point me in the direction of information relating to this topic and, if possible, advise what I need to do to create such a F/E. Thank you

 

[Corticus]

Hi John P,

I have a few questions first...

How would you have Excel and not Access? The two are sold and installed together.

What is F/E?

To answer your question, by using 'Data|Get External Data' in Excel, you can retrieve data from tables in an .mdb file (not to mention many other db programs), even if you don't have Access, but I can't see why this would ever be.

edit: that's a good reason, Ziggy, thanks

HTH,

 

[Ziggy]

Access is only available in the Professional version of "Office", "Standard" edition does not have it.


I suspect if this is a network situation that you could set up "Linked" tables to Access, use an update query to update the tables the changes will be available in Excel. But I think you were hoping to have the users control Access some how, I've never thought about it that way but it sounds possible.

I'll ask around



Ziggy

 

[A_Pitts]

I just wandered in here for some info on the same topic, but I think I have a more specific situation in mind.
I have a quote log with about 15 fields. Right now we keep it as an Excel spreadsheet and each user opens it, adds or modifies, and then save and close. Of course, only one person can work with it at a time. (Sharing would be too risky for our bunch.) It wasn't a problem when there were 2 or 3 of us using it, but now there are 10 people.
Can I have a copy of Access running on a single computer on the network with each user having Excel to add new records and modify old ones? Access would referee so that users could change a record without tying up the entire database.
And if it can work, can you direct me to any good sources for doing it. I learned Excel because I needed it and this may be the reason to learn Access.
Thanks,
A_Pitts

 

[Ziggy]

A pitts,

Are you also saying your users can't get "Access"?

I haven't done it this way, but I don't see why it would not work. I am assuming each user is maintaining their own data list. So 10 sheets could be linked to access, and create an append/update queries to pull the data into one table.

Just an Idea,

Ziggy

See also http://www.access-programmers.co.uk/forums/
it is a great source for Access help I jump between the 2 sites(Mr.excel) all the time.