Creating a DSN.......

Hi,
im using the code below to create a dsn fron vb for a SQL Server databse...but im getting an error if i try to add a userid (using UID) to the attributes.....is this not the correct way for adding a userid?? is there another way to do this??

the error im getting is:

"Run time error 3146

ODBC call failed".

Thanx.

Code:

---

    Dim nRet As Long
    Dim sDriver As String
    Dim sAttributes As String

    sDriver = "SQL Server"
    sAttributes = "Server=my_server" & Chr$(0)
    sAttributes = sAttributes & "DSN=test" & Chr$(0)
    sAttributes = sAttributes & "DATABASE=test" & Chr$(0)
    sAttributes = sAttributes & "UID=test" & Chr$(0)
    'sAttributes = sAttributes & "PWD=test" & Chr$(0)
   
    DBEngine.RegisterDatabase "test", "SQL Server", True, sAttributes
     

    nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, sDriver, sAttributes)

---

With ADO you can create a dsn less connection to the db, just create the appropriate connection string.

Tip description

If you need to quickly create and delete a Date Source Name (DSN) on the fly, use the code to do so. Authored by Waty Thierry.

Code:

---


Option Explicit

'Declarations Used to Generate DSN
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

Public Sub CreateDSN(sDSN As String)
    Dim nRet As Long
    Dim sDriver As String
    Dim sAttributes As String

    sDriver = "Oracle73 Ver 2.5"
    sAttributes = "Server=pressdb.world" & Chr$(0)
    sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
    sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
    sAttributes = sAttributes & "DATABASE=DB" & Chr$(0)
    sAttributes = sAttributes & "UID=Waty" & Chr$(0)
    sAttributes = sAttributes & "PWD=myPassword" & Chr$(0)

    DBEngine.RegisterDatabase "kiki", "Oracle73 Ver 2.5", True, sAttributes

    nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, sDriver, sAttributes)
End Sub

Public Sub DeleteDSN(sDSN As String)
    Dim nRet As Long
    Dim sDriver As String
    Dim sAttributes As String

    sDriver = "Oracle73 Ver 2.5"
    sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)

    nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)

End Sub

---

zuperman,
that is the code i've used....with a few changes for SQL Server.....am getting an error when i do.......any other ideas?

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.

Code:

---

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

---

have already gone thro' that example, zuperman.....i dont have any problems when i create the dsn without specifying the userid and the pwd...its only when i do specify a userid and a pwd that i get an error......

so i guess for now i'll just not add that bit.....and use the userid and pwd at the time of connecting to the databse....unless someone can come up with a solution to my problem

thanx anyway!

I have seen alot of queries about how to create DSN's from the code. The quickest way is to create the entries iis n the registry in the ODBC.INI. If you set up 2 DSN's both NT and SQL authentification you can see the entries that are put in.
The following are the main ones:
AutoTranslate
Database
Description
Driver
DriverLastUser
Server
Trusted_Connection

As you see the password is not stored here, it is not needed, as you would be passing that in your connection string.

This has worked for me and is only needed for older style applications that still use ODBC. All new applications use use providers then you will not have this problem.

MSDN article Q184608 should help.
http://support.microsoft.com/support.../q184/6/08.asp

thanx, will check them out......

thanx zubair and mongo...that was really helpful! :)