making a system dsn w/ api

[Nickey]

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

[Serge]

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:

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.

[Nickey]

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

[dasari999]

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

[Nickey]

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..

[dasari999]

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

[Serge]

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

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:

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

[dasari999]

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

[Serge]

Just change the server name to use the IP instead.

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