Connection Information with Access Project and SQL Server backend 3

[arimatan]

Hi there,

I am using a MSDE database with an Access Project (*.adp) as frontend. The database runs on various computers so that I have to change the connection properties in the File/Connection menu in Access. But I need to make this change in VBA code defining connections I need, too.
How can I get information about the connection (especially the Server Name) I selected in the File/Connection menu of the Access project with VBA?

any help or hints would be greatly appreciated

 

[OnTheFly]

I need to do this also but could not find the VBA code to do it. I went the easy way and used a macro to call the menu item Connection. Not real fancy but at least it allows my users to change the default connection log-in so their user name comes up at opening.

The other way would be to not directly connect to the database until you need to and doing that through and ADO connection in the code. You can then pop up a form that collects the user information when the project opens and connect to the database with that information.

Don't know if this helps or not but thought I would try.

 

[jfischer]

Here's some sample code that I use to change connections in ADPs. You can adapt this code as needed.

Function ChangeConnection(sServerName As String, _
                          sLoginName As String, _
                          sPassword As String, _
                          bTrustedYn As Boolean, _
                          sDatabase As String) As Boolean
  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 & &quot;&quot; <> &quot;&quot; Then
    sBaseConnect = sBaseConnect _
                 & &quot;INITIAL CATALOG=&quot; & sDatabase & &quot;;&quot;
  Else
    sBaseConnect = sBaseConnect & &quot;INITIAL CATALOG=master;&quot;
  End If
  sBaseConnect = sBaseConnect _
               & &quot;DATA SOURCE=&quot; & sServerName
  'Make the connection
  If bTrustedYn Then
    CurrentProject.OpenConnection sBaseConnect
  Else
    CurrentProject.OpenConnection sBaseConnect, sLoginName & &quot;&quot;, sPassword & &quot;&quot;
  End If
  
ChangeConnection_Exit:
  On Error Resume Next
  ChangeConnection = bSuccessYn
  Exit Function
  
ChangeConnection_Err:
  bSuccessYn = False
  MsgBox &quot;Error #&quot; & Err.Number & vbCrLf & vbCrLf & Err.Description, _
         vbOKOnly, Err.Source & &quot; - ChangeConnection&quot;
  Resume ChangeConnection_Exit
End Function

 

[OnTheFly]

Thanks so much. This will be a great help for me anyway. I hope arimatan can use it also.

Thanks again,

OnTheFly

 

[arimatan]

Thank you for your hint. it helped me a lot

 

[Steve101]

Good one jFisher. Works like a charm. A star for your efforts,





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