Server name on Form

  • Thread starter Thread starter Jim Gaudet, MCSE
  • Start date Start date
J

Jim Gaudet, MCSE

We have a small problem. We run multiple copies of the same database on the
same server. And sometimes we accidentally create the ADE file pointing to
the wrong connection. I was hoping that I could have the Server Name and the
Database Name show up somewhere in the form so I could know which database I
am connected to.

Does anyone know how to do this?

Thanks in advance.

Jim
 
The CurrentProject.Connection gives you the ServerName and
DatabaseName, for example:

?CurrentProject.Connection
Provider=Microsoft.Access.OLEDB.10.0;
Persist Security Info=True;
Data Source=XXXXX;Integrated Security=SSPI;
Initial Catalog=YYYYY;Data Provider=SQLOLEDB.1

XXXXX and YYYYY are the ServerName and DatabaseName
respectively.

HTH
Van T. Dinh
MVP (Access)
 
Thanks,



Van T. Dinh said:
The CurrentProject.Connection gives you the ServerName and
DatabaseName, for example:

?CurrentProject.Connection
Provider=Microsoft.Access.OLEDB.10.0;
Persist Security Info=True;
Data Source=XXXXX;Integrated Security=SSPI;
Initial Catalog=YYYYY;Data Provider=SQLOLEDB.1

XXXXX and YYYYY are the ServerName and DatabaseName
respectively.

HTH
Van T. Dinh
MVP (Access)
Click to expand...
 
Could you give me an example so I can enter it into my form.

I guess I am too much of a newbie to understand how to implement this into
my form.

Thank,
 
'********
Public Function fnServerName(ByVal strConnect As String) As String
Dim lngStartPos As Long
Dim lngLength As Long

lngStartPos = InStr(1, strConnect, "Data Source=") + 12
lngLength = InStr(lngStartPos, strConnect, ";") - lngStartPos
fnServerName = Mid(strConnect, lngStartPos, lngLength)
End Function
'********

Public Function fnServerDBName(ByVal strConnect As String) As String
Dim lngStartPos As Long
Dim lngLength As Long

lngStartPos = InStr(1, strConnect, "Initial Catalog=") + 16
lngLength = InStr(lngStartPos, strConnect, ";") - lngStartPos
fnServerDBName = Mid(strConnect, lngStartPos, lngLength)
End Function
'********

Public Sub TestFunctions()
Dim strConnect As String

strConnect = CurrentProject.Connection
Debug.Print fnServerName(strConnect)
Debug.Print fnServerDBName(strConnect)
End Sub
'********
 
Thanks,

That's great.


Van T. Dinh said:
'********
Public Function fnServerName(ByVal strConnect As String) As String
Dim lngStartPos As Long
Dim lngLength As Long

lngStartPos = InStr(1, strConnect, "Data Source=") + 12
lngLength = InStr(lngStartPos, strConnect, ";") - lngStartPos
fnServerName = Mid(strConnect, lngStartPos, lngLength)
End Function
'********

Public Function fnServerDBName(ByVal strConnect As String) As String
Dim lngStartPos As Long
Dim lngLength As Long

lngStartPos = InStr(1, strConnect, "Initial Catalog=") + 16
lngLength = InStr(lngStartPos, strConnect, ";") - lngStartPos
fnServerDBName = Mid(strConnect, lngStartPos, lngLength)
End Function
'********

Public Sub TestFunctions()
Dim strConnect As String

strConnect = CurrentProject.Connection
Debug.Print fnServerName(strConnect)
Debug.Print fnServerDBName(strConnect)
End Sub
'********
Click to expand...
 
You should be able to parse the server name from the
connection string using mid() and instr(). The
connection string is in:
CurrentProject.AccessConnection.ConnectionString
The server name appears after the phrase "Data Source"
ND
 
Back
Top