472,958 Members | 1,583 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

DSN Nightmare

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


Hope someone can help


Nov 12 '05 #1
1 2094
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.

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Drisco | last post by:
Hello, I realy need help,Searched the NG for a days with no vail. Sorry for the long script. I am trying to get a variable from php to flash. I have the following code in php <a...
by: Deena | last post by:
Hi Windows forms layout is turning to be a nightmare. I add my controls to a form and then save it and then it just get's all messed up. I'm using inheritance if this makes any difference. Also...
by: Dave | last post by:
Hello All, I am having a nightmare trying to add a new row to my Datagrid. When I use the code below I get the error: 'Invalid CurrentPageIndex value. It must be >= 0 and < the PageCount. Can...
by: Jim Mirra | last post by:
Hey, I have been working on this for a while now and I keep getting the same error. here is my code: ------- start code --------- Dim strSQL = "Select tagid,itemid,description,status,...
by: Afshar | last post by:
Dear nice people around the world! Please help ending this nightmare, I have 2 problem with ASP.NET Menu control First: I have a horizontal menu at top of my page, each item have some sub-items...
by: Samuel Shulman | last post by:
I get the following error Error 2 Could not load file or assembly 'RusticiSoftware.ScormContentPlayer.Logic, Version=2006.1.2576.40320, Culture=neutral,...
by: Andrew Wan | last post by:
I have been having a nightmare with ASP/ASP.NET & IIS6. We use Msxml2.DOMDocument.4.0 object to create a XML object in ASP. The Msxml2.DOMDocument.4.0 is from the Windows Platform SDK Feb 2003...
by: =?Utf-8?B?TWVyZGFhZA==?= | last post by:
I am using ASP.NET 2.0 C# I am getting errors when I publish my site. It works fine on our local server. Pages that have maintainscrollpositiononpostback=true set, get errors such as:...
by: Phillip Taylor | last post by:
I'm having an absolute nightmare with regular diff tools which is making it really hard to manage changes etc in CVS. Tools such as WinMerge and ViewCVS really have really poor matching results...
by: Widge | last post by:
I'm having a nightmare. I've used this technique before and can't understand why I'm getting an error using it now. Basically I have a form, on that is a sub form datasheet. What I am eventually...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.