Cannot Connect to 'Database' using CurrentProject.Connection

[bazianm]

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 Access 2003 on 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:

    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.

[June7]

I've never seen code like that.

Review http://allenbrowne.com/ser-29.html

[ssanfu]

Maybe this will help:

"The Connection Strings Reference"
http://www.connectionstrings.com/

[bazianm]

I've never seen code like that.

Review http://allenbrowne.com/ser-29.html

Well, I didn't write it but I am kind of stuck with it. Like I said, it works in 2003 on XP. Not in 7.

[June7]

Code is not etched in stone. I suggest you modify it.

I have no idea what the CursorLocation property is for. Never seen it, never used it.

What do you want to do with the recordset?