Connecting Excel to Access General Question

Hi,
I have an Excel document the connects to Access database. I make this connection in 11 subroutines in various modules. The code below is an example of one of the connections.

1. The code works great for me in my test environment. I moved the Excel document and the Access file to my live environment mimicking my test environment setup. I have read/write access to all the folders in my live environment, and I updated the Access file path in my code. When I open the Excel document, it works great. However, when I had someone who does not have read/write access to the folder containing the Access database, the connection fails. Is my issue the security rights of the user? If so, how can I make this work. I have about 30 users who will be using the Excel budget document, and I don't want them to have access to the Access database.

2. The code for connecting to the Access database is written in 11 different subroutines. Is there a way that I can do the connection so that I don't have to update 11 different subroutines Access database file path?

Please Login or Register  to view this content.

After doing more searches, I figured out a workaround the security issue for the folder the Access database is located in. I moved the Access database to a folder that all my users have read/write Access and then I password protected the Access database. It was essential that I did the following.
1. In the Access File Options > Client Settings > select "Use legacy encryption".
2. In the VBA code put the password.

Please Login or Register  to view this content.

Still hoping to get advice about my second question.

Thanks,
Karen

Ad2.
1. Declare a global variable at the beginning of the standard module:

Please Login or Register  to view this content.

2. Delete assigning a value to this variable and remove the declarations in all procedures.

Depending on your needs, you will comment on one and uncomment the other public declaration.

Artik

Hi Artik,

I neglected to say that the subroutines are in different modules. I did this just to help me stay organized while developing the code.

Need to verify what I am supposed to do based on your advice.
1. Get all the subroutines located in the same module.
2. At the beginning of this module declare the public global variable.
3. Remove the declaration in each individual subroutine in the module.

Thanks for your time.
Karen

Sorry. It was late and I made mistakes in my speech.
1. You declare a public constant in one of the standard modules. I devote a separate module to this type of declaration:

Please Login or Register  to view this content.

2. Corrected procedure below (fragment). I have marked the lines of code to be removed:

Please Login or Register  to view this content.

3.If in any procedure you have used a variable name other than ACCESSFILE, you must correct it to the name of this public constant.

Artik

@ Artik

Thanks, much better way than the way I was doing it.

Hi Artik,

Thank you for the great directions. It WORKED!! The simplicity of solution is much appreciated. This saves so much time and reduces the chance of human error on my part.

Karen