Here is some ODBC API code you can use behind a form to refresh your
ODBC DSN, and THEN relink your table(s). Just call the sub from a Form
button. This will create a new DSN or overwrite an existing DSN on the
workstation where this code is called from. It is the same as
refreshing the DSN (if the dsn already exists).
----------------------------------------------------------
'Constant for a string variable type.
Private Const REG_SZ = 1
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult
As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Sub createSystemODBCdsn()
'Microsoft Knowledge Base Article - 184608
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim lResult As Long
Dim hKeyHandle As Long
'Specify the DSN parameters.
DataSourceName = "NameYouWantForYourDSN"
DatabaseName = "NameOfSqlServerDB"
Description = "<a description of the new DSN>"
DriverPath = "C:\WINNT\System32\SQLSRV32.DLL"
LastUser = "SA"
Server = "YourSqlServerName"
DriverName = "SQL Server"
'Create the new DSN key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)
'Set the values of the new DSN key.
lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))
'Close the new DSN key.
lResult = RegCloseKey(hKeyHandle)
'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, ByVal
DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)
End Sub
----------------------------------------------------------
----------------------------------------------------------
Then to re-link your tables
---------------------------------------------------------
---------------------------------------------------------
Sub Relink()
Dim DB as DAO.Database, tdf As TableDef
DoCmd.RunSql "Drop Table yourODBCtbl"
Set DB = CurrentDB
Set tdf = DB.CreateTableDef("YourODBCtbl")
tdf.Connect = "ODBC;DSN=yourDSNname;UID=sa;PWD=;DATABASE=SqlServ erDB"
tdf.SourceTableName = "dbo.TblNameOfSqlServerDBtable"
DB.TableDefs.Append tdf
--------------------------------------------------------
I don't have Access2003 so not sure if Sub Relink() will work as written
above here. It should since ODBC is kind of legacy technology (old tech
- use old coding). You might consider migrating to ADO and stored
procedures. The performance gain will be like if you have an ODBC
procedure that takes 45 minutes to run, it will take 500 milliseconds to
run with ADO and a stored procedure on sql Server (assuming your server
has at least 4 gigs of memory and multiple processors - well, maybe 5 -
10 seconds, but that's better than 45 minutes - been there).
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
