Specifiying a connection string in ms access forms

[thebionicredneck]

Hi guys,

I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?

Please note, I am a newbie and I know next to nothing about vba. I have seen lots of samples of code concerning issues similar to this, but I don't know where exactly to put the code in, so some simple guidance for an access newcomer on this would be appreciated.


Many thanks

 

[Pat Hartman]

When you link the tables, the location of the BE is stored in the link. If you distribute an .accdb and your users have a retail version of Access, they can use the linked tables manager if necessary to relink the BE. You could also create a form to help them instead of making them use the linked tables manager.

 

[thebionicredneck]

Thanks for the suggestion Pat. Is there a way to do this without the users needing to do it themselves to reduce the risk of errors?

 

[thommo41]

Not sure I understand this, why do you need to re-link the tables? Are you users accessing it from different places, with different paths/drive letters? I run front end/back end databases all the time, the front end can sit wherever, as long as the back end stays consistent?

 

[Galaxiom]

One really simple way to achieve this is to link the backend via a drive mapping letter.

The client then just needs to put the backend wherever it suits and map that drive on all the clients. If the backend gets moved just map the new location to the same letter.

Another way is to store the path somewhere and use code to redefine the Connect property in a loop through the TableDefs collection.

 

[thebionicredneck]

Not sure I understand this, why do you need to re-link the tables? Are you users accessing it from different places, with different paths/drive letters? I run front end/back end databases all the time, the front end can sit wherever, as long as the back end stays consistent?

Hi, the reason I need to re-link the tables is because the solution is not yet live and I was just thinking about having something robust so that when implemented, I can manage the connection paths if the backend ever moves

 

[thommo41]

Gotcha, Glaxiom's suggestion using drive mappings, fixing the path to a drive, and making sure the network drive always points to the correct folder seems the most obvious one. Failing that, his other suggestion is more complicated but will work just the same I'm sure!

 

[Pat Hartman]

It is important that you have an easy way to relink the tables. Even if the users don't need it, YOU do. When you are developing, you should NOT be connected to the production database. You should have your own test version. To help myself with this, I have a field on every form that checks the path to the BE and if the networkID is mine, I make the field visible in red and it says PRODUCTION or in green, it says TEST so I am always consious of what BE I am using.

Mapping drive letters works in many shops but not all. In some cases, there are so many mapped drives that it is not possible to maintain consistancy. In any case, it is better to use the UNC path which will be consistant.

\\Servername\folderpath\databasename

 

[thebionicredneck]

Thanks for all your responses. Using the UNC path seems to be the best option as it ensures it is consistent across the board

 

[Galaxiom]

Mapping drive letters works in many shops but not all. In some cases, there are so many mapped drives that it is not possible to maintain consistancy. In any case, it is better to use the UNC path which will be consistant.

\\Servername\folderpath\databasename

If there are enough letters available and the drive mappings are done though group policy it can be appropriate but I do prefer UNC paths too. I only suggested it as a easy option.

Some bad thngs can happen with mapped drives. Here is a story that helps remind me to always be very careful.

A colleague had juststarted in the job and the archiving system wasn't working on a server after a reboot. He discoverd the drive mapping it required had been lost so went to map it again.

He put in the letter and up popped the path in the history. He was under a lot of pressure from multiple problems in the system he had just taken over. It was probably a reasonable assumption that this was the normal path but unfortunately it was not a good assumption.

The archiving system started working on files required by the system, dutifully writing them to the archive ... and deleting them from the disk.

The system soon went down and it was not very good for his reputation in the new job.