Create DSN as installation? 1

[jgoodman00]

I have an application which requires a DSN connection.

Ideally, I would like to automatically create this DSN at the time the program is installed.

I am using the Visual Studio installer.


Any suggestions?

James Goodman MCP

http://www.james-goodman.co.uk

 

[phzero]

Add the reference Microsoft Remote Data Object 2.0 to your project and then write code like this:

strAttribs = "Description=aaBopTemp" _
& Chr$(13) & "User ID=dba" _
& Chr$(13) & "Password=sql" _
& Chr$(13) & "Servername=BopMatch" _
& Chr$(13) & "Network=tcpip"

' Create new registered DSN.
rdoEngine.rdoRegisterDataSource "aaDummy", "Adaptive Server Anywhere 6.0", False, strAttribs

Not that it matters which Provider you're using since some fields might not be applicable whereas with others its absolutely crucial. Hope it helps.

 

[tnguyen03]

...check out this thread that i've posted thread222-540580.

 

[UncleT]

Here is how I do it. This will only create Access or Sql Server ODBCs.

1) Copy the following code and make it into a module.
2) Make a backup copy of folder "Setup1" under C:\Program Files\Microsoft Visual Studio\VB98\Wizards\PDWizard
3) Rename setup1.exe to setup1.old under C:\Program Files\Microsoft Visual Studio\VB98\Wizards\PDWizard
4) open Setup1.vbp file and add the module you created.
5) In the form_load section call the functions to create the appropriate ODBC.
6) create new setup1.exe
7) copy setup1.exe into C:\Program Files\Microsoft Visual Studio\VB98\Wizards\PDWizard
8) create new setup package
9) NOTE Delete or rename the new Setup1.exe and rename the old back to setup1.exe (if not all setups from here on out will create the DSN's)


Hope this helps.




''Code for module
Option Explicit

Private Const SQL_SUCCESS As Long = 0 ' ODBC Success
Private Const SQL_ERROR As Long = -1 ' ODBC Error
Private Const SQL_FETCH_NEXT As Long = 1 ' ODBC Move Next

Private Declare Function SQLDataSources Lib "ODBC32.DLL" _
(ByVal hEnv As Long, ByVal fDirection _
As Integer, ByVal szDSN As String, _
ByVal cbDSNMax As Integer, pcbDSN As Integer, _
ByVal szDescription As String, ByVal cbDescriptionMax _
As Integer, pcbDescription As Integer) As Integer

Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" _
(env As Long) As Integer

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 Const ODBC_ADD_SYS_DSN = 4
'

Public Sub GetDSNs()

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: :::
'::: This routine does the actual work :::
'::: :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'
Dim intRetCode As Integer ' the return code
Dim strDSNItem As String ' the dsn name
Dim strDRVItem As String ' the driver name
Dim strDSN As String ' the formatted dsn name
Dim intDSNLen As Integer ' the length of the dsn name
Dim intDRVLen As Integer ' the length of the driver name
Dim hEnv As Long ' handle to the environment
Dim strTemp As String ' Tempspace
Dim strDSNTemp As String ' Tempspace
Dim int_Ct As Integer
Dim int_Ct2 As Integer
Dim str_DSN_Hold() As String

On Error Resume Next

ReDim str_DSN_Hold(0 To 5000)

If (SQLAllocEnv(hEnv) <> SQL_ERROR) Then
Do

strDSNItem = Space$(1024)
strDRVItem = Space$(1024)

intRetCode = SQLDataSources(hEnv, SQL_FETCH_NEXT, strDSNItem, _
Len(strDSNItem), intDSNLen, strDRVItem, _
Len(strDRVItem), intDRVLen)

strDSN = Left$(strDSNItem, intDSNLen)

' If (Len(strDSN) > 0) And (strDSN <> Space$(intDSNLen)) Then
' strDSNTemp = strDSN & vbTab & &quot;(&quot; & _
' Left$(strDRVItem, intDRVLen) & &quot|&quot;
' ' Check for dupes...
' If InStr(strTemp, strDSNTemp) = 0 Then
' strTemp = strTemp & strDSNTemp
' End If
' End If

If UCase(Mid$(strDRVItem, 1, 10)) = &quot;SQL SERVER&quot; Or _
UCase(Mid$(strDRVItem, 1, 23)) = &quot;MICROSOFT ACCESS DRIVER&quot; Then
str_DSN_Hold(int_Ct) = &quot;&quot;
For int_Ct2 = 1 To Len(Trim$(strDSNItem))
If Asc(Mid$(strDSNItem, int_Ct2, 1)) >= 32 And Asc(Mid$(strDSNItem, int_Ct2, 1)) < 127 Then
str_DSN_Hold(int_Ct) = str_DSN_Hold(int_Ct) + Mid$(strDSNItem, int_Ct2, 1)
End If
Next int_Ct2

int_Ct = int_Ct + 1
End If

Loop Until intRetCode <> SQL_SUCCESS
End If

ReDim g_str_DSN(0 To int_Ct - 1)

For int_Ct = 0 To UBound(g_str_DSN)
g_str_DSN(int_Ct) = str_DSN_Hold(int_Ct)
Next int_Ct

End Sub

'******* The following code will add a DSN

Public Function CreateSQLServerDSN(DSNName As String, _
Description As String, ServerName As String, Database As String) As Boolean

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN SQL SERVER DATABASE
'PARAMETERS: 'DSNName = DSN Name
'ServerName = Name of Server
'Database = Database to Use
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateSQLServerDSN &quot;MyDSN&quot;, &quot;MyServer&quot;, &quot;MyDatabase&quot;

Dim sAttributes As String

sAttributes = &quot;DSN=&quot; & DSNName & Chr(0)
sAttributes = sAttributes & &quot;DESCRIPTION=&quot; & Description & Chr(0)
sAttributes = sAttributes & &quot;Server=&quot; & ServerName & Chr(0)
sAttributes = sAttributes & &quot;Database=&quot; & Database & Chr(0)
sAttributes = sAttributes & &quot;Trusted_Connection=No&quot;
CreateSQLServerDSN = CreateDSN(&quot;SQL Server&quot;, sAttributes)

End Function

Public Function CreateAccessDSN(DSNName As String, _
DatabaseFullPath As String, bln_ReadOnly As Boolean) As Boolean

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN ACCESS DATABASE
'PARAMETERS: 'DSNName = DSN Name
'DatabaseFullPath = Full Path to .mdb file
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateAccessDSN &quot;MyDSN&quot;, &quot;C:\MyDb.mdb&quot;

Dim sAttributes As String

'TEST TO SEE IF FILE EXISTS: YOU CAN REMOVE IF YOU
'DON'T WANT IT
If Dir(DatabaseFullPath) = &quot;&quot; Then Exit Function

sAttributes = &quot;DSN=&quot; & DSNName & Chr(0)
sAttributes = sAttributes & &quot;DBQ=&quot; & DatabaseFullPath & Chr(0)
If bln_ReadOnly = True Then
sAttributes = sAttributes & &quot;ReadOnly=1&quot; & DatabaseFullPath & Chr(0)
Else
sAttributes = sAttributes & &quot;ReadOnly=0&quot; & DatabaseFullPath & Chr(0)
End If
CreateAccessDSN = CreateDSN(&quot;Microsoft Access Driver (*.mdb)&quot;, _
sAttributes)

End Function

Public Function CreateDSN(Driver As String, Attributes As _
String) As Boolean

'PURPOSE: CREATES A SYSTEM DSN
'PARAMETERS: 'Driver = DriverName
'ATTRIBUTES: 'Attributes; varies as a function
'of the Driver
'EXAMPLE: Refer to Code Above

CreateDSN = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, _
Driver, Attributes)

End Function





****** Code for frmSetup1 Form_Load

If CreateSQLServerDSN(&quot;DSN&quot;, &quot;Description&quot;, &quot;Server&quot;, &quot;Password&quot Then
End If

If CreateAccessDSN(&quot;DSN&quot;, &quot;Path of MDB&quot;, True) Then
End If

 

[SonOfEmidec1100]

Although it does not cover exactly what you want have a look at thread222-551876