Multiple SQL Databases for an Access ADP 2

[trainman319]

Here is an interesting problem.

I have a situation where I need to allow the user to connect an Access ADP to identical SQL Databases on different servers and I can not hard code the server path.

Since any data stored in a table in the ADP is actually stored on the server, it can't be accessed until the ADP is connected.

I thought of writing a regular mdb which would contain the user various server paths, but how do I: 1) pass the 'path string' to the ADP to use when its opens and 2) how do I open the ADP using code contained in the mdb?

I am open to any and all suggestions.

 

[Steve101]

Here's some code which you can adapt and use to dynamically alter the connection. Note that originates from TT (author J. Fisher); credits provided in the initial comments, though I cant recall the associated thread.

I also use command line parameters on a shortcut to configure the connection server and database arguments.

Function ChangeConnection(sServerName As String, _
                          sLoginName As String, _
                          sPassword As String, _
                          bTrustedYn As Boolean, _
                          sDatabase As String) As Boolean
'--------------------------------------------------------
'Compliments JFisher of tek-tips.
'Makes a connection to a Server and database.
'--------------------------------------------------------
  Dim sBaseConnect As String
  Dim bSuccessYn As Boolean
  
  On Error GoTo ChangeConnection_Err
  bSuccessYn = True
  If sServerName & "" = "" Then
    'Use the Connection dialog
    DoCmd.RunCommand acCmdConnection
    GoTo ChangeConnection_Exit
  End If
  'Build the BaseConnectionString
  sBaseConnect = "PROVIDER=SQLOLEDB.1;"
  If bTrustedYn Then
    sBaseConnect = sBaseConnect _
                 & "INTEGRATED SECURITY=SSPI;" _
                 & "PERSIST SECURITY INFO=FALSE;"
  End If
  If sDatabase & "" <> "" Then
    sBaseConnect = sBaseConnect _
                 & "INITIAL CATALOG=" & sDatabase & ";"
  Else
    sBaseConnect = sBaseConnect & "INITIAL CATALOG=master;"
  End If
  sBaseConnect = sBaseConnect _
               & "DATA SOURCE=" & sServerName
  'Make the connection
  If bTrustedYn Then
    CurrentProject.OpenConnection sBaseConnect
  Else
    CurrentProject.OpenConnection sBaseConnect, sLoginName & "", sPassword & ""
  End If
  
ChangeConnection_Exit:
  On Error Resume Next
  ChangeConnection = bSuccessYn
  Exit Function
  
ChangeConnection_Err:
  bSuccessYn = False
  MsgBox "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description, _
         vbOKOnly, Err.Source & " - ChangeConnection"
  Resume ChangeConnection_Exit
End Function

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[trainman319]

Hi Steve,

This function will fit the bill. JFisher of tek-tips wrote a slick piece of code.

However, you mentioned that you used command line parameters on a shortcut to load the function. Could you give me a simple example of how that is done? I rarely use command line parameters.

Thanks

Neil

 

[Steve101]

Neil,

The target for your shortcut should look something like this:
[tt]
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" C:\YourPath\YourApplication.adp /cmd YourServer,YourDatabase
[/tt]
Then in your application, you use the command() function to retrieve the /cmd string, and the split function to separate the string components, then use them in the ChangeConnection() function; eg.

CommandLineParms = Split(Command(), ",")
sServer = CommandLineParms(0)
sDatabase = CommandLineParms(1)

and so on. Of course with some slightly more sophisticated code you can make the command line options more flexible; eg. change the order, or make them optional, but generally, what is supplied is adequate

Note from the above shortcut example, that the full name of the executable is required; you cannot just put:
[tt]
C:\YourPath\YourApplication.adp /cmd YourServer,YourDatabase
[/tt]
as generally the association here will not detect the /cmd argument.

Hope this works for you,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[trainman319]

Awesome! I tried it and it worked perfectly.

And the really cool part is that I now have one more tool for the ever growing arsenal.

Thank You Steve!

 

[Steve101]

Another thing that this solution is useful for is for separating a development and production environment. I use one shortcut for development against a test database; another for production which links to a production database. Works great, with NO chance of messing up production data.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ginoitalo]

A few similar questions to add to this great thread:

1.
Can you 'allow password saving' when using the CurrentProject.OpenConnection method to connect to SQL via an ADP ?

2.
Having an ADP/ADE to SQL Server, the database Login Screen appears when the database is opened. I never want to save the password that the USER enters. What I'd like to do is replace that login screen with my own.
How can I completely disable it from appearing ?

 

[Steve101]

I simply 'allow password saving' by using the File, Connection menu option available on the adp application in design mode, and responding appropriately in the dialogue box which appears. What you enter here is persistent, and whilst encoding the password in the application, prevents the password prompt from appearing each time the application is started.

I'm unsure of your second question. Are you trying to use your own login/password as opposed to SQL. Not sure if this helps, but I identify users according to their Windows operating system login, then simply use a 'trusted' connection between the application and the database.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ginoitalo]

Hi Steve,

Is there a way to 'allow password saving' when you change the database via code with the currentproject.openconnection method is what I was getting at.

a trusted connection is the way to go, but not if your users are mobile and out of the domain controller's reach.

"Are you trying to use your own login/password as opposed to SQL"

--Exactly

 

[Steve101]

ginoitalo,

If you inspect the code posted in my first thread, you'll find reference to login / password parameters. Well providing these were set up appropriately in SQL Server, nothing to stop you from hard coding this into the above routine. I'm not sure how you would prevent the initial connection from being established when the database is opened??

It strikes me though that you do need to provide an initially valid connection, just to get the application through the initial connection stage.

Let us know how you travel,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)