1

I have what is likely a common scenario. I have an MS-Access application split into front-end and back-end files. I want to set up users with an Excel spreadsheet that connects to data on the back-end. But rather than have everyone hit the back-end directly, I'd like to connect to the back-end through linked tables in the front-end, which resides in a folder under %APPDATA% on each user's machine/environment.

Is it possible then to specify the Data Source parameter in the OLEDB for Jet connection string using shell variables (or whatever they are), for example something like %APPDATA%/path/to/app.mdb ? I cannot seem to get this syntax to work. I don't want to have to resort to using Excel VBA if I can help it.

Or, alternatively, can you reassure me that it's not a problem for multiple users to connect (read-only) directly to the back-end file from Excel?

Many thanks,
Eric

Eric G
  • 1,282
  • 8
  • 18
  • 1
    What do you think you'd be accomplishing by going through the front end? You're still going to be opening the back end data, just via an intermediate step. I don't know it for a fact, but I wouldn't be surprised if once the linked table is resolved, Jet/ACE just goes directly to the back end. This looks like premature optimization to me. – David-W-Fenton May 30 '11 at 22:57
  • That's helpful to know. I'll just connect directly to the backend then. – Eric G May 31 '11 at 00:29

0 Answers0