December 10, 2014 at 1:41 pm
I have a .accdb file connected to a SQL server database (using linked tables - DSNless connection). I have Dev, Test, and Production environments. I would like to put some sort of indicator (text, background color...) on all of my forms which tells the user what environment the .accdb file is connected to. How do I do that?
December 10, 2014 at 5:18 pm
So are you using the same front-end accdb database and changing the connection string on start? If so, you would need to enumerate all of the form objects to change the background color - conditional formatting cannot be used on form objects, only selected objects contained on forms. Another technique involves adding a string in a table that indicates the environment, and displaying that in a text box on all major forms. That could be done with an unbound text box, and some code on the OnLoad event to populate it based in the environment table. Depending on the complexity of your front-end, that can be a substantial task. Another strategy is to load all of your forms into a master form as subforms, but that involves some complex code in Access.
We usually have a separate Development copy, as the front-end typically changes each time we put out test and production versions, as more often than not the changes occur in the front-end rather than in SQL Server. Hope that gives you some ideas.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 11, 2014 at 7:56 am
Thanks for the response Wendell.
The answer to your first question is; I'd like to... I think. Right now I have separate Access file for DEV, TEST, and PROD environments. The challenge I was experiencing, and looking for a way to solve, elegantly, amounts to version management and migrating changes though the environments. I'm a Access developer out of necessity not by profession (although I think I might be discovering my calling :-)). I don't have very well developed version and environment management practices, yet...
I've actually created a form (password protected) with buttons which pass a sever/instance & database name to a function which links the tables to the selected environment. It works fine but I don't know if it actually makes sense from an operational perspective since structural differences in the DB may not align with the functionality in the Access file.
I already had a table called "Version" in my database with a float value in a single column which I set manually and then check for on the load of my main menu. If the version number is different than what the form is expecting the user is driven to an internal web page to download the proper version of the Access file. I added a computed column to that table with the value of @@SERVERNAME.
I created a subroutine which can be called from the OnLoad event of each form which sets the caption of a label and also changes the backcolor of the form. Seems to work quite nicely, but, since this required a DB change to add the computed column to my Version table, I can't actually test this until I migrate my DB changes through the environments. If there were a way to call the @@SERVERNAME SQL variable directly rather than from a table via DLOOKUP then I wouldn't have to change the structure of my Version table.
December 12, 2014 at 11:17 am
One of the things about Access, that you will discover if you haven't already, is that the recommended practice is to load the front-end Access file on the workstation, rather than trying to run multiple people in a single copy of the front-end on the server. So that adds another issue to the deployment process. There are several solutions, including a batch file to simply download the .mdb/accdb file each time the database is started. A more sophisticated product is available from FMS inc. or you can roll your own like we have.
Another issue you are likely to encounter is that if you want to have users connecting to the Dev and Test environments, each workstation will need an ODBC driver for each back-end. Once those are setup, they usually don't require any maintenance - until somebody gets a new workstation, or you get a new user. Where are you in the development process - still testing, or do you have users in the production environment? 20 years of experience with this environment has shown that you will probably make 5 or 10 changes to the front-end for every change you make in the SQL Server back-end.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply