[AccessD] OT - Changing a DSN Connection

Hi Paul

Here's the basics:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlconfigdatasource.asp

and here is example code where you could enter the modified name of the server into the connect string:

<code>

  Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( _
    ByVal hwndParent As Long, _
    ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) _
    As Long

  ' Add user DSN.
  Private Const ODBC_ADD_DSN              As Long = 1
  ' Configure user DSN.
  Private Const ODBC_CONFIG_DSN           As Long = 2
  ' Remove user DSN.
  Private Const ODBC_REMOVE_DSN           As Long = 3
  ' Add system DSN.
  Private Const ODBC_ADD_SYS_DSN          As Long = 4
  ' Configure system DSN.
  Private Const ODBC_CONFIG_SYS_DSN       As Long = 5
  ' Remove system DSN.
  Private Const ODBC_REMOVE_SYS_DSN       As Long = 6
  ' Remove the default DSN.
  Private Const ODBC_REMOVE_DEFAULT_DSN   As Long = 7
  
  Private Const ODBC_ERROR_GENERAL_ERR             As Long = 1
  Private Const ODBC_ERROR_INVALID_BUFF_LEN        As Long = 2
  Private Const ODBC_ERROR_INVALID_HWND            As Long = 3
  Private Const ODBC_ERROR_INVALID_STR             As Long = 4
  Private Const ODBC_ERROR_INVALID_REQUEST_TYPE    As Long = 5
  Private Const ODBC_ERROR_COMPONENT_NOT_FOUND     As Long = 6
  Private Const ODBC_ERROR_INVALID_NAME            As Long = 7
  Private Const ODBC_ERROR_INVALID_KEYWORD_VALUE   As Long = 8
  Private Const ODBC_ERROR_INVALID_DSN             As Long = 9
  Private Const ODBC_ERROR_INVALID_INF             As Long = 10
  Private Const ODBC_ERROR_REQUEST_FAILED          As Long = 11
  Private Const ODBC_ERROR_INVALID_PATH            As Long = 12
  Private Const ODBC_ERROR_LOAD_LIB_FAILED         As Long = 13
  Private Const ODBC_ERROR_INVALID_PARAM_SEQUENCE  As Long = 14
  Private Const ODBC_ERROR_INVALID_LOG_FILE        As Long = 15
  Private Const ODBC_ERROR_USER_CANCELED           As Long = 16
  Private Const ODBC_ERROR_USAGE_UPDATE_FAILED     As Long = 17
  Private Const ODBC_ERROR_CREATE_DSN_FAILED       As Long = 18
  Private Const ODBC_ERROR_WRITING_SYSINFO_FAILED  As Long = 19
  Private Const ODBC_ERROR_REMOVE_DSN_FAILED       As Long = 20
  Private Const ODBC_ERROR_OUT_OF_MEM              As Long = 21
  Private Const ODBC_ERROR_OUTPUT_STRING_TRUNCATED As Long = 22
  
  Private Const SQL_SUCCESS            As Long = 0
  Private Const SQL_SUCCESS_WITH_INFO  As Long = 1
  Private Const SQL_NO_DATA            As Long = 100
  Private Const SQL_ERROR              As Long = -1
  Private Const SQL_INVALID_HANDLE     As Long = -2
  Private Const SQL_MAX_MESSAGE_LENGTH As Long = 512
 
  ' Nul pointer.
  Private Const vbAPINull                 As Long = 0


Private Sub ConfigDSN() 

' Examples of driver names:
'   SQL Server
'   MySQL ODBC 3.51 Driver
'   Microsoft ODBC for Oracle
'
' Example of connection string:
'   DSN=MySQL Test;DESC=MySQL ODBC 3.51 Test;DATABASE=Test;SERVER=192.168.1.100;USER=root;PASSWORD=password;PORT=3306;SOCKET=;OPTION=3;STMT=;
'
    Dim strDriver As String 
    Dim strAttributes As String 
    Dim lngRet As Long 

    strDriver = "SQL Server" & vbNullChar
    strAttributes = "<YourModifiedConnectString>" & vbNullChar
    lngRet = SQLConfigDataSource(Me.hWnd, ODBC_CONFIG_DSN, strDriver, strAttributes) 

End Sub

</code>

This should get you started.

/gustav

>>> paul.hartland at isharp.co.uk 05-01-2006 14:21 >>>
To all,

We are having a new server (very soon) and changing the name to something
like AL-MASTER from AL-ORR the thing is I have a lot of Excel management
reports which use various DSN's pointing to AL-ORR, is there something I
could write in Access or VB that a a click of a button will find these DSN's
and replace the server name ?

Any help on this would be greatly appreciated.

Thanks in advance 

Paul Hartland

P.S. Happy New Year to all list members, don't know what I would do without
it sometimes