How to use CASE SELECT in VBA Ms Access
Hi
Having worked through the SQL Server as a backend to Ms Access FE I want to make a code that will check the connection strings that is valid from the access FE module, for example if the FIRST string below is wrong then it should check the SECOND string or return connection FAILED result:
(1) sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatab
(2) strConnect = "ODBC;Driver={SQL Server};UID="& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
From the above situation how do I use CASE SELECT , END SELECT in a VBA code? Do not worry about a full code I have a working code intact, its working fine.
I have put the same working VBA code in a module and is call by a macro called autoexe, now what to achieve with suggested selection is that before deployment to some clients I have to collect some as follows:
(1) Computer Number ( for security VBA code purpose)
(2) Computer name ( to used in the connection string above )
The current VBA will continue working silently without the user knowing what is happening, so in short string (1) will have my computer/server details while string (2) while have the new details for the new client computer name (Where the application will be deployed). The reasons for the above suggestion is as follows:
(1) If I compile the application based on my computer connection it will fail to connect the new client computer because the server name will be different , hence the required 2 string above fitted with CASES SELECT
(2) If I create a table within access to allow the users to be selecting the server names I fear that they may mess it up, hence the reason why users should not know what is happening in the background, the VBA should instead do the selection using SELECT CASE
I’m not familiar with CASE SELECT , I still think that is the way to go to achieve what I want. I want to stick to same code I have but adjust a bit with case select.
I know the syntax it should like below:
Select Case Expression
Case Value1
[Action to take when Expression = Value1]
Case Value2
[Action to take when Expression = Value2]
Case ...
Case Else
[Default action when no value matches Expression]
End Select
WHAT DO I PUT ON THIS (Select Case Expression??????????????) that is where my problem is!!!!!!!!
Having worked through the SQL Server as a backend to Ms Access FE I want to make a code that will check the connection strings that is valid from the access FE module, for example if the FIRST string below is wrong then it should check the SECOND string or return connection FAILED result:
(1) sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatab
(2) strConnect = "ODBC;Driver={SQL Server};UID="& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
From the above situation how do I use CASE SELECT , END SELECT in a VBA code? Do not worry about a full code I have a working code intact, its working fine.
I have put the same working VBA code in a module and is call by a macro called autoexe, now what to achieve with suggested selection is that before deployment to some clients I have to collect some as follows:
(1) Computer Number ( for security VBA code purpose)
(2) Computer name ( to used in the connection string above )
The current VBA will continue working silently without the user knowing what is happening, so in short string (1) will have my computer/server details while string (2) while have the new details for the new client computer name (Where the application will be deployed). The reasons for the above suggestion is as follows:
(1) If I compile the application based on my computer connection it will fail to connect the new client computer because the server name will be different , hence the required 2 string above fitted with CASES SELECT
(2) If I create a table within access to allow the users to be selecting the server names I fear that they may mess it up, hence the reason why users should not know what is happening in the background, the VBA should instead do the selection using SELECT CASE
I’m not familiar with CASE SELECT , I still think that is the way to go to achieve what I want. I want to stick to same code I have but adjust a bit with case select.
I know the syntax it should like below:
Select Case Expression
Case Value1
[Action to take when Expression = Value1]
Case Value2
[Action to take when Expression = Value2]
Case ...
Case Else
[Default action when no value matches Expression]
End Select
WHAT DO I PUT ON THIS (Select Case Expression??????????????) that is where my problem is!!!!!!!!
Select Case is used to select from a list of values. I don't think that's what you want to do. If you want to test which connection string works, then you'd want to use a function that does that, and sets the connection string accordingly. I'd use a Database Property to store the valid connection string, and do something like this:
Function CheckMyConnection() As Boolean
Dim fConnGood As Boolean
<check your first connection string here, and set fConnGood=True if it works>
If fConnGood then
'/ Set the database property and exit:
Currentdb.Properties("MyCo
CheckMyConnection = True
Exit Function
Else
<check your second connection string here, and set fConnGood=True if it works>
'/ Set the database property and exit:
Currentdb.Properties("MyCo
CheckMyConnection = True
Exit Function
End If
'/ if we make it here, neither was good, so we return false
CheckMyConnection = False
End Function
You'd call CheckMyConnection as needed, and use Currentdb.Properties("MyCo
You'd need to add the MyConnectionString property to your database, of course. There's several ways to do that; see this article for more information on that:
https://www.experts-exchange.com/articles/29438/Access-Custom-Database-Properties.html
Function CheckMyConnection() As Boolean
Dim fConnGood As Boolean
<check your first connection string here, and set fConnGood=True if it works>
If fConnGood then
'/ Set the database property and exit:
Currentdb.Properties("MyCo
CheckMyConnection = True
Exit Function
Else
<check your second connection string here, and set fConnGood=True if it works>
'/ Set the database property and exit:
Currentdb.Properties("MyCo
CheckMyConnection = True
Exit Function
End If
'/ if we make it here, neither was good, so we return false
CheckMyConnection = False
End Function
You'd call CheckMyConnection as needed, and use Currentdb.Properties("MyCo
You'd need to add the MyConnectionString property to your database, of course. There's several ways to do that; see this article for more information on that:
https://www.experts-exchange.com/articles/29438/Access-Custom-Database-Properties.html
You'd need to add the MyConnectionString property to your database, of course
I have tried to follow the article about adding a property to the connection string, it is not very clear,Mr scott you almost sorted out my problem I'm still stuck on adding the MyConnectionString property to my database?????????
Regards
Chris
I have tried to follow the article about adding a property to the connection string, it is not very clear,Mr scott you almost sorted out my problem I'm still stuck on adding the MyConnectionString property to my database?????????
Regards
Chris
Hi Scott McDaniel (Microsoft Access MVP - EE MVE )
The issue is on the dialogy box What is required to be entered on the follow:
(1) Value
(2) Type
Once the above is done is there anything that is supposed to be done? I have followed the instruction properly but I just want to be clear on this?
Regards
Chris
The issue is on the dialogy box What is required to be entered on the follow:
(1) Value
(2) Type
Once the above is done is there anything that is supposed to be done? I have followed the instruction properly but I just want to be clear on this?
Regards
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What you want to do is probably using
Open in new window