Extract Server Name from ODBC

Paul Cook-Giles
Paul Cook-Giles used Ask the Experts™
on
I have linked tables in a sql database in my Access database.  I need to build a function that will accept the name of a linked table, and return the name of the server on which the table resides.  

I already know how to query MSysObjects to get the .Connect string, which tells me the name of the DSN used for the linking
((SELECT  *FROM MSysObjects WHERE (((Type)=4)) OR (((Type)=6))  or   CurrentDb.Tabledefs("mstLookupTb").Connect or Dlookup("Connect", "MSysObjects", "Name = 'mstLookupTb'")).  I don't need the name of the DSN;  I need the name of the server.  In this screenshot, I need to get "Sandbox01", not "PaulsSandbox".
User DSN "PaulsSandbox"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can't really get the servername from the connect string, unless you've specifically created the string to include the servername. If you're using a DSN, the servername is in that DSN, so you'd have to (a) parse the DSN name from the connection string and then (b) parse the DSN to get the servername.

Can you show the results of your query or DLookup? That might give more information.
Paul Cook-GilesSenior Application Developer

Author

Commented:
Sure.  :)
Dlookup:  DSN=PaulsSandbox;Description=copy of production data;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=Sandbox01
The Connect column in the query returns the same string.

Getting the servername from the connect string isn't what I need to do;  I can see the connect string and the server name isn't in it.
I need to retrieve the name of the server from the ODBC User DSN.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Okay - so you have the DSN name. You now need to look in the Registry to find the information. The data can be in various locations, but for the most part it's going to be in HKCU\Software\ODBC\ODBC.INI, in a subkey for the DSN (in your case, PaulsSandbox). In that key, you'll find a value named "Server".

Here's how to read the registry:

http://vba-corner.livejournal.com/3054.html
Paul Cook-GilesSenior Application Developer

Author

Commented:
Thank you, Scott!  I'd found that, but I couldn't make the .RegRead work. These two sites, however, did give me the clues to get the server name out of the Registry:
https://superuser.com/questions/595551/how-to-export-a-specific-registry-key-to-a-text-file-using-command-line
https://kencenerelli.wordpress.com/2012/07/26/export-an-odbc-data-source-from-the-registry/


Public Function GetLinkedServerName(strDSNName As String) As String
'accepts the name of a User DSN
'exports the Registry entry for that DSN to a text file
'reads through the file and returns the server name

'the Registry entry name template is HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\NameOfTheUserDSN

Dim strServerLine As String, strFilename As String, strTextLine As String, iFile As Integer
strFilename = "C:\ServerName.txt"
Shell "reg export HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & strDSNName & " " & strFilename

iFile = FreeFile
Open strFilename For Input As #iFile
Do Until EOF(1)
'step through lines in file, return one like  "Server"="NameOfTheUserDSN"
    Line Input #1, strTextLine
    If strTextLine Like "*server*" Then
      strServerLine = strTextLine
      GoTo ReturnServerName
   End If
Loop

ReturnServerName:
'strip out double quotes and identifier
strServerLine = Replace(strServerLine, Chr(34), "")
strServerLine = Replace(strServerLine, "Server=", "")
GetLinkedServerName = strServerLine

'cleanup
Close #iFile
Kill strFilename

End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today