Create DSN

Is there a way to create DSN (Data Source Name) on local computer programmatically using MS Access?
In my case DSN will read SQL Server2000 database.

Thanks in advance.

Try the following bit of code, call the fCreate_DSN function and pass in the name of the DSN you wish to create as well as server name and Database. It assumes a few things but you can probably see the options for yourself in the code.

The function will also check for existing DSN and only create if needed

Cheers





Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, _
ByVal fRequest As Integer, _
ByVal lpszDriver As String, _
ByVal lpszAttributes As String) As Long


Private Declare Function RegOpenKeyEx _
Lib "advapi32.dll" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long) As Long


Private Declare Function RegEnumKeyEx Lib "advapi32.dll" _
Alias "RegEnumKeyExA" _
(ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
ByVal lpftLastWriteTime As String) As Long

Const ODBC_ADD_SYS_DSN = 4




Function fCreate_DSN(strDSN As String, strServer As String, strdb As String)
'============================================================
' Purpose: Create a new system DSN
' Programmer: Richard Jervis
' Date: 26/09/01
'============================================================
On Error GoTo fCreate_DSN_Err
Dim strErrMsg As String 'For Error Handling

Dim lngKeyHandle As Long
Dim lngResult As Long
Dim lngCurIdx As Long
Dim strValue As String
Dim classValue As String
Dim timeValue As String
Dim lngValueLen As Long
Dim classlngValueLen As Long
Dim lngData As Long
Dim lngDataLen As Long
Dim strResult As String
Dim DSNfound As Long
Dim syscmdresult As Long

'If fDoes_DSN_Exists(JDS_DSN_name) = True Then
syscmdresult = SysCmd(acSysCmdSetStatus, "Creating System DSN " & strDSN & "...")

lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & strDSN & Chr(0) & _
"Server=" & strServer & Chr(0) & _
"Database=" & strdb & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Trusted_Connection=Yes" & Chr(0) & _
"Description=Database" & Chr(0) & Chr(0))


If lngResult = False Then

MsgBox "ERROR: Could not create the System DSN " & strDSN & "." & vbCrLf & vbCrLf & _
"Please make sure that the SQL Server ODBC drivers have been installed." & vbCrLf & _
"Contact your IT Help Desk for more information."


End If

syscmdresult = SysCmd(acSysCmdClearStatus)
'End If

fCreate_DSN_Exit:
Exit Function

fCreate_DSN_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrLf
MsgBox strErrMsg, vbInformation, "Error in fCreate_DSN procedure"
Resume fCreate_DSN_Exit
End Select


End Function



Function fDoes_DSN_Exists(strDSNName As String) As Boolean
'============================================================
' Purpose: Search for system DSN
' Programmer: Richard Jervis
' Date: 26/09/01
'============================================================
On Error GoTo fDoes_DSN_Exists_Err
Dim strErrMsg As String 'For Error Handling

' Look for our System Data Source Name. If we find it, then great!
' If not, then let's create one on the fly.

Dim lngKeyHandle As Long
Dim lngResult As Long
Dim lngCurIdx As Long
Dim strValue As String
Dim classValue As String
Dim timeValue As String
Dim lngValueLen As Long
Dim classlngValueLen As Long
Dim lngData As Long
Dim lngDataLen As Long
Dim strResult As String
Dim syscmdresult As Long

syscmdresult = SysCmd(acSysCmdSetStatus, "Looking for System DSN " & strDSNName & " ...")

' Let's open the registry key that contains all of the
' System Data Source Names.

lngResult = RegOpenKeyEx(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI", _
0&, _
KEY_READ, _
lngKeyHandle)

If lngResult <> ERROR_SUCCESS Then
MsgBox "ERROR: Cannot open the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI." & vbCrLf & vbCrLf & _
"Please make sure that ODBC and the SQL Server ODBC drivers have been installed." & vbCrLf & _
"Contact call your MDTS System Administrator for more information."
syscmdresult = SysCmd(acSysCmdClearStatus)
'Unable to open the registry, so we cannot check to see if the
'DSN exists. So we are going to assume that it does.
fDoes_DSN_Exists = True
End If

' Now that the key is open, Let's look among all of
' the possible system data source names for the one
' we want.

lngCurIdx = 0
fDoes_DSN_Exists = False

Do
lngValueLen = 512
classlngValueLen = 512
strValue = String(lngValueLen, 0)
classValue = String(classlngValueLen, 0)
timeValue = String(lngValueLen, 0)
lngDataLen = 512

lngResult = RegEnumKeyEx(lngKeyHandle, _
lngCurIdx, _
strValue, _
lngValueLen, _
0&, _
classValue, _
classlngValueLen, _
timeValue)
lngCurIdx = lngCurIdx + 1

If lngResult = ERROR_SUCCESS Then

' Is this our System Data Source Name?

If strValue = strDSNName Then

'DSN Found

fDoes_DSN_Exists = True
syscmdresult = SysCmd(acSysCmdClearStatus)

End If

End If

Loop While lngResult = ERROR_SUCCESS And Not fDoes_DSN_Exists


syscmdresult = SysCmd(acSysCmdClearStatus)


fDoes_DSN_Exists_Exit:
Exit Function

fDoes_DSN_Exists_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrLf
MsgBox strErrMsg, vbInformation, "Error in fDoes_DSN_Exists procedure"
Resume fDoes_DSN_Exists_Exit
End Select


End Function

Thanks Richard!
I'll check your function.
I searched a few MS Access books and help files but could not find anything.
Where did you get this function?

Igor.

Parts of it came from the net, unfortunately I forget where, with improvisations from myself!

This code has been checked in 97 and XP so should work ok for you.

Hi Richard,

That code you posted is Awsome and could potentially save someone like myself ALOT of work so thank you for posting it and I will be sure to give all credits for it's creation to you in the code. I do have a few questions as to its implementation though that I was hoping you would be able to help me with:

In what part of the code do we pass the DSN, Server, and Database name? Also - where/how would this code be implemented...?

Thanks in advance!
Kevin

Yeah the codes pretty handy, should really be updated to work for more data sources than SQL Server... so If someone fancies updating or already has something please post!!!!!!


I run this function every time my database opens, usually this will be opened on a PC again and again so the DSN should already be available but when the app is moved to a new PC the code will automatically create the DSN meaning that my SQL links are still available.

just use...

call fCreate_DSN(DSN_NAME , SERVER_NAME, DATABASE_NAME)

Where...

DSN_NAME is a string containing the name of the DSN you want to create so something like "SQLServerDATA"

SERVER_NAME is a string containing the name of the SQL server.

DATABASE_NAME is a strin continaing the name of the database on your server.

You can then reference the actual DSN in code to get connection details or use it to connect liked tables or pass through queries.


Hope that helps

Richard,

I took the code and added it to a new module. I then used this on the 'On Open' event of my db splash form:

call fCreate_DSN(PARKSQAADMIN, DNRS0660, PARKMGMT)

where PARKSQAADMIN is the DSN_NAME, DNRS0660 is the name of the server, and PARKMGMT is the name of the database and I got the following error:

Complie Error:
ByRef argument type mismatch

On the name 'PARKSQAADMIN'.....?

Any thoughts as to why this isn't cooperating? I and running Win2000 and the application I am trying to use this on is a MS Access XP front end with SQL Server 2000 back end.

Thanks for the help!
Kevin

You are passing those in as strings arent you?

I'm guessing that PARKSQAADMIN is a string variable that contains the name of your DSN? If not and that is the name of your variable then it should have qoutes around it...

Otherwise it should work fine.


Let me know how you get on

Hi, Richard!
Hi everybody!
The function you gave me creates DSN with trusted connection but the way we set up SQL Server connection required to pass UserID(LoginId) and password.
So your function does not support it....
I found procedure which also creates DSN but does not establish LoginID and password in DSN itself.

May be you or somebody know how to include in code these 2 parameters??????????

The following procedure is from Visual Basic 6.0 help file.

RegisterDatabase Method Example

This example uses the RegisterDatabase method to register a Microsoft SQL Server data source named Publishers in the Windows Registry.

Using the Windows ODBC Control Panel icon is the preferred way to create, modify, or delete data source names.

Sub RegisterDatabaseX()

Dim dbsRegister As Database
Dim strDescription As String
Dim strAttributes As String
Dim errLoop As Error

' Build keywords string.
strDescription = InputBox( "Enter a description " & _
"for the database to be registered.")
strAttributes = "Database=pubs" & _
vbCr & "Description=" & strDescription & _
vbCr & "OemToAnsi=No" & _
vbCr & "Server=Server1"

' Update Windows Registry.
On Error GoTo Err_Register
DBEngine.RegisterDatabase "Publishers", "SQL Server", _
True, strAttributes
On Error GoTo 0

MsgBox "Use regedit.exe to view changes: " & _
"HKEY_CURRENT_USER\" & _
"Software\ODBC\ODBC.INI"

Exit Sub

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
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
End If

Resume Next

End Sub

Best regards.
Igor

Hi IgorB

in the code I pasted, in function fCreate_DSN there is a bit....

lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & strDSN & Chr(0) & _
"Server=" & strServer & Chr(0) & _
"Database=" & strdb & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Trusted_Connection=Yes" & Chr(0) & _
"Description=Database" & Chr(0) & Chr(0))


The line that says "Trusted_Connection=Yes" tells the DSN whether to use windows NT log on informatino or not. If you set this to No the user will be prompted to enter UserName and password each time the use a connection through your DSN.

If you want to create the DSN with a User and Password saved in, you can add to the above with LogIn= and Password=.

Hope that helps

Hi RichardJ,

Just wanted to pop in and say a big THANK YOU to you as I got it working now and everything is great!!! This code is going to save me sooooo much time (not to mention field techs who would have to manually build the DSN! )

Thanks for your help on getting this to work and thanks again for your willingness to share your code with others....

thanks and take care,
Kevin

Hi Richard
I enclosed in functoin SQLConfigDataSource Login and Password, it did not work. I tried to use LoginID, UID, User ID and Password,PWD, Pswd - still not successfull.....
I beleive that when Trusted_Connection=No we have to input manually Login ID and Password every time for every single attemp to connect.
By the way ""Trusted_Connection=Yes" is default if you don't specify and "UseProcForPrepare=Yes" apply only for SQL Server6.5 and older.

If anybody can pass Login and password parameters, please let everybody know.

And again thanks Richard for a good way to create DSN using API function.

Igor.

Hi richard I tried to add the code
"Login=user" & Chr(0) & _
"Password=user" & Chr(0) & _

the user has select,update,delete on the database
but I was prompted that it couldn't create the DSN
but win I use trusted connection it could create a DSN but i dont want the user enter the username and password for the odbc connection

Please help.

Support for DB2

What in above function would need to be changed to support connecting to a DB2 ODBC datasource?

i need dsn to be get created automatically for my websitedeveloped in J2EE wen i register it wid domain.
how can do that means how can i make my code flexible so that i do not need to make dsn for it?
i m using ms-access 2007.
plz help me soon.
my id is keith_taurus2006@yahoo.com
if u can plz mail me solution on my id.
thanku