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.
| 