Server name on Form

[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

 

[Van T. Dinh]

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)

 

[Jim Gaudet, MCSE]

Thanks,

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)

 

[Jim Gaudet, MCSE]

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,

 

[Van T. Dinh]

'********
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
'********

 

[Jim Gaudet, MCSE]

Thanks,

That's great.

'********
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
'********

 

[ND]

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