Hard-Coded Data Question

[marksmithy69]

I am just looking for some advice on the best way to alleviate a problem I am having with my database. I just started a new project at work which uses an existing MS Access database, which contains hard-coded references to a server, in a few of the modules. My job is to get rid of any hard coded references. This database exist in 3 different environments. There is the development environment, where all code changes are made, the test environment where the application is tested, and the production environment, which is what the client uses. In each of these environments, the hard-coded references are a little different, because the server names are different in each environment. My question is...what would be the best way to change the code, so that the code is identical across each environment? I want to be able to promote all code changes from dev, and not have to change the hard-coded paths each time I promote code, which is what is currently being done. Maybe using global variables? Any advice would be greatly appreciated. Thank you.

Mark

 

[LittleSmudge]

My approach to that is to stick all of those \\Server\Path details in a table within the database itself

You can then look up the info based on what state the database is in ( Maybe even get the database to check it's own current \Server\Path location to decide itself whether it is in Development Mode, Test Mode or Production Use )

To get db's current Full Path Name use
Dim db As Database
Set db = currentdb()
MsgBox currentdb.name



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[willir]

marksmithy69

I have been thinking of invoking a solution but have not done it yet, Consequently, this is hypothetical only.

Store the connection information in a local Access table.

With VBA, and this will depend greatly on whether you are using DAO or ADO, retrieve the information to access the external tables.

Depending on your needs, this code could run upon startup or at the module level. Of course, the one module should be ultimately responsible for running the connection to the external servers.

This accomplishes two things...
The table with the connection is the only place that requries the update.
The one connection module with VBA code is used.

This also adds the flexibility where you can have different connection strings depending on needs in the table. For example, connect to an ODBC Oracle database at this location, an Access database at that location.

I am sorry that I do not have specifics. I tend to prioritize my work on what needs to be done. I think BillPowers and JeremyNYC, and some of the well established members, will have more experience at this type of project than myself.

Richard

 

[marksmithy69]

By using currentdb.name, I retrieve the name of the database, with it's associated path, such as C:\db1.mdb. Is there a way to retrieve the path name to the database, using the appropriate server name instead of the server drive letter? I want \\server1.db1.mdb instead. Thanks again.