Querying Access data in Excel Spreadsheet

[Livid_Enema]

I have created a workbook which both imports data from an access database and exports data to same.

Some of the data I need to access is dependent on parameters which the user will change in excel e.g. I need to extract PeriodID (ID for a month end date) based on a date in a speified cell in excel.

MS Query is a very handy way to achieve this as the wizards automate almost everything. However, this workbook will be shared on a common drive on a server (as will the database).

Initial testing suggests that the connection and queries I'm making on my machine do not work on other people's machines.

What is the best way to achieve the queries in excel which query data from an Access Database, but will work on all machines accessing the sheet from a shared drive? Is there any way I can avoid writing complex VBA code for this? I have an awful lot of specific queries like the one I mentioned above to perform.

Thanks in advance
Liam

 

[DCrake]

You may be better served if you move the spreadsheet to the server and recreate the links to the Access database whilst there. This is because other users may not have the same mapping as you.

David

 

[Livid_Enema]

Hi David

Thanks for the suggestion. I moved both the database and the spreadsheet to the shared drive on the server before I made a connection and queries. I saved the DNS connection in the same folder as the db and spreadsheet on the drive before making the connection and queries also and I based the queries on this connection, not the default one in the local My Documents folder.

This doesn't seem to solve the problem. I have a feeling (hope!) if I get the connection defined correctly I might be able to use MS query to do all of my queries.

Any further help is greatly appreciated.

Thanks
Liam

 

[Livid_Enema]

I have solved the problem! I copied the database and spreadsheet onto the server again, remade my connections and did some queries (all using the import data option on the data menu). I have tried it on a few other machines on the network and it appears to be working as it should

I think the problem lay with security settings on the other machines. If anyone else has this problem, ensure the database, spreadsheet and data source are all stored on the server. Also ensure that all users of the spreadsheet change their security settings to allow your data source, automatic refreshing and macros if necessary.

P.S. Sorry for the double post.

 

[DCrake]

Glad to see we have another satisfied customer. Call back anytime