Create DSN entry

  • Thread starter Thread starter jsaunders
  • Start date Start date
J

jsaunders

I have written a program that uses ODBC connections. Can
anyone tell me how to have it create the System DSN for
an access database on load? or in the deployment package?
 
It's not a good idea to use ODBC to interface to an Access/Jet database. You
should use the OleDb provider--it's a far richer interface. ADO.NET does not
know how to create an ODBC DSN. This requires use of special ODBC APIs that
(frankly) I don't recommend for a variety of reasons.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I've seen that before. Here is my reasoning for using ODBC.. I want the
program to install, placing a copy of the dbase in the users application
directory. I want it to set up a DSN for that dbase before the program
starts. Now I want to do this so the user can copy the dbase to a shared
drive on a network, then point each of the client's DSNs to the shared
dbase. That way multiple users can use the same dbase on the network. I
have a helpdesk program at work that works similarly. I can do this
since I don't have to include the path in the ODBC connection to the
program. However, if I use OleDb, I have to designate an exact path.
That doesn't seem very business-ready. I don't know how else to make the
program able to be shared easily. If I am missing something, please fill
me in, as I am a newbie to this. Here's a good example though:

I have this program at work and at home (for test purposes)
At work, the dbase in installed on an F drive, while at home, it's a C
path. With Ole, one or the other will always fail. Now that I have it
ODBC, they both work fine anywhere I direct it to.

If anyone can just tell me how to create a DSN on startup I will be very
happy.
 
It's easier to create a UDL file that points to the common database file.
It's simply a Unicode file that contains the connect string data.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
UDL file. That I haven't thought of.. could work. Now is it something
that the basic computer user will be able to modify without a problem?
 
Possibly, if you let them. It's editable with Notepad (in newer versions of
Windows). You can protect it or make it RO/Hidden.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

I may have to face the same problem in the near future
and I am kind of curious about your answer. I have read
that, at least currently, the ODBC API has more
capabilities than the OLE DB provider. Can you tell me a
few reasons why you don't recommend using the ODBC API's?

Thanks in advance,

Bob Costello
 
¤ I have written a program that uses ODBC connections. Can
¤ anyone tell me how to have it create the System DSN for
¤ an access database on load? or in the deployment package?

As Bill mentioned you should avoid using ODBC with Access. The Jet OLEDB Provider is the preferred
method.

However, I posted this a week ago if you should still happen to need it.

The following is a conversion from VB 6.0 code in MS KB article:

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

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

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


Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs)
Handles Command1.Click

Dim intRet As Integer
Dim strDriver As String
Dim strAttributes As String

'Set the driver to SQL Server because it is most common.
strDriver = "SQL Server"
'Set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = "SERVER=SomeServer" & Chr(0)
strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr(0)
strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr(0)
strAttributes = strAttributes & "DATABASE=pubs" & Chr(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)
If intRet <> 0 Then
MsgBox("DSN Created")
Else
MsgBox("Create Failed")
End If

End Sub

Private Sub Command2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs)
Handles Command2.Click

Dim intRet As Integer
Dim strDriver As String
Dim strAttributes As String

'Set the driver to SQL Server because most common.
strDriver = "SQL Server"
'Set the attributes delimited by null.
'See driver documentation for a complete list of attributes.
strAttributes = "DSN=DSN_TEMP" & Chr(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, strDriver, strAttributes)
If intRet <> 0 Then
MsgBox("DSN Deleted")
Else
MsgBox("Delete Failed")
End If

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
How funny, I was just in the process of converting that same code. How
do I change it for MS Access? This only works for SQL. Also, this only
creates a User DSN. How do I make it for system DSN?
 
It's because the Jet ODBC driver is so limited. It's designed to permit
applications to extract data out of Access databases as they migrate to
other platforms--not as a feature-rich query interface. The OLE DB provider
for JET is highly evolved and tuned for functionality and performance.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
¤
¤ How funny, I was just in the process of converting that same code. How
¤ do I change it for MS Access? This only works for SQL. Also, this only
¤ creates a User DSN. How do I make it for system DSN?
¤

Whoops, sorry about that. Here's the modified code for Microsoft Access:

Private Const ODBC_ADD_SYS_DSN As Short = 4 ' Add System DSN

strDriver = "Microsoft Access Driver (*.mdb)"
strAttributes = "DESCRIPTION=AccessDSN" & Chr(0)
strAttributes = strAttributes & "DSN=AccessDSN" & Chr(0)
strAttributes = strAttributes & "DBQ=e:\MyDocuments\db1.mdb" & Chr(0)

intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
If intRet <> 0 Then
MsgBox("DSN Created")
Else
MsgBox("Create Failed")
End If


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top