General discussion

Locked

Creating a DSN using visual basic

By tl_129 ·
I am developing software that uses an ODBC data source name (DSN) to locate a database that's out on the network. The problem is that on every client, I have to create the DSN manually. I'd like to know how to set up a DSN with all the necessary parameters programmatically, using Visual Basic

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Creating a DSN using visual basic

by Mike Jones In reply to Creating a DSN using visu ...

I'm not a programmer but,

Setup the DSN once on a PC and then use regedit to export the odbc registery key for that DSN to an .reg file.

All you then need to do is get Visual Basic to run the reg file.

Or double click the reg file on the PCthat you want to install the ODBC settings.

You might not need to use Visual Basic because you can execute the reg file from a batch file.

Collapse -

Creating a DSN using visual basic

by Mike Jones In reply to Creating a DSN using visu ...

from a batch file

reg import test.reg

this bypasses any prompts to import the information.

Collapse -

Creating a DSN using visual basic

by tl_129 In reply to Creating a DSN using visu ...

Poster rated this answer

Collapse -

Creating a DSN using visual basic

by charliecorolie In reply to Creating a DSN using visu ...

Is it a requirement that you use a DSN? If not, you can use a DSNless connection under ADO and store your parameters where you like.

I don't think there's any way of doing this from VB without using the ODBC API directly.

Collapse -

Creating a DSN using visual basic

by tl_129 In reply to Creating a DSN using visu ...

Poster rated this answer

Collapse -

Creating a DSN using visual basic

by MadestroITSolutions In reply to Creating a DSN using visu ...

There is a way to do it with VB. You need to use windows API and the registry. After typing the answer here, I realized the space is not enough for the code, so I will put some here, then include the other part as comments to my answer, please put them together.... and if you are unable to do so, drop me a line at: juanja01@optonline.net and I will send you the code in an e-mail.

Put this in the declarations area:
Option Explicit

'Constant for a string variable type.
Private Const REG_SZ = 1

Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

--------------------------------------------
Then, on a function or subroutine, put this:
--------------------------------------------

Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String

Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

DataSourceName = "<the name of your new DSN>"
DatabaseName = "<name of the database to be accessed by the new DSN>"
Description = "<a description of the new DSN>"
DriverPath = "<path to your SQL Server driver>"
LastUser = "<defaultuser ID of the new DSN>"
Server = "<name of the server to be accessed by the new DSN>"
DriverName = "SQL Server"

continued on comment.....

Collapse -

Creating a DSN using visual basic

by MadestroITSolutions In reply to Creating a DSN using visu ...

The rest of the code......

'Create the new DSN key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _ DataSourceName, hKeyHandle)

'Set the values of the new DSN key.

lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _ ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))

'Close the new DSN key.lResult = RegCloseKey(hKeyHandle)

--------------------------------------------
Now to modify your key if you need to, use this:
-------------------------------------------

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)

------------------------------------------
I hope you can makesense of it, since it might be layed out wrong as text in HTML, so if you have problems with the code, feel free to drop me a line at: juanja01@optonline.net

Good luck!

Collapse -

Creating a DSN using visual basic

by tl_129 In reply to Creating a DSN using visu ...

The question was auto-closed by TechRepublic

Collapse -

Creating a DSN using visual basic

by RDLC In reply to Creating a DSN using visu ...

This is the code I use and is very effective.

'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& ' NULL Pointer

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 Function CreaODBC(NomDSN As String, DBQ As String, Description As String) As Boolean
On Error GoTo errh
Dim intRet As Long
Dim strDriver As String
Dim strAttributes As String

'Set the driver to SQL Server because it is most common.
strDriver = "Microsoft Access Driver (*.mdb)"
'Set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = "SERVER=SomeServer" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=" + Description & Chr$(0)
strAttributes = strAttributes & "DSN=" + NomDSN & Chr$(0)
strAttributes = strAttributes & "DBQ=" + DBQ & Chr$(0)
strAttributes = strAttributes & "UID=sa" & Chr$(0)
strAttributes = strAttributes & "PWD=" & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _ strDriver, strAttributes)
If intRet Then
List1.AddItem NomDSN & " --> " & DBQ
Else
List1.AddItem "ERROR :" & NomDSN & " --> " & DBQ
End If
CreaODBC = intRet
errh:
If Err.Number <> 0 Then
MsgBox Err.Description
CreaODBC = False
Exit Function
End If
End Function

Collapse -

Creating a DSN using visual basic

by RDLC In reply to Creating a DSN using visu ...

If you receive any errors in the code below is because you must include a ListBox in the form, to see the results (This Part is optional and can be removed).

If intRet Then
List1.AddItem NomDSN & " --> " & DBQ Else
List1.AddItem "ERROR :" & NomDSN & " --> " & DBQ
End If

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums