470,872 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,872 developers. It's quick & easy.

DSN Nightmare

Dan
Hi I have recently converted an Access 97 database to 2003. Most of
the change went fine with no problems but every now and then my DSN
connections to my back-end SQL-database fail and it does not allow me
to add new records to the table anymore. I have to delete the tables
and re-link them to get it working again. Does anyone know why this
happens and how to stop it??? The ability to add records also gets
lost when i change the server(through code)i am working on, I use this
connection string to reattach all my tables and queries once the
server has been changed

"ODBC;DSN=CRMDBPRD;APP=Microsoft Office
2003;UID=reporter;DATABASE=WORKGRPS"

Hope someone can help

Cheers

Dan
Nov 12 '05 #1
1 1943
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!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Drisco | last post: by
reply views Thread by Deena | last post: by
4 posts views Thread by Dave | last post: by
6 posts views Thread by Jim Mirra | last post: by
3 posts views Thread by Samuel Shulman | last post: by
1 post views Thread by =?Utf-8?B?TWVyZGFhZA==?= | last post: by
2 posts views Thread by Phillip Taylor | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.