Making a DSN through Code!!! 2

Guest_imported · Nov 3, 2000

i am trying to make DSN through my Code using DBEngine.RegisterDatabase but it is givin me error when i give driver as Microsoft Access Driver (*.mdb) for Access. it makes a user DSn in case i give Driver as SQL Server.
i want to make System DSN using Access.
i also tried to use ODBCCP32.DLL using SQLConfigDatasource but it gives an error stating that it cannot find entrypoint or the dll may be corrupt. from wher i can get a new dll
Can u please enlightenen me on this
regards
Vishal

Karl Blessing · Nov 3, 2000

Try this, these are three functions I have that uses this method, you might be able to learn from them, I havent had time to go through and comment them.

sniplet from code

Code:

Private Sub cmdNext_Click()

      Dim itmcpy As Items
      Dim MsgBoxStr As Variant
      Dim Ubnd As Long, Lbnd As Long
      For Each itmcpy In Ini.Section(&quot;ODBC&quot;).Items
         MsgBoxStr = SplitAndDecode(itmcpy.Name)
         Ubnd = UBound(MsgBoxStr)
         Lbnd = LBound(MsgBoxStr)
         Select Case MsgBoxStr(Lbnd)
            Case &quot;AddDSN&quot;
               If Ubnd < 6 Then
                  MsgBox &quot;Too Few Parameters&quot;
               Else
                  MsgBox &quot;Add System DSN&quot; & vbCrLf & MsgBoxStr(Lbnd + 1) & vbCrLf & MsgBoxStr(Lbnd + 2) _
                  & vbCrLf & MsgBoxStr(Lbnd + 2) & vbCrLf & MsgBoxStr(Lbnd + 1) & vbCrLf & MsgBoxStr(Lbnd + 3) & _
                  vbCrLf & MsgBoxStr(Lbnd + 4)
                  
                  'Parameter setup
                  Dim DSN           As String
                  Dim Server        As String
                  Dim Address       As String
                  Dim Database      As String
                  Dim Description   As String
                  Dim Security      As String
               
                  DSN = &quot;DSN=&quot; & Trim(MsgBoxStr(Lbnd + 1))
                  Server = &quot;SERVER=&quot; & Trim(MsgBoxStr(Lbnd + 2))
                  Address = &quot;ADDRESS=&quot; & Trim(MsgBoxStr(Lbnd + 2))
                  Database = &quot;DATABASE=&quot; & Trim(MsgBoxStr(Lbnd + 1))
                  Description = &quot;DESCRIPTION=&quot; & Trim(MsgBoxStr(Lbnd + 3))
                  Security = &quot;NETWORK=dbmssocn&quot;
               
                  Select Case MsgBoxStr(Lbnd + 4)
                     Case &quot;SQL&quot;
                        SqlDriver = &quot;SQL Server&quot;
                        SQLParameter = DSN & vbNullChar & Server & vbNullChar & Address & vbNullChar & Security & vbNullChar & _
                        Database & vbNullChar & Description & vbNullChar & vbNullChar
                     Case &quot;MSOracle&quot;
                        SqlDriver = &quot;Microsoft ODBC for Oracle&quot;
                        SQLParameter = DSN & vbNullChar & Server & vbNullChar & Description & vbNullChar & _
                        vbNullChar
                     Case &quot;Oracle73&quot;
                        SqlDriver = &quot;Oracle73&quot;
                        SQLParameter = DSN & vbNullChar & Server & vbNullChar & Database & vbNullChar & _
                        Description & vbNullChar & vbNullChar
                  End Select
                  
                  'DSN execution
                  If SQLConfigDataSource(Me.hwnd, ODBC_ADD_SYS_DSN, SqlDriver, SQLParameter) = False Then
                     MsgBox &quot;Unable to create the system DSN&quot; & vbCrLf & &quot;-------------------------------&quot; _
                     & vbCrLf & DSN & vbCrLf & Server & vbCrLf & Address & vbCrLf & Database, _
                     vbCritical And vbOKOnly, &quot;Error&quot;
                  End If
               End If
            Case &quot;DelDSN&quot;
               If Ubnd < 2 Then
                  MsgBox &quot;too Few Parameters&quot;
               Else
                  MsgBox &quot;Delete System DSN&quot; & vbCrLf & MsgBoxStr(Lbnd + 1) & vbCrLf & MsgBoxStr(Lbnd + 2)
                  Select Case MsgBoxStr(Lbnd + 1)
                     Case &quot;Access&quot;
                        SqlDriver = AccessDriver
                     Case &quot;SQL&quot;
                        SqlDriver = &quot;SQL Server&quot;
                     Case &quot;MSOracle&quot;
                        SqlDriver = &quot;Microsoft ODBC for Oracle&quot;
                     Case &quot;Oracle73&quot;
                        SqlDriver = &quot;Oracle73&quot;
                  End Select
                  If SQLConfigDataSource(Me.hwnd, ODBC_REMOVE_SYS_DSN, SqlDriver, &quot;DSN=&quot; & MsgBoxStr(Lbnd + 2) & vbNullChar) = False Then
                     MsgBox &quot;Unable to remove the system DSN&quot;, vbCritical And vbOKOnly, &quot;Error&quot;
                  End If
               End If
         End Select
      Next
      ODBCRan = True
      NextForm
End Sub

Private Sub Form_Load()
   FadeForm Me, 2
   ThreeDForm Me
   If ScriptExist = False Then
      txtServer.Text = &quot;(local)&quot;
      txtDescription.Text = &quot;Web connection to ALMIS Database&quot;
      optSQLServer.Value = True
      Frame1.Visible = True
   Else
      Frame1.Visible = False
      lblUpgrade.Visible = True
   End If
End Sub

Private Function CreateMainODBC()
   ' save all the information
   Dim bSuccess      As Boolean
   Dim hwnd          As Long
   Dim DSN           As String
   Dim Server        As String
   Dim Address       As String
   Dim Database      As String
   Dim Description   As String
   Dim Security      As String
   
   DSN = &quot;DSN=&quot; & Trim(txtDatabase.Text)
   Server = &quot;SERVER=&quot; & Trim(txtServer.Text)
   Address = &quot;ADDRESS=&quot; & Trim(txtServer.Text)
   Database = &quot;DATABASE=&quot; & Trim(txtDatabase.Text)
   Description = &quot;DESCRIPTION=&quot; & Trim(txtDescription.Text)
   Security = &quot;NETWORK=dbmssocn&quot;
   If optSQLServer.Value = True Then
      SqlDriver = &quot;SQL Server&quot;
      SQLParameter = DSN & vbNullChar & Server & vbNullChar & Address & vbNullChar & Security & vbNullChar & _
         Database & vbNullChar & Description & vbNullChar & vbNullChar
   ElseIf optMSOracle.Value = True Then
      SqlDriver = &quot;Microsoft ODBC for Oracle&quot;
      SQLParameter = DSN & vbNullChar & Server & vbNullChar & Description & vbNullChar & _
         vbNullChar
   Else
      SqlDriver = &quot;Oracle73&quot;
      SQLParameter = DSN & vbNullChar & Server & vbNullChar & Database & vbNullChar & _
         Description & vbNullChar & vbNullChar
   End If
'   bSuccess = SQLConfigDataSource(Me.hwnd, ODBC_ADD_SYS_DSN, Driver, _
'      DSN & vbNullChar & Server & vbNullChar & Database & vbNullChar & _
'      Description & vbNullChar & vbNullChar)
   bSuccess = SQLConfigDataSource(Me.hwnd, ODBC_ADD_SYS_DSN, SqlDriver, SQLParameter)
   CreateMainODBC = bSuccess
End Function

Private Function CreateAccessODBC()
   Dim bSuccess      As Boolean
   Dim Driver        As String
   Dim DSN           As String
   Dim Server        As String
   Dim Database      As String
   Dim Description   As String
   
   DSN = &quot;DSN=&quot; & DescriptionDSN
'   Server = &quot;SERVER=&quot; & Trim(txtServer.Text)
   'Database = &quot;DATABASE=&quot; & gsWebServerPath & WEBSARAS_PATH & &quot;\database\descriptions.mdb&quot;
   Database = &quot;DBQ=&quot; & WebServer & SarasRoot & DescDatabase
   Description = &quot;DESCRIPTION=&quot; & &quot;Descriptoin stuff&quot;
   AccessParameter = DSN & vbNullChar & Database & vbNullChar & _
      Description & vbNullChar & vbNullChar
   bSuccess = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, AccessDriver, AccessParameter)
   CreateAccessODBC = bSuccess
End Function

API Declarations

Code:

' ODBC API
Public Const ODBC_ADD_DSN = 1&
Public Const ODBC_CONFIG_DSN = 2&
Public Const ODBC_REMOVE_DSN = 3&
Public Const ODBC_ADD_SYS_DSN = 4&
Public Const ODBC_CONFIG_SYS_DSN = 5&
Public Const ODBC_REMOVE_SYS_DSN = 6&
Public Const ODBC_REMOVE_DEFAULT_DSN = 7&

Public Const ODBC_ERROR_GENERAL_ERR = 1
Public Const ODBC_ERROR_INVALID_BUFF_LEN = 2
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST_TYPE = 5
Public Const ODBC_ERROR_COMPONENT_NOT_FOUND = 6
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD_VALUE = 8
Public Const ODBC_ERROR_INVALID_DSN = 9
Public Const ODBC_ERROR_INVALID_INF = 10
Public Const ODBC_ERROR_REQUEST_FAILED = 11
Public Const ODBC_ERROR_INVALID_PATH = 12
Public Const ODBC_ERROR_LOAD_LIB_FAILED = 13
Public Const ODBC_ERROR_INVALID_PARAM_SEQUENCE = 14
Public Const ODBC_ERROR_INVALID_LOG_FILE = 15
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FAILED = 17
Public Const ODBC_ERROR_CREATE_DSN_FAILED = 18
Public Const ODBC_ERROR_WRITING_SYSINFO_FAILED = 19
Public Const ODBC_ERROR_REMOVE_DSN_FAILED = 20
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22

Public Declare Function SQLCreateDataSource Lib &quot;odbccp32.dll&quot; _
   (ByVal hwnd&, ByVal lpszDS$) As Boolean
Public Declare Function SQLConfigDataSource Lib &quot;odbccp32.dll&quot; _
   (ByVal hwnd As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, _
   ByVal lpszAttributes As String) As Boolean

if you need more clarification let me know, but the key command here is "SQLCreateDataSource"

the two functions (other than the next_click) first one is for a typical SQL, Oracle, etc , and the other one is for Access (they could be one and the same, cept when I created the access one, I had some specific settings already set out)
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

MikeLacey · Nov 4, 2000

karl -- that's just *got* to be a faq...

Mike
michael.j.lacey@ntlworld.com

http://www.cargill.com/

Karl Blessing · Nov 4, 2000

I thought it was for a moment, but apparently the FAQ I had in VB General was how to get the listing of all the DNS on the machine, not how to create one through the code.
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Alt255 · Nov 5, 2000

This is some good stuff, Karl. Could you comment it and add it to your DSN FAQ?

Thanks.

Karl Blessing · Nov 5, 2000

the Next_Click is actually smaller, I acidently showed you if a script existed in my instalation program rather than if you typed it in.

Code:

Private Sub cmdNext_Click()
      Dim bMainODBCCreated                As Boolean
      Dim bAccessODBC                     As Boolean
   
      bMainODBCCreated = CreateMainODBC
      If bMainODBCCreated = False Then
         MsgBox &quot;ODBC not created&quot;
      End If
      bAccessODBC = CreateAccessODBC
      If bAccessODBC = False Then
         MsgBox &quot;Access ODBC not created&quot;
      End If
      If bAccessODBC And bMainODBCCreated Then
         ODBCRan = True
         NextForm
      End If
End Sub

but the other two function are correct, I am in the process of commenting, and making a sample project (which will be on my site)
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Nov 5, 2000

Unfortunatly I cannot post to FAQ due to some server error on Tek-tips (I already emailed Dave) but in any case you can pick up the project at

http://www.kb244.com/Downloads/

I'll see if I can get the FAQ up soon. (the FAQ is basically the cut and paste out of that project, which is commented)
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Making a DSN through Code!!! 2

Guest_imported

New member

Karl Blessing

Programmer

MikeLacey

MIS

Karl Blessing

Programmer

Alt255

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

Similar threads

Part and Inventory Search

Sponsor