Passing username and password to DSN created on the fly.

[VegaLA]

Good day all.

I recently set up one of my Databases (access 2007) to create an ODBC connection to SQL Server 2000 on the fly, using the aid of these two sites/articles:

http://support.microsoft.com/kb/892490


http://www.connectionstrings.com/sql-server

and all was well with the world, but we've had a slight change in that we no longer want to add the user's usernames to SQL Server 2000, and have instead opted to use a

username/password for that application/Database alone. I've created the username and password in the SQL Server, tested it and it works, but goign about making the changes for the

application itself is not so good.

The code I had is as follows:

CreateDSNConnection("MySQL", "MySQLServer", "MyDB", "MyUserName", "MyPassword")

which then calls this function:

Function CreateDSNConnection(strDescription As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
On Error GoTo CreateDSNConnection_Err

Dim stConnect As String

If Len(stUsername) = 0 Then

stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
Else

stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase
End If

DBEngine.RegisterDatabase strDescription, "SQL Server", True, stConnect

CreateDSNConnection = True
Exit Function
CreateDSNConnection_Err:

CreateDSNConnection = False
MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description

End Function

This does in fact create the DSN on the fly but insists on usign the user's username in the DSN connection, so I figured I needed to add the username and password variables,

changing the line to this:

stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "User id=" & stUsername & vbCr & "PASSWORD=" & stPassword

which now refuses to create the DSN, I suspect that my syntax is incorrect but it falls in line with the connection strings site above.

Can anyone tell me where i'm going wrong? I've double checked the SQL server user and it has the correct rihts to teh Database, but I cannot get those two values to write to the

DSN connection on the fly.

Thanks in advance,
Mitch.