• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MS Query Data Connection & Coworkers' Different Drive Letters

cmkarnes

Member
Hi, I've got an Excel Workbook containing two different data connections using MS Query which taps into an Access database on a shared network drive. The queries are working fine. Both the workbook & Access database are on the same shared drive. I am the person who constructed the workbook and data connections. The people here in the office have different drive letters for accessing the shared drive as many of us have access to other shared drives as well.

I set up the MS Query data connections from my computer. The problem is if someone else goes in the workbook and needs to refresh the data, it won't recognize the request because the data connections only recognize my drive letter assigned to the drive. They can look at the tables, but that's it.

I don't know enough about SQL to know if the Connection String can be edited to allow for this? For the Access database - I had no part in how it was designed or set up, but all of us are able to access it and make changes as needed to records.

The connection string currently looks like the below. The "J" is my shared drive letter. I am not able to download the file due to sensitivity of the contents. Thanks so much.

DSN=MS Access Database;DBQ=J:\XXXX DATABASE\Final Database\XXX Database Application - FE.accdb;DefaultDir=J:\XXX DATABASE\Final Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
 
Hi,

Instead of drive letter you give the server address, when you map the share drive to your system you give the server address like \\server\sharefolder give directly that address in the code it will work fine irrespective of drive letter.

Regards
Abdul Matheen
 
Thank you for the reply. I'm not sure why I did not think of this - I recall now doing this with an Access database a couple of years ago when splitting the front and back end. Thankfully, this is an easy solution! Have a great day! Chris
 
Hi, am sorry to trouble you again. So, I've got the address for the shared drive, but am having an issue getting it to work. If I am in Excel, and click on "Connections" and bring up Connection Properties, in the Connection String block I assume I can edit from there? Also, due to my poor knowledge of SQL statements, it is not clear to me how I would construct it. Would you be able to give me an idea?

Here's the connection as it stands right now:
DSN=MS Access Database;DBQ=J:\XXXX DATABASE\Final Database\XXX Database Application - FE.accdb;DefaultDir=J:\XXX DATABASE\Final Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

Here's the address of the shared drive (I've made up fake items here for the purpose of posting - \\r03vdm3icow\hgwirmgfs05\SHARE\XXX Master Folder\ (and after this I'm lost). It keeps saying invalid address, when I know it is valid, so it's me no doubt.) I don't understand how to incorporate into the current connection.

Thanks so much - C
 
Hi cmkarnes,

I'm a little late to this thread, so you might have already found a solution. But, have you looked at the command text box in the connection properties window, definition tab?

If you scroll all the way to the bottom of that box, there should be a FROM statement. By default, it should say "FROM J:\XXXX DATABASE..." If you replace the "J:" there as well, it should work.

Hope that helps,
David
 
Hi, I eventually did figure it out. I went in to where you mentioned, and redid the connection string with our shared drive's address. I could not get it to work if I kept all the alternate info that shows at the bottom. I deleted all that. I also found it was very fussy with the punctuation and such and had to play with it a bit, but then, I finally got it working and the other users have no issues now. Thanks! cmk
 
Back
Top