I have an application that started in Access 2000. I have it running in 2010 now but the code is unchanged. It worked fine in 2003 on XP.
Here's the problem.
I am running a SQL command (code to follow) that is dying with an error:
-2147467259--connection to 'dbname' failed.
This is happening consistently but only when running in 2010. When I run it in XP I am fine. All other database operations up until that point (such as setting recordsources for drop downs) work fine. Data from the tables shows up fine. This command does not.
I tried a decompile/recompile. No go. I even tried updating the structure of the file to accdb with no success.
I am using ADO/MDAC 2.8. SQL Server 2012 (SP1) with SQL Native Client 2008R2. (I tried it with Native Client 2012 too).
Here's the basic code:
Code:
adoJobRs.ActiveConnection = CurrentProject.Connection
strSql = "SELECT fkTcbHdr FROM dbo_vJobDtlByReel WHERE teJob = '" & prStrTeJobNo & "' AND " _
& "itmNo = '" & prStrItmNo & "' AND NZ([testStatus],'') <> 'Void'" _
& " ORDER BY testDateTime, fkTcbHdr, temp, tempSeq"
adoJobRs.Source = strSql
adoJobRs.CursorLocation = adUseClient
adoJobRs.LockType = adLockReadOnly
adoJobRs.Open
Any help would be greatly appreciated.