create DSN not working

[Keith G Hicks]

Can anyone tell me why this fails? It works if I leave out the UID and PWD
lines but when those are in there it does nothing. It returns 0. I can
create the DSN manually in ODBC datasources using all the same parameters.


Option Compare Database
Option Explicit

Const ODBC_ADD_SYS_DSN = 4 'Add data source
Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6 'Remove data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
hwndParent As Long, ByVal fRequest As Long, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Sub Command0_Click()
Call Build_SystemDSN
End Sub

Sub Build_SystemDSN()

Dim ret%, Driver$, Attributes$

Driver = "SQL Server" & Chr(0)
Attributes = "DSN=Keith Test 2" & Chr(0)
Attributes = Attributes & "DESCRIPTION=Keith Test for sqldb1" & Chr(0)
Attributes = Attributes & "SERVER=server" & Chr(0)
Attributes = Attributes & "DATABASE=sqldb1" & Chr(0)
Attributes = Attributes & "UID=sa" & Chr(0)
Attributes = Attributes & "PWD=abc123" & Chr(0)

ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)

'ret is equal to 1 on success and 0 if there is an error
If ret <> 1 Then
MsgBox "DSN Creation Failed"
End If

End Sub


Thanks,

Keith

 

[Keith G Hicks]

Never mind. I guess it's not possible.

Keith

 

[Douglas J. Steele]

Do you really need a DSN? Take a look at
http://www.accessmvp.com/djsteele/DSNLessLinks.html for one way to use
DSN-less connections.

If you're determined to have a DSN, take a look at
http://support.microsoft.com/?id=184608 for another way to create them.

Finally, I don't have any SQL Server DSNs on this machine, so I can't check,
but check the registry for a DSN that works. Does it actually contain
parameters named UID and PWD, or are they called something else?

 

[Guest]

Keith,

Don't give up so fast

I have several excelent Access sites bookmarked that I refer to constantly.
One of them is "The Access WEb" at http://www.mvps.org/access

I did a search for "DSN" and found this page:

"Creating DSNs from code" ( http://www.mvps.org/access/tables/tbl0014.htm)


There is code courtesy of Anthony S. Karre that appears to do what you are
trying to do.


HTH

 

[Guest]

Also "Relink ODBC tables from code" at

http://www.mvps.org/access/tables/tbl0010.htm

with code courtesy of Dev Ashish


HTH

 

[Keith G Hicks]

Yes, I do need a DSN. I actually need it for something in Delphi language to
connect to a 3rd party tool, not MS Access. But I know Access and found more
examples for DSN creation out there. Anyway, I decided to just write
directly to the registry instead and it works peachy. Thanks everyone for
the information.

Keith