30. juni 2004 - 17:16Der er
7 kommentarer og 1 løsning
Skifte connection til SQL server i kode
Findes der en metode til at skifte connection til en SQL server i en Access .ADP applikation ? Det er den connection man refererer til med CurrentProject.Connection jeg er interesseret i. Jeg ville gerne have det lavet så jeg f.eks. kan skifte mellem test og drift data i min applikation. Det må godt være sådan at skiftet sker fra en lille formular hvor man kan angive server, database, bruger og password. Når man har skiftet db og evt. lukker applikationen ned, skal de nye indstillinger selvfølgelig huskes til næste opstart af applikationen.
Er der nogen der har set sådan funktionalitet et sted på nettet, er jeg meget interesseret i at høre om det.
Hi Hugo I can give you some information as to how it can be done but I cant give you the code. What we use is a bit overkill and actually fills quite a bit.
What is important is this.
CurrentProject.BaseConnectionString contains the connection string which you need to alter. The string contains a substring something like this.
DATA SOURCE=ThisIsYourOldServer
You need to replace this sub string with
DATA SOURCE=ThisIsYourNewServer
Then you need to close the current connection using
CurrentProject.CloseConnection
Now open the connection again with the new BaseConnectionString
we do it when the application starts up using autoexec macro to call the code. We then look in the registry to see which server to use. We have chosen this way so that users dont have access to it. For developers it sjust a case of double clicking a .reg file containing the connection information. Then running autoexec again to read the registry and change the connection.
OK - but what if the users wants to test something? I have a lot of users that would like to have a sandbox to play around with. I could make it so that only administrators have access to setup the 2 connections neede and then give the users access to a couple of menuitems that allows them to switch.
So you could just add a simple form with a combo box containing the possible servers. What you have to be carefull of is that the users forgets to change back to production dB, and then YOU have a problem later when they complain that the data they created earlier cant be found (becuase its in the test dB :o) )
Terry> if I could find some code for retrieving the list of servers and the list of db's on the selected one then I would be almost there.
Do you say that users are stupid ? :-) I have a plan regarding this issue. I have a hidden form already and I plan to put some timerfunction on this that displays a popup every now and then when the user is working on the test db. Then they can't say that they haven't been warned :-)
Sounds like a good idea. I would have thought that the database's had the same names in test and prod. But if you need to alter this too then you need to replace
Haven't got a finished app. yet, but I think it is time to close and since Terry was the only one answering........................
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
