pulling data out of the ODBC connection

[Tazcat]

I've an Access frontend connected to a SQL backend. The SQL server prompts the user for their ID/password at log in. I need to write a function to capture the UID out of the ODBC connection string, and store it to a variable. For the life of me I can't figure out how to even get started on this one. I can find all sorts of references (both here and in my own library) for how to PASS the DSN connection string, but nothing on how to PULL it.

Any help would be appreciated.



Melanie
~Tazcat2k~

 

[genomon]

Just a guess (haven't worked with this in awhile), but I think the ODBC32.dll makes this data available thru API. Unfortunately I don't have the API declares handy, but maybe you could check MSKB? Also there are a bunch at the bottom of this page:

http://www.vbexplorer.com/VBExplorer/viewcode.asp?SendText=files/Odbcmthd

I wondered why the baseball was getting bigger.
Then it hit me.

 

[genomon]

OK try this:

Declare Function SQLConnect Lib "odbc32.dll" (ByVal ldbc As Long, ByVal Server As String, _            ByVal serverlen As Integer, ByVal uid As String, ByVal uidlen As Integer, _            ByVal pwd As String, ByVal pwdlen As Integer) As Integer

I think you want uid.

I wondered why the baseball was getting bigger.
Then it hit me.

 

[Tazcat]

thanks for the genomon, but I don't know enough about api calls to make use of this. I was hoping for an ADO solution to the problem, if you've any thoughts along that line?

Melanie
~Tazcat2k~

 

[genomon]

Don't know of any ADO methods, but the info is available in SQL schema. You could run a stored proc to extract it, and then post it to a table linked into Access...

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[cmmrfrds]

This might get you started. Linked tables are defined as pass-through

Paste this into the standard module.

Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As ADOX.Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
'' Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type

''If tb.Type = "TABLE" Then
If tb.Type = "PASS-THROUGH" Then
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type
For Each pp In tb.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
End If
'' End If

Next

End Function

 

[SkipVought]

Hi,

try this as a starting point...

dim a, i as integer
a = split(CurrentProject.Connection.connectionstring,";")
for i = 0 to ubound(a)
  msgbox a(i)
next

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Tazcat]

thanks everyone for your feedback. it's begining to look like I am going to need to figure out how to make that API call work... unfortunately, I don't know to do that. So I guess I'm back where I started, unless anyone has some advice where I might go to teach myself API in a hurry?

Melanie
~Tazcat2k~

 

[jrbarnett]

Tazcat

Have you tried

MsgBox cn.Properties("User ID").Value

where cn is your ADODB.Connection object. If there's a \ in the name it means you've connected through windows authentication with the domain part beforehand and the username afterwards; if you are using SQL authentication there won't be a \ in the username.

You can get a very useful set of information if you loop through the properties of a connection object.

An alternative (as you're using sql server) is to run select suser_sname() and return its value to a variable in your app.

John