making a system dsn w/ api

How do you programmatically set up a system database connection through API

VB Code:

Const ODBC_ADD_SYS_DSN = 4       'Add System data source
Const ODBC_ADD_DSN = 1       'Add User data source
Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source
 
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 Enum enumDSN_Type
    eUserDSN = ODBC_ADD_DSN
    eSystemDSN = ODBC_ADD_SYS_DSN
End Enum
Public Function CreateDSN(pDSNName As String, pDBPath As String, pDSNType As enumDSN_Type) As Boolean
    Dim lngRetVal
    Dim strDriver As String
    Dim strAttributes As String
    
    CreateDSN = False
     
    strDriver = "Microsoft Access Driver (*.mdb)" & Chr(0)
    strAttributes = "DSN=" & pDSNName & Chr(0)
    strAttributes = Attributes & "Uid=Admin" & Chr(0) & "pwd=" & Chr(0)
    strAttributes = Attributes & "DBQ=" & pDBPath & Chr(0)
    lngRetVal = SQLConfigDataSource(0, pDSNType, strDriver, strAttributes)
    
    'If the Return Value 1 then
    'DataSource is created succesefully
    'otherwise it has falied
    CreateDSN = lngRetVal
End Function

You can call this routine like this:

VB Code:

Private Sub Command1_Click()
    Call CreateDSN("MyDSN", "C:\MyDB.mdb", eSystemDSN)
End Sub

This will create a SYstem DSN for Access database.
By changing the driver type you can setup ODBC DSN for any type of database/server.

Ahh you are so awsome, thank you very much!!

what do we need to change the attributes for sql database which resides in remote machine.

I mean for client configuration we need to give the remote ip address in server textbox and network libraries we need to check tcp/ip
to achieve this what we have to add in the attributes?

Thanx in advance

All you have to add is the Server attribute. So in your attributes string you go...

Attributes = Attributes & "Server=127.0.0.1" & Chr(0)
Attributes = Attributes & "Database=The database which resides on sql server" & chr(0)

Those two are the extra ones for doing remote..

Hi, Thanx

The second parameter in the following function what I should I replace in case of sqlserver


Private Sub Command1_Click()
Call CreateDSN("MyDSN", "C:\MyDB.mdb", eSystemDSN)
End Sub

We can change this function to accept both Access and SQL Server:

VB Code:

Const ODBC_ADD_SYS_DSN = 4       'Add System data source
Const ODBC_ADD_DSN = 1       'Add User data source
Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source
 
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 Enum enumDSN_Type
    eUserDSN = ODBC_ADD_DSN
    eSystemDSN = ODBC_ADD_SYS_DSN
End Enum
 
Public Enum enumDBType
    eACCESS = 0
    eSQL_SERVER = 1
End Enum
 
Public Function CreateDSN(p_DSNName As String, p_DSNType As enumDSN_Type, _
                    p_DBType As enumDBType, p_strDatabase As String, _
                    Optional p_strServer As String = "", _
                    Optional p_strUser As String = "", _
                    Optional p_strPassword As String = "") As Boolean
    Dim lngRetVal
    Dim strDriver As String
    Dim strAttributes As String
    
    CreateDSN = False
     
    Select Case p_DBType
        Case eACCESS
            strDriver = "Microsoft Access Driver (*.mdb)" & Chr$(0)
            strAttributes = "DSN=" & p_DSNName & Chr$(0)
            strAttributes = Attributes & "Uid=Admin" & Chr$(0) & "pwd=" & Chr$(0)
            strAttributes = Attributes & "DBQ=" & pDBPath & Chr$(0)
        Case eSQL_SERVER
            strDriver = "SQL Server"
            strAttributes = "SERVER=" & p_strServer & Chr$(0)
            strAttributes = strAttributes & "DESCRIPTION=" & Chr$(0)
            strAttributes = strAttributes & "DSN=" & p_DSNName & Chr$(0)
            strAttributes = strAttributes & "DATABASE=" & p_strDatabase & Chr$(0)
            strAttributes = strAttributes & "UID=" & p_strUser & Chr$(0)
            strAttributes = strAttributes & "PWD=" & p_strPassword & Chr$(0)
              
    End Select
 
    lngRetVal = SQLConfigDataSource(0, p_DSNType, strDriver, strAttributes)
    
    'If the Return Value 1 then
    'DataSource is created succesefully
    'otherwise it has falied
    CreateDSN = lngRetVal
End Function

Then you can call it like this:

VB Code:

Private Sub Command1_Click()
    Call CreateDSN("YourDSN", eSystemDSN, eSQL_SERVER, "YourDatabase", "ServerName", "sa")
End Sub

Thanx,
it is working for local database.

for rempote database what r the attributes to be add.

suppose the remote machine ip =207.100.100.100
server= server1
database=data1
uid=uid1
pwd =pwd1

I can create the dsn manually for remote database what I am doing is for in dsn creation wizard in client configuration I am giving as server 201.100.100.100

before that I am giving server as server1 and network library tcp/ip checked.

Now I need to create that with my vb program coz I can not go to all my clients place and create it manually.

Please help me in this regard.

Thanx in advance

Just change the server name to use the IP instead.

VB Code:

Private Sub Command1_Click()
    Call CreateDSN("YourDSN", eSystemDSN, eSQL_SERVER, "YourDatabase", "207.100.100.100", "UserName", "YourPassword)
End Sub