Microsoft Access Office VB VBA Help and Examples
           
             

Rollout ODBC Settings quickly

You've built your database and its out in use. But suddenly the DBA team inform you of a new set of ODBC settings as your back end data needs to move.

Well you could visit each user and change the settings manually. Heck why not use one of those nifty ODBC add-ins or the such.

Anyone who knows me knows whats coming next....Lets cheat and just jump straight into the reigstry!!!!!!!!

OK here goes.....

  • First off make the new ODBC connection on your computer
  • Open registry editor (regedit.exe or regedt32.exe) and browse to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
  • Highlight ODBC.INI in the left hand side and choose file - > export
  • Make sure you have Branch selected and save the file somewhere. Remember the name.
  • Close registry editor
  • Open the file that got exported...see mine below... in notepad
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"WebDev"="SQL Server"
"Prd_CorpEventsWeb"="SQL Server"
"SEEMS"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC File DSN]
"DefaultDSNDir"="C:\\Program Files\\Common Files\\ODBC\\Data Sources"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Prd_CorpEventsWeb]
"Driver"="C:\\WINDOWS\\System32\\sqlsrv32.dll"
"Description"="WebDev Database"
"Server"="spri11501"
"Database"="d_dbmdc001"
"LastUser"="John Hawkins"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SEEMS]
"Driver"="C:\\WINDOWS\\System32\\sqlsrv32.dll"
"Description"="SEEMS Smiley Copy"
"Server"="XP-HOME"
"Database"="Seems"
"LastUser"="sa"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\WebDev]
"Driver"="C:\\WINDOWS\\System32\\sqlsrv32.dll"
"Description"="WebDev Database"
"Server"="137.34.200.72"
"Database"="d_dbmdc001"
"LastUser"="John Hawkins"
  • Ok note there are three ODBC connections listed...we need to strip it down to the one we are interested in. In this case the one titled SEEMS.
  • This leaves us with the below file
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"SEEMS"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SEEMS]
"Driver"="C:\\WINDOWS\\System32\\sqlsrv32.dll"
"Description"="SEEMS Fabalou"
"Server"="XP-HOME"
"Database"="Seems"
"LastUser"="sa"
"Trusted_Connection"="Yes"
  • Save the new file (your will be different depending on the odbc connection) and then go into the access database in question. Note you must save this file on a server  / share the users can access
  • You need to call the following code somewhere when the database first opens before it does anything with the tables.
Public Sub Check_DSN()
   On Error Resume Next
   Err.Clear
   Dim x As String
   Dim i As Long
   'Change the following line to reflect a field and table that is linked with the new odbc settings
   x = DMax("field Name", "table name")
   If Err.Number <> 0 Then 'User does not have DSN installed for SEEMS
      'Change the following line to reflect the location your reg file is saved in
      Shell "regedit /s \\Server\share$\seems.reg", vbMaximizedFocus
      For i = 1 To 200 'give a pause so registry can catch up
         DoEvents
      Next
   End If
   On Error GoTo 0
   Err.Clear
End Sub

Thats it. Roll it out and if the users cannot open the dmax statement for the linked table then the code will run and install the new reg settings then continue. For some systems it may require coming out of the database and back in to get the new odbc settings working.

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.