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