You can use Visual Basic to create ODBC Data Sources Names (DSNs) dynamically for MS SQL Server. Simply place this line in a module:
Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent AsLong, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVallpszAttributes As String) As LongFunction PrepareDSN(ByVal strServerName As String, ByVal strDBName As String,ByVal strDSN As String, ByVal strDBUser As String, ByVal strDBUserPassword AsString) As BooleanOn Error GoTo error_hdl Dim boolError As Boolean Dim strDSNString As String PrepareDSN = False strDSNString = Space(MAX_BUFFER_SIZE) strDSNString = "" strDSNString = strDSNString & "DSN=" & strDSN & Chr(0) strDSNString = strDSNString & "DESCRIPTION=" & "DSN Created Dynamically On " & CStr(Now) & Chr(0) strDSNString = strDSNString & "Server=" & strServerName & Chr(0) strDSNString = strDSNString & "DATABASE=" & strDBName & Chr(0) strDSNString = strDSNString & Chr(0) If Not CBool(SQLConfigDataSource(0, _ ODBC_ADD_DSN, _ ODBCDriverDescription, _ strDSNString)) Then boolError = True Msgbox("Error in PrepareDSN::SQLConfigDataSource") End If If boolError Then Exit Function End If PrepareDSN = True Exit Functionerror_hdl: msgbox "PrepareDSN_ErrHandler::" & Err.DescriptionEnd Function
