DB Connection

[DayWalker]

I was wondering if anybody could assist me. I have a windows app and it is connecting to a SQL db. Now the server name is not the same in all the locations. And it will be a huge task to redo the connection string for each location just to change the server name. Is there a way that i can make the app search the network for a server (kinda like sql server service) or by using the app.config somehow?

[Travis]

You could use a DSN. You can standardize the name of the DSN, but have the connectivity set up differently for each place this application is deployed.

 

 

Here is a sample of a Module that I use to create a DSN automatically. You could setup a form to enter the parameters needed. (This uses NT Security so if you want to use the SQL security you will need to tweek the connection string)

 

 

 

Option Explicit

 

'Constant Declaration

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

 

'Function Declare

#If Win32 Then

 

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

#Else

Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _

(ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _

lpszDriver As String, ByVal lpszAttributes As String) As Integer

#End If

 

Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, pcbDescription%) As Integer

Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)

Const SQL_SUCCESS As Long = 0

Const SQL_FETCH_NEXT As Long = 1

 

'DSN's Name

Public Const strDSN = "CSClaimSystem"

'DSN's Description

Public Const strDSNDescription = "Data Link to the CS Claim System Database"

'Database to link to

Public Const strDatabase = "CSClaimSystem"

 

Public Sub MakeDSN()

'Creates the DSN

On Error Resume Next

 

#If Win32 Then

Dim intRet As Long

#Else

Dim intRet As Integer

#End If

 

Dim strDriver As String

Dim strAttributes As String

 

'Set the driver to SQL Server because it is most common.

strDriver = "SQL Server"

'Set the attributes delimited by null.

'See driver documentation for a complete

'list of supported attributes.

 

strAttributes = "SERVER=CORPMISS01412N4\SQLSERVER1" & Chr$(0)

strAttributes = strAttributes & "DESCRIPTION=" & strDSNDescription & Chr$(0)

strAttributes = strAttributes & "DSN=" & strDSN & Chr$(0)

strAttributes = strAttributes & "DATABASE=" & strDatabase & Chr$(0)

 

'Sets the DSN to use NT Security

strAttributes = strAttributes & "Trusted_Connection=Yes" & Chr$(0)

 

'To show dialog, use the Hwnd instead of vbAPINull.

intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _

strDriver, strAttributes)

If intRet Then

'MsgBox "DSN Created"

Else

MsgBox "DSN Create Failed", vbCritical + vbOKOnly, "Contact MIS"

End If

 

End Sub

 

Public Sub RemoveDSN()

'Removes the DSN

On Error Resume Next

#If Win32 Then

Dim intRet As Long

#Else

Dim intRet As Integer

#End If

Dim strDriver As String

Dim strAttributes As String

 

'Set the driver to SQL Server because most common.

strDriver = "SQL Server"

'Set the attributes delimited by null.

'See driver documentation for a complete list of attributes.

strAttributes = "DSN=" & strDSN & Chr$(0)

'To show dialog, use Form1.Hwnd instead of vbAPINull.

intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _

strDriver, strAttributes)

If intRet Then

'MsgBox "DSN Deleted"

Else

MsgBox "DSN Delete Failed", vbCritical + vbOKOnly, "Contact MIS"

End If

 

End Sub

 

Public Function CheckForDSN() As Boolean

'Checks for the existance of the DSN

On Error Resume Next

Dim i As Integer

Dim sDSNItem As String * 1024

Dim sDRVItem As String * 1024

Dim sDSN As String

Dim sDRV As String

Dim iDSNLen As Integer

Dim iDRVLen As Integer

Dim lHenv As Long 'handle to the environment

 

CheckForDSN = False

 

'get the DSNs

If SQLAllocEnv(lHenv) <> -1 Then

Do Until i <> SQL_SUCCESS

sDSNItem = Space$(1024)

sDRVItem = Space$(1024)

i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, iDSNLen, sDRVItem, 1024, iDRVLen)

sDSN = Left$(sDSNItem, iDSNLen)

sDRV = Left$(sDRVItem, iDRVLen)

 

If sDSN <> Space(iDSNLen) Then

If sDSN = strDSN Then

CheckForDSN = True

Exit Do

End If

End If

Loop

End If

 

End Function

 

Public Function AttachTables() As Boolean

On Error GoTo ErrorHandler

 

AttachTables = False

 

'You can add the tables to link to an Access Table an loop through the recordset

'Or write a DoCmd.TransferDatabase for each table to link.

 

Dim rst As New ADODB.Recordset

Dim cnn As New ADODB.Connection

Dim cmd As New ADODB.Command

 

cnn.Open "DSN=" & strDSN

cmd.CommandText = "up_ListOfTables"

cmd.CommandType = adCmdStoredProc

cmd.ActiveConnection = cnn

Set rst = cmd.Execute

rst.MoveFirst

Do While Not rst.EOF

DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & strDSN & ";UID=;PWD=;LANGUAGE=us_english;" _

& "DATABASE = " & strDatabase, acTable, rst.Fields(2), rst.Fields(2)

rst.MoveNext

Loop

 

 

AttachTables = True

 

Exit Function

ErrorHandler:

AttachTables = False

Debug.Print Err.Number & " " & Err.Description

 

End Function