Register a DSN with UID & PWD?

[RemyS]

Believe me, I've tried!

I'm working with a SQL Server 7 backend and an Access 97 frontend ... and an Excel 97 frontend too.

I was able to register the DSN without UID and PWD, and Access worked fine, but it seems Excel needs the UID & PWD to connect.

This is what I've got that works (without UID & PWD):

'*************************************************
'* Declare Constants
Const DSN = "BERT_Stg"
Const SERVERNAME = "lons000391"
Const DBNAME = "BERT_UAT"
Const DESCRIBE = "BERT STG SQL Database"
Const CONNECTSTRING = "ODBC;DSN=" & DSN & ";DATABASE=" & DBNAME & ";UID=BERT_user;PWD=sesame"
'*************************************************

Function RegisterDSNs()

  Dim strAttributes As String
  Dim errLoop As Error

    strAttributes = "Database=" & DBNAME & _
        vbCr & "Description=" & DESCRIBE & _
        vbCr & "Server=" & SERVERNAME
        
'* Update Windows Registry.
On Error GoTo Err_Register
    DBEngine.RegisterDatabase DSN, "SQL Server", True, strAttributes
On Error GoTo 0

  
Exit Function

Err_Register:

  '* Notify user of any errors that result from the invalid data.
  If DBEngine.Errors.Count > 0 Then
    For Each errLoop In DBEngine.Errors
      Debug.Print "Error number: " & errLoop.Number & " -- " & errLoop.Description
    Next
  End If

  Resume Next


End Function

I've tried adding

    strAttributes = "Database=" & DBNAME & vbCr _
        & "Description=" & DESCRIBE & vbCr _
        & "Server=" & SERVERNAME & vbCr _
        & "UID=BERT_user" & vbCr _
        & "PWD=sesame"

I've tried adding a

vbCr

at the end.

I've tried using the full ODBC connection string.

I've tried using

LastUser

instead of

UID

I've also tried using the

Function SQLConfigDataSource Lib "ODBCCP32.DLL"

intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)

with

Private Const vbAPINull As Long = 0

None of these attempts were able to create the DSN.

It also appears that on every forum I've searched no solution has ever been posted to solve/explain this.


If anyone can help, or actually knows the answer, I shall shower them with resplendent adulation and reverence, not to mention an expert star.

Hundreds of ways to do things with PC's, and learning new ways every day.

 

[Norris68]

Have a look at

http://support.microsoft.com/default.aspx?scid=kb;en-us;147875

in the MS KBase. It's about using DSN-less connections in DAO & RDO, but also uses UID & PWD parameters. It also contains links to other KBase articles that may help.

 

[RemyS]

Thanks,
but somehow I don't think a DSN-less connection will work with an Excel97 Pivottable (they're not all that easy to re-connect in code).

I did manage to get a workaround though:

I removed the ODBC connection (created in the code above)
I then refreshed the pivottables in Excel.

Needless to say they were unable to find the external data, and the ODBC administrator kindly wizarded me through the process of creating a new DSN. At this stage I made sure to enter the UID and PWD. I had to do this for each pivottable and querytable.

Interestingly just before the end of the process it prompted me a second time to use Trusted connection (which I unchecked) and enter the UID and PWD again. I think this might be Excel saving these details in the workbook, rather than within the DSN.

I also noted that when the DSN is created in code, it does not save the UID (

LastUser

), but showed my NT loggin. However, recreating it through Excel did save the UID.

Further irony: the Excel registered DSN (with UID) caused an error in the Access startup routine!?! So I had to remove the DSN again and allow Access to register it through the code.

Challenge surmounted!

Hundreds of ways to do things with PC's, and learning new ways every day.