Access 2007 Connectivity Issues With SQL Server 2008 (ODBC Error 3151)

Hidden or "ghost" (or "leftover") breakpoints should never happen, and are often an indication of corruption or troubles in your database. In  cases like this, I always recommend that you do the following:

1) Make a backup
2) Compact: Click the Office Button - Manange - Compact
3) From the VBA EDitor, click Debug - Compile. Fix any errors. Compile again. Continue doing so until the menuitem is disabled (which means it's compiled).
4) Compact again
5) Build a new, blank database. Import everything into it.
6) Debug - Compile again
7) Compact again

If you have issue importing into a new database, you may have a corrupt object (although that's not what it sounds like here). If so, you'd have to determine which object is giving you troubles (normally the import wizard will hang on that object), and then take action to determine what to do with that object - either rebuild it, restore from a recent backup, etc.

Hi,

I've seen this "Hidden breakpoint" issue before.  It is caused when abreakpoint is set then the code is changed, perhaps deleting the breakpoint line.

A fix to the breakpoint issue is to design a module, click Debug, Clear all Breakpoints, recompile and save.

Regards,

Bill

Bill is correct, in that there is a method to "Clear All Breakpoints", but this doesn't resolve the issue of WHY those hidden breakpoints are occurring. My point is this: If you are experiencing hidden or "ghost" breakpoints, there is a problem with the database. This is not normal, expected behavior, and anytime that occurs your best bet is to immediately move everything to a new container (after making a backup, of course).

>DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle (0)

Looks as it would like to ask for user/password but has no password dialog.

Can you post the connect code?

Thanks for the comments,

I tried importing all the objects into a new container and resetting the references etc, however the problem persists.

In regards to the breakpoints, Access can get confused on breakpoints when they are set while debugging running code.  I forget the exact sequence to causes the problem, but it is common and well know in my opinion, and I can successfully clear the hidden breakpoints when they occur.

I guess another way to put it is if the application does not go into debug mode before reaching certain parts of the application I get the Error 3151 caused by the ODBC error DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle (0).  It is almost like going into debug mode causes additional things to load or something that prevents the error from occuring.

So it works fine if I either get one of the hidden breakpoints in my code, or if I intentionally create a syntax error that causes the application to go into debug mode.  However this obviously is not a solution, just a puzzling way to get it to work.

In regards to the question from js-profi:

At the beginning of the application drop and re-link all of the SQL Server tables based upon configuration settings.  The function below creates the missing links based upon a local Access table "Network Table".  Note I recently added the pintMode option so that I could try relinking or refreshing the links when the error occurs and the pintMode = 3 RefreshLink code does run once the error occurs (Note there is no OpenRecordset method used in the mode 3 option on a Non-Access table), however I've since not used the refreshlink logic since it did not resolve my problem.

One reason the data was moved to SQL Server was due to data security.  Users are not allowed to gain direct access to the data and since Access 2007 removed the security workgroup options for accdb, it was decided to use SQL Server.  So a derived connection string with user name and password is passed into the pstrConnect variable for connections and the user name and password are not stored with the link, however the fact that we are relinking at the start of the application every time, automatically logs into the server and creates the SQL Server connection without user prompts and passwords are not left behind in the application in plain text.

The Derived Connect String is like:
ODBC;Driver={SQL Server Native Client 10.0};Database=MyDatabaseName;UID=MyUserName;PWD=MyPassword;SERVER=MyServerNameOrIPAddress\MyDatabaseInstanceName;APP=MyAppName;

Private Function funcCreateMissingLinks(pstrConnect As String, Optional pintMode As Integer) As Boolean
    Dim strLinkName As String
    Dim strSourceTableName As String
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim dbsDb As DAO.Database
   
    On Error Resume Next
    Set dbsDb = CurrentDB
    Set rst = dbsDb.OpenRecordset("NetworkTable")
    rst.MoveLast
    rst.MoveFirst
    Do While rst.EOF = False
        strLinkName = rst.Fields("NetworkTable").value
        strSourceTableName = "dbo." & strLinkName
       
        Set tdf = dbsDb.TableDefs(strLinkName)
        If pintMode = 3 And Err.NUMBER = 0 Then
            tdf.RefreshLink
        Else
            If Err.NUMBER = 0 Then
                dbsDb.TableDefs.Delete strLinkName
                dbsDb.TableDefs.Refresh
            Else
                ' Ignore error and reset
                Err.NUMBER = 0
            End If
            Set tdf = dbsDb.CreateTableDef(strLinkName)
            tdf.Connect = pstrConnect
            tdf.SourceTableName = strSourceTableName
            dbsDb.TableDefs.Append tdf
        End If
        rst.MoveNext
    Loop
   
    Set tdf = Nothing
    rst.Close
    Set rst = Nothing
    dbsDb.Close
    Set dbsDb = Nothing
    funcCreateMissingLinks = True
   
End Function


The failing OpenRecordset is similar to the rest in the application and looks like this:

Dim dbsDb As DAO.Database
Dim rstData As DAO.Recordset
Set dbsDb = CurrentDB

Set rstData = dbsDb.OpenRecordset("LinkedTableName", dbOpenDynaset, dbSeeChanges)

So perhaps there is something wrong with the "LinkedTableName"? Well I can still open the linked table through the Navigation Pane with no problems, I just can't seem to reference it in code.

The ACE also appears to be making 3 separate connections to SQL Server if that makes any difference.

Hope this helps.

Thanks

Hi,

I use this connection method for DSNLess connections to SQL and it does not time out.  It is also SQL version independent.

Dim vCon as String
vCon = "ODBC;Driver={SQL Server};NETWORK=DBMSSOCN;UID=XXXX;PWD=YYYY;SERVER=ZZZZ;DATABASE=AAAA;"

DoCmd.DeleteObject acTable, "TTTTT"
DoCmd.TransferDatabase acLink, "ODBC", vCon, acTable, "dbo.TTTT", "TTTT", True
...

Bill

why do you need dropping and relinking? you should be able to have/use multiple connections to same database. if you are not busy close the connection rather than let it timeout.

Hi,

I had similar problems with SQL Server 2005 in the standard installation (Enterprise version) which most of the time opened the connections without any problems and then refuses to accept the connection - and after a while it worked again without any problems. I searched for some weeks to find a solution and tried any connection string and code problems I could test and find and finally it was a simple setting on the server which is a Windows 2003 setting to stop attacks with too many and too fast connections.

Here are the settings I found at last which solved any problem:

TCP Settings:
In Registry Key "HKLM\System\CurrentControlSet\Services\Tcpip\Parameters"
EnableRSS DWORD 0
EnableSecurityFilters DWORD 0
EnableTCPA DWORD 0
EnableTCPChimney DWORD 0
SynAttackProtect DWORD 0

And the "Maximum worker threads" setting was set to 288 instead of 0 (8 CPU
server, can be different on other machines having more or less CPUs, see
here:
http://www.generation-nt.com/us/redirect.html?url=A2wAcVUnBnEHaFUoDCxXOQJxUTNWPQcvV2kCPAYwA30FbQVzXTQBYAMiUy8KZgRoUj9QeVMzBGwOcFV3VHIHJANoAGxVMQZzBzNVdQx6V3sCb1EkVmIHOVczAmUGYQM7BSwFYV0oAXYDLlMnCnQEclI9UCJTbQ%3D%3D

After setting these values on the server and the server's Windows all
problems were gone (rebooting of Windows is necessary).

The complete discussion about that problem and all my tries can be found here:

http://www.generation-nt.com/us/answer/access-2007-sql-server2005-connection-was-forcibly-closed-gne-1-help-30322472.html?page=1

Maybe there are additional new settings in Windows Server 2008 and SQL Server 2008.

Cheers,

Christian

Thanks for your suggestion Bill.

It tried both methodologies and the issue still persists.

As additional information.  While troubleshootig, it does not appear that opening a specific recordset is causing the issue.  I stepped through the code to where a failure was occurring and there happened to be two recordsets opened one after the other for comparison purposes.  The first one opened successfully, but the second did not.  So I decided to switch the sequence in which the recordsets were opened to see if there was a difference.  Turned out that regardles of the recordset, the second one to open always fails so the issue is not related to a specific table, but rather some threshold being hit where Access gives up?

I did find that if I change the recordset type from DAO to ADO it tended to work around the issue, but I have hundreds of DAO recordset deffinitions in the application and DAO is suppose to work so I would like to get DAO to work than recode the guts of the application.

I did not see the additional comments until just now.

js-profi:
I need to relink all tables on startup because the application is designed to be able to talk to multiple SQL Server Instances based upon configuration settings that are read at startup.  Granted the destination database is the same, but the host of the individual database tables may vary.
I agree that multiple connections to the same database is fine.  I was not aware that I can tell the ACE to close a connection via the DAO.  I was at one point trying to find a way to see what connections the ACE was holding open so I could associate them with a specific SQL Server SPID, however it was not obvious to me how to accomplish this.
The issue I had with allowing ACE to close the connections automatically after the default 600 seconds is that once I tried to open a recordset via VBA after the timeout had occurred, Access was always generating an ODBC error.  Forcing the ACE to keep the connections open until the application is closed worked around this issue.

Bitsqueezer:
I will need to digest your comment a bit more, but it appears that you were actually having the connection closed with SQL Server.  I have traced the sessions with Profiler and when the error occurs, the connections are not dropped at all and there is no Audit Logout.  In fact the SPIDs stay active until I actually close the application.  Again, I will review your reference, but it appears I am experiencing something different and I suspect it is somewhere in the ACE layer.

Thanks for all the ideas!  I did just install the application on a different machine using the runtime and am experiencing the same behavior, granted it was another Windows 7 64 bit machine.

Mark

what is the role of access in the current scenario? do you still work with access tables linked to sql server dbms? if so, where are those connections defined?

regarding using an already defined connection in VBA: don't know much of VBA. In C++ you would/could use the pointer to CDatabase object representative for connection. Generally, ODBC has a handle HDBC for each connection. The CDatabase class is not much more than a wrapper class for that handle. By using the same handle eg. for getting a new statement handle HSTMT you were using the same connection.

For the record, ADO is a much preferred choice over DAO when dealing with server-type data. DAO can work, but I've heard of too many people running into serious issues (both performance and otherwise) when choosing DAO to hit server data. I understand your desire to keep this in DAO, but I believe you'd be doing yourself and your users a disservice if you didn't at least consider the option of moving to a more correct data access library.

I prefer DAO over ADO. ADO has all advantages and disadvantages of a COM solution what makes it much more complex and error-prone (especially when using it the first time). It is not more correct than DAO. one shouldn't mixup (possible?)  performance benefits with correctness. often performance issues come from bad design.

<I prefer DAO over ADO>

And that is certainly your preference. I can confidently say, and I presume you would agree, that the overwhelming majority of developers prefer ADO when working with server-based data, as it generally provides greater benefits over DAO in that regard. That's not true with Access-based data, of course, as DAO is very tightly integrated with JET/ACE. I'm not sure I would agree that it is more error-prone, but to each his own I suppose.

DAO has a much more complex object model than does ADO (which basically consists of Connection, Command and Recordset objects). This can make working with it somewhat difficult if you're not well-versed in that object model.

DAO doesn't really handle large recordsets well, if that's an issue.

<often performance issues come from bad design.>

Very true. Given no design issues, however, when connecting to server-based data, performance will generally be better with ADO than DAO.

My point being: if you're working with server-based data, it might be time to consider moving to ADO, given that ADO is much more oriented to that platform than is DAO.

Just my .02 worth ;)

I agree to LSMConsulting, in any case where an external database especially SQL Server is included I would always prefer ADO. I had the experience to improve a database which was originally written as a pure Access database written with DAO. It was moved to SQL Server and during the time I found a lot of strange problems DAO produces with the SQL Server especially with fields longer than 256 characters and much more problems.
I moved it completely to ADO and that was very much better, in performance, in ease of use and working with SQL Server datatypes.
But, and that is for the author of this thread: Yes, you may have a lot of DAO code in your Access now and sure it will be a lot of work to switch to ADO but I if I were you would go on to one step ahead DAO->ADO (which I have successfully done with the above mentioned database): Create a new frontend based on an Access project (which means ADP instead of ACCDB). This has the big advantage that you work DIRECTLY with the SQL Server, no Jet in between, no problems with datatype and the best: Never again any ODBC problem. You remove a lot of error possibilities between your frontend and the SQL Server and as a benefit you will get a LOT more performance (more than only using ADO instead of DAO). Another advantage is that ALL forms and reports and so on works ONLY with ADO, in a project you can NOT use DAO anymore (it is not possible to set a reference to DAO, Access removes it automatically, if you do it manually it cannot be successful used).
You never need any relink to any server - that's a good thing, too - because you set up the main server right at the beginning in the SQL Server settings of the project.
Now you have the need that you must access different servers or for example a simple Excel file opened as database. This is NO problem in an Access project because you only need to open an ADO recordset for example in the Form-Load-event and then apply it to the form's recordset property - on this way you can set any external database including MDBs, Excel files or whatever can be opened with ADO (nearly everything but WorldOfWarcraft...grin....).

You will never get any ODBC timeout because there is no ODBC in an Access project. I deployed the mentioned database some weeks ago and all previous problems with the ACCDB version before were gone. One of the best things can be seen if you use the following product (it's free like everything coming from SysInternals):

http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx

This little tool shows you what an Access database does with the SQL Server: It opens one connection for EVERY open table and anything else you do! Start the tool and go to the navigation bar of Access and then open all the linked tables in a short time as fast as you can open them, then go to the tool and look what's open: You will see one connection for each table and exactly this issue was the problem with my above posting with the SQL Windows 2003 Server: Windows thinks that this is a try to hack the server with too much connections and this setting is new to W2003 with SP1 and higher as a standard setting. The registry entries above avoids this - but W2003 server will not try to stop hacks with these settings!
If you switch to Access project and try the same you will see that there are a maximum of 3 to 5 connections to the server because all open tables uses the same connection and only your code is responsible for further connections which should be closed after using. With ADP you can leave the Windows settings as they are because there will be no new connection if you open a form which is bound to a table.

But back again to the problem. I analyzed your above code a little bit and see some problems here. First of all, if you want to create really stable programs with Access, NEVER use "On Error Resume Next" if it is not ABSOLUTELY necessary. In 99.9% there is no need to use this because you can always use an error handler which can (if needed) use "Resume Next" to jump to the next command. The "On Error Resume Next" not only catches the error at the place where you check the error state but at every other line, too, so you never know if there is a problem somewhere else in your code.
Next is, after opening the recordset you do not test if this was successful, the "On Error Resume Next" catches any erroneous access to the rst object in for example "rst.movenext" and any other command. Instead you should first test if the recordset object is nothing to be sure that the opening was successful. In a new if you then must test if the recordset is empty (you can't do that in one IF because unfortunately any IF command tests ALL conditions so if the recordset object is nothing it would produce an error if you test if it is empty in the same line - in VB.NET you can use "AndAlso", this is not possible here). The test can be made with testing on BOF and EOF together (both are true if the recordset is empty).
Only after these tests you can go on using the recordset object - and what I said is not only for this code but for any other place in your code where you used this sort of construct.
At the end, of course, you need to close the recordset and that should not be done with "rst.close - set rst=Nothing" because this will produce errors (which you as always catch with the "On Error Resume Next"). Test it in the right way and close it in ANY function which opens a recordset always at the end of the function/sub like this:

If Not rst Is Nothing Then
   rst.Close
   Set Rst=Nothing
End If

If the recordset in DAO is not open the Close statement will produce an error which you can catch in the error handler (NOT with "On Error Resume Next").
Here you see again a big advantage in ADO:

If Not rst Is Nothing Then
   If rst.State = adStateOpen Then rst.Close
   Set Rst=Nothing
End If

This will close in any way without getting an error.
Next is "MoveLast" and "MoveFirst" - if you execute this Access will immediately load ALL records from the table into the recordset. This CAN be better in case of large dropdown lists in a form (where you can reach the same with "ListCount") and in endless forms with a big number of records so the resources on the server are not blocked to other users while loading the rest of the records in a slow way (what Access in ACCDB normally does). It is not needed if you step through all records of a recordset in a loop, only "MoveFirst" can be a good idea.
Next is "OpenRecordset" - you open it without specifying the open type. That is OK in quick and dirty programming if you just want to check something in a fast written simple sub. But NEVER do that in an application, you should ALWAYS explicitly set the right connection type because you are working in a multi user environment with SQL Server.
As you only want to loop once through the table here you should write here "OpenRecordset("NetworkTable",dbOpenForwardOnly,dbReadOnly)". Otherwise your table is opened as dynaset which means it is fully read/write accessible which is not needed here and can produce problems if the server locks the record you are currently using. If the application crashes the server needs to resolve the lock on it's own which since 2005 is possible but it takes time during the other users maybe get in trouble while accessing it.
Working with SQL Server needs a LOT more discipline as using a simple Access database so check your code at all places exactly and remove any "On Error Resume Next" and add a correct error handler to all subs and functions.

The error 3151 is a general ODBC error not specifying the exact problem so maybe it is a good idea not only to check all the Windows and SQL Server error logs but to additionaly link your table through an external ODBC connection (for test purposes) and enable the ODBC logging in the ODBC settings of Windows which will produce a fast growing ODBC log file similar to the SQL Profiler but on the client side. This CAN give you an idea where the problem is.
And pleaaase...remove any "On Error Resume Next"...(did I say it already?...:-).....).

Cheers,

Christian

Thanks for all the comments.  I will look into them more thoroughly tonight since I am currently a SQL Server DBA during the day and an Access Programmer at night :)

During my lunch break here I did want to add one more observation that the application seems to run fine in a Windows Virtual PC of Windows XP SP3, but I am consistently running into the issue on two different Windows 7 64-bit machines.  I have not had the opportunity to test on Windows Vista machines since I upgraded most of those to Windows 7, but perhaps I'm running into a 64-bit/32-bit issue.  I am going to try my Windows 7 32-bit machine tonight and see if the problem is reproducible.

Just an observation made late last night...

I agree that On Error Resume Next is not ideal.  When I took over this project it had practically no error handling other than On Error Resume Next.  I added error handling to ALL the regular application code,  except I never did the application startup functions that are in a separate module, hence the old On Error Resume next at the beginning of the example script.

I am however using On Error Resume Next on my Function/Sub EXIT script that does object cleanup at the end.  The rest of the function/sub body is error handled like it should be.  I like the nothing test methodology though and will start using that in the future, however, correct me if I am wrong, but the On Error Resume Next during the exit (Cleanup) of a function should .

The NetworkTable referenced in the startup module is just a local Access table that is not shared, just stores the "Configuration" settings for the Access frontend, but most of all the other DAO recordsets specify (dbOpenDynaset, dbSeeChanges) since they are usually updating values and need to hold locks for the short second it is opened and subsequently closed.  The default installation databases are very very very small, like 10-15 MB and probably has less than 10,000 rows in all tables combined, so while I have not looked, I suspect most locks will quickly escalate to a table read lock.  This is the size database I am testing with but users may grow the database long term to about 100 MB, but still nothing compared to the 500 GB + databases I manage in my day job.  The primary purpose of SQL Server in this role is to provide data security that Access 2007 no longer has and to provide a networkable solution.

If I were to do a whole lot of work with the DAO logic and trying to make it ADO, I would probably favor moving most of the logic into stored procedures which would be more robust, but I doubt my client has that in the budget if you know what I mean.  It is just unfortunate that the existing DAO logic is giving me so much grief.

I will post back again once I have reviewed the other suggestions and done some more testing.

Thanks again,

Mark

Hi Mark,

interesting, because in this thread:

https://www.experts-exchange.com/questions/25011274/How-to-automate-adding-a-query-to-Excel-2007-spreadsheet.html

the author has problems with connecting VB.NET to Excel with ODBC. I gave him a working solution which I tested here with XP/Vista 32 Bit and it worked without problems, he said he has problems with this but he uses Windows 7 and 64 Bit - so maybe it is really a problem of W7 or 64 Bit or both? Maybe there are additional security issues or problems of "classic" DAO with 64 Bit? Worth a check.

I saw in your code which sense this table had, so no problem with the explanation, it should only be an example if you use similar constructs on other such functions. In local tables it does not make any difference as they are local and not used by other users...:-)

Cheers,

Christian

So again, I wish I had more time tonight to troubleshoot further tonight but I am out of time.

I've run the same installation routine on different operating system versions and it only works successfully on XP so far.

Tested:
XPMode (VPC) 32-bit           Success
XP Pro 32-bit                      Success
Vista 32-bit                         Not Tested Yet
Vista 64-bit                         Fail
Win 7 32-bit                        Fail
Win 7 64-bit                        Fail

Could there be something causing the issue that is machine specific?

From your breakdown it looks to be OS-specific.

You can intermix ADO and DAO in your application. I do it all the time. I wonder if you could use ADO when you run into trouble areas, and then leave the DAO code that's working.

we should drop that ADO vs. DAO as long as nobody thinks it is the cause of the problems (where none of the author's comments give a clue to). think it is a connection issue where the dbms connection cannot recover from timeout. simply should never run into timeout. best by activily disconnecting after use (or after an own timeout).

Installed the applicaiton on a Vista 32-bit machine during lunch and it worked fine.

I'm not an expert on references, but perhaps there are different versions of a referenced library on each OS?
My current references are as follows in this order:

Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Forms 2.0 Object Library
Microsoft Common Dialog Control 6.0 (SP3)
SageKeySecurity
Microsoft Scripting Runtime

In regards to timeouts,  I can make the application failed within about 10 seconds of clicking through the screens.
Setting the TraceODBCAPI flat and reviewing the output correlated to the 3151 error does continuously give me the DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle (0).

It looks like I get 3146 errors correlated to the DIAG [S1000] [Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command (0), but does recover if given enough retries.

I will research further tonight since my lunch is over.

So at the moment I am getting the opinion that despite Microsoft indicating DAO is still a valid methodology, it is prone to random errors that can not be prevented nor recoverable in code.  That it is not anything I am specifically doing wrong but that there is just no solution to the problem other than not use the DAO.

I spent the last day converting the 387 DAO recordsets defined in the application to ADO.  A few personal tests did not produce any errors but I am going to send it off to user test and see if any issues resurface.  Hopefully the invested time pays off.

Application owners started testing the ADO implementation yesterday and will probably take another week or two to get through the testing all the screens thoroughly.
While the discussion here was good and touched on a variety of areas, I do not believe we answered the underlining question of why DAO was causing the errors like "DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle (0)" and how to fix it.  Microsoft supports DAO connectivity with SQL Servers and I see it used at companies all the time to work with SQL Servers performing ad hoc queries and reports.
ADO is a different alternative technology which was supposed to replace DAO until I guess Microsoft decided to keep it around, but that is a different topic.
I think we can all agree that ADO should work because it works around the problem I was trying to find a solution to without changing all of the code in the application.  I would estimate that switching from DAO to ADO has resulted in an additional 100 unexpected man-hours in this project, but in reality the DAO issue still exists and remains a mystery to me.

Using the ADO alternative technology to work around the actual problem has tested out fine as expected, but certainly a laborious step in a large project.

I'm fine with closing the question as the underlining DAO technology issue unanswered.