Display ODBC Data Source System DSN Name on Access Form

[DinoZon]

Hello,



Does anyone know how to retrieve and display the current ODBC Data Source System DSN Name on an Access form? I have an access form that people will be using to test in different environments and would like to display their connection name so they know which environment they are logged into.

Thank you.

[June7]

Test this in the VBA immediate window:

?Application.CurrentProject.BaseConnectionString

Is that the info you want? I doubt it. Seems to be the frontend not the backend.

How do you establish connection? VBA or linked tables?

[ipisors]

DinoZon,

I think this question needs more specifics to it. People can have many DSNs set up on their computer, so...Which one would you be wanting? At my current FTE the average person has about 8 DSNs set up at a minimum, I have about 30 or more.

Are you perhaps thinking of whichever Connect property is associated with one particular table of the database table links? The Connect property of whatever table is underlying the current form? (are you sure the Form's recordsource is a simple table?, if not it doesn't make sense)

More specifics please...a database doesn't have just "one dsn" - in fact a database in general doesn't rely on a DSN. LInked tables do...some code may...but you haven't specified this or what yet.

thanks

[DinoZon]

Sorry, I should have included more details. I have an access front end that connects to an Oracle back-end through linked tables. We have 3 different environments, production, test, and development, and 3 different ODBC System DSN connections named after the servers themselves. When users log into the application they have to log in through the Oracle ODBC Driver Connect login screen. The Service Name identifies which server they are connecting to, which would be either TDSAD (development), TDSAT (test), or TDSAP (production). It is the TDSAD, TDSAT, OR TDSAP that I would like to display on the Access forms so they know which environment they are logged into

[Xipooo]

A FORM doesn't have an ODBC connection, but the data behind it might. If you are using an Access query for the data source of your form, then you can look in currentdb.querydefs("<queryname>").connect property to find the ODBC connection string being used for that query.

If you are making your own query string and not using a stored query in Access then you will have to look at the individual tables being referenced in your query by looking at currentdb.tabledefs("<tablename>").connect property to find the ODBC connection string being used for that table.

FWIW, for easier use I have found going DSN-less and programing in your ODBC connection strings is a bit more manageable. I don't like going around to every PC on the network and making sure the DSN's are all set up correctly. Conversely if you have a decent domain admin he/she can use Group Policy to create the DSN's.

[orange]

I'm not sure if this applies to your situation, but I set up a routine that let me work with whichever Oracle environment I needed. We had 4 environments.

stratdev1 DEVL..........Develoment
stratpre1 STRTPRE.....PreProduction/Maintenance
strategis SGIP...........Corporate Production
stratuat SUAT.........User Acceptance Testing

I've been retired for a number of years, but this was part of the code involved.

'---------------------------------------------------------------------------------------
' Procedure : RelinkCOCATables
' DateTime  : 2006-01-03 17:19
' Author    : j
' Purpose   : Relink all existing COCA_DBA tables in the proper Oracle environment
'
'Parm:  Env   string
'        stratdev1     DEVL
'        stratpre1     STRTPRE
'        strategis     SGIP
'        stratuat      SUAT
'---------------------------------------------------------------------------------------
'
Sub RelinkCOCATables(Env As String)

Dim connectEnv As String 'environment to connect to
Dim cocaDev As String    'current coords DEVL
Dim cocaPre As String    'current coords STRTPRE
Dim cocaProd As String   'current coords SGIP
Dim cocaUat As String    'current coords SUAT
Dim Msg As String        'msg string to show activity
Dim sResponse As String  'response to msg buttons
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb

'Oracle Tables ODBC
cocaDev = "Ora901 devl;PWD=XXXXX;"
cocaPre = "Ora901 strtpre;PWD=YYYYYY;"
cocaProd = "Ora901 sgip;PWD=PPPPPPP;"
cocaUat = "Ora901 suat;PWD=WWWWWWW;"

Select Case Env
  Case "stratdev1"
   connectEnv = cocaDev
   Case "stratuat"
   connectEnv = cocaUat
  Case "stratpre1"
   connectEnv = cocaPre
  Case "strategis"
   connectEnv = cocaProd
  Case Else
 End Select
   
' -- RELINK EACH COCA_DBA TABLE
For Each tbl In db.TableDefs
     If tbl.Name Like "COCA_DBA*" Then
     'drop current linked table
        DoCmd.RunSQL ("DROP TABLE " & tbl.Name)
     'relink the table in proper environment
        DoCmd.TransferDatabase acLink, "ODBC Database", _
         "ODBC;UID=COCA_USER;DSN=" & connectEnv, _
         acTable, "COCA_DBA." & Mid(tbl.Name, 10), _
         tbl.Name, , True
         Msg = Msg & Mid(connectEnv, 1, InStr(connectEnv, ";")) & "  Relinked : " & tbl.Name & vbCrLf
     Else
     End If
Next tbl

[ipisors]

Coming back late to the talk, but my 2 cents -
Thanks for the extra info. I see now somewhat what you are dealing with.

1. I have no idea how you'd track this Oracle login screen, sorry.
2. Can you use a different approach? Create a function that tests their ability to all 3 tables. The 2 that fail means they aren't connected to X or Y, the one that succeeds means they are connected to Z. Not sophisticated, but simple as heck. When you can't answer a question directly by programming, sometimes a very simple function can answer it indirectly.... (similar example...when I want to see if a user has access to a folder before a procedure starts, I don't use any fancy ad-ent code, I just run a boolean function that attempts to create and delete a text file in that location and trap the error. Works like a charm).
3. Interesting comment on DSN-less -- I certainly agree, I'd advise anyone to take advantage of this any time they can. I've found it of limited value because the DB's I have that utilize some serious RDBMS's in the first place (like at my full time jobs), are so mixed - some SQL server, DB2, Oracle, Access - that the DSN-less code I've found, I just couldn't quite get it to work for Oracle given our setup. But if all you had were sql server it's much easier.

[Xipooo]

Coming back late to the talk, but my 2 cents -
...
3. Interesting comment on DSN-less -- I certainly agree, I'd advise anyone to take advantage of this any time they can. I've found it of limited value because the DB's I have that utilize some serious RDBMS's in the first place (like at my full time jobs), are so mixed - some SQL server, DB2, Oracle, Access - that the DSN-less code I've found, I just couldn't quite get it to work for Oracle given our setup. But if all you had were sql server it's much easier.

In those situations, we set up linked servers on the MS SQL server. That way we have one point of entry for all our data and a much more powerful SQL engine is handling the operations. OPENQUERY is your friend.

[ipisors]

In those situations, we set up linked servers on the MS SQL server. That way we have one point of entry for all our data and a much more powerful SQL engine is handling the operations. OPENQUERY is your friend.

Well we do have a lot of linked sql servers but unfortunately, none linked to other rdbms. would be nice, but i have nothing to do with authority on that setup. ;(