Connection String to be used calling SQL stored procedures from MS Access 2013

My main work machine crashed earlier in the week and I am setting up on another computer in the office.  With help from EE, I was able to create a DSN to re-link the SQL backend tables in my Access 2013 application.

The next step is to create a connection string used by the routines that call stored procedures.

To create the connections string on other computers I’ve set up I open one of the linked tables is design mode.  The I click the ‘property sheet’ button.  From the property sheet screen I copy the ‘Description’ property.  In this case
“ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion;TABLE=dbo.vInstallPay_Schedule”

To create the connection screen used by my stored procedure calls I remove the ‘Table’ option.  In this case that gave me
“ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion”

This is the connections string I use in my stored procedure calls.  Up until setting up this machine that has always worked perfectly but that is not happening on this machine.

When I run a stored procedure command I am getting the error in the pic

EE Connect Error - SP
This is the code I am using the call the stored procedure
Public Sub buildJerrysReportTempRecordset(Optional passedMuniCode As Variant = Null, _
                                          Optional passedSchoolDistrictNum As Variant = Null, _
                                          Optional passedCountyCode As Variant = Null, _
                                          Optional passedFromDate As Variant = Null, _
                                          Optional passedThruDate As Variant = Null, _
                                          Optional passedTaxTypeID As Variant = Null)
clearTable "tblRptJerrysReport"
' Set TaxType For First Pass.  If they want all Muni's for School or County might need two passes
Dim wkJerrysReportLineTotal As Double
Dim rsOut As ADODB.Recordset
Set rsOut = New ADODB.Recordset
rsOut.Open "tblRptJerrysReport", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "spRptJerrysMonthlySummary"
    .CommandType = adCmdStoredProc
    Debug.Print gConnection
    .ActiveConnection = "ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion"   'gConnection
    ' Input Params
    .Parameters.Append .CreateParameter("passedCountyCode", adBigInt, adParamInput, , passedCountyCode)
    .Parameters.Append .CreateParameter("passedSchoolDistrictNum", adBigInt, adParamInput, , passedSchoolDistrictNum)
    .Parameters.Append .CreateParameter("passedMuniCode", adBigInt, adParamInput, , passedMuniCode)
    .Parameters.Append .CreateParameter("passedTaxTypeID", adBigInt, adParamInput, , passedTaxTypeID)
    .Parameters.Append .CreateParameter("passedFromDate", adDBTimeStamp, adParamInput, , passedFromDate) 'aDBTime, aDBDate, adDBTimeStamp, adDate
    .Parameters.Append .CreateParameter("passedThruDate", adDBTimeStamp, adParamInput, , passedThruDate)
    Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        ' Processed Returned Records
        If rs.EOF Then
           ' MsgBox "No Records Returned"
            While Not rs.EOF
                    rsOut![MuniCode] = Nz(rs!MuniCode)
                    rsOut![SchoolDistrictNum] = Nz(rs!SchoolDistrictNum)
                    rsOut![LotBlock] = Nz(rs!LotBlock)
                    rsOut![DepositDate] = Nz(rs!DepositDate)
                    rsOut![PostingDate] = Nz(rs!PostingDate)
                    ' Separate Remitted and UnRemitted on Jerrys Report depending on if there is a 'DateSentToTaxingAuthority'
                    If IsNull(rs!DateSentToTaxAuthority) Then
                        rsOut![Remitted_YN] = False
                        rsOut![Remitted_YN] = True
                        rsOut![DateSentToTaxAuthority] = Nz(rs!DateSentToTaxAuthority)
                    End If
                    rsOut![PaymentDate] = Nz(rs!PaymentDate)
                    rsOut![FaceAmt] = Round(Nz(rs!FaceAmt), 2)
                    rsOut![PenaltyAmt] = Round(Nz(rs!PenaltyAmt), 2)
                    rsOut![InterestAmt] = Round(Nz(rs!InterestAmt), 2)
                    rsOut![SvcChgAmt] = Round(Nz(rs!SvcChgAmt), 2)
                    rsOut![LienCostAmt] = Round(Nz(rs!LienCostAmt), 2)
                    ' Everything for Jerrys Line total except Cost, which varies based on which tax type and school
                    wkJerrysReportLineTotal = Round(Nz(rs!FaceAmt) + _
                                                    Nz(rs!PenaltyAmt) + _
                                                    Nz(rs!InterestAmt) + _
                                                    Nz(rs!SvcChgAmt), 2)
                    ' The cost amount on Jerrys Report for City Pgh, County, Pgh School and Library do not include
                    ' below the line costs.
                    If Nz(rs!TaxTypeID) = eTaxType.eBoroMuniTwp And (Nz(rs!MuniCode) >= 101 And Nz(rs!MuniCode) <= 132) _
                        Or Nz(rs!TaxTypeID) = eTaxType.eCounty _
                        Or Nz(rs!TaxTypeID) = eTaxType.eLibrary _
                        Or (Nz(rs!TaxTypeID) = eTaxType.eSchool And Nz(rs!SchoolDistrictNum) = 50) Then  ' 50=Pgh Schools
                        rsOut![JerrysReportCost] = Round(Nz(rs!LienCostAmt), 2)
                        wkJerrysReportLineTotal = wkJerrysReportLineTotal + Round(Nz(rs!LienCostAmt), 2)
                        rsOut![JerrysReportCost] = Round(Nz(rs!AboveAndBelowCostAmt), 2)
                        wkJerrysReportLineTotal = wkJerrysReportLineTotal + Round(Nz(rs!AboveAndBelowCostAmt), 2)
                    End If
                    rsOut![JerrysReportLineTotal] = wkJerrysReportLineTotal
                    rsOut![AboveAndBelowCostAmt] = Round(Nz(rs!AboveAndBelowCostAmt), 2)
                    rsOut![MailCostAmt] = Round(Nz(rs!MailCostAmt), 2)
                    rsOut![AttyFeesAmt] = Round(Nz(rs!AttyFeesAmt), 2)
                    rsOut![RecordCostsAmt] = Round(Nz(rs!RecordCostsAmt), 2)
                    rsOut![SherSaleAmt] = Round(Nz(rs!SherSaleAmt), 2)
                    rsOut![TransactionTypeID] = Nz(rs!TransactionTypeID)
                    rsOut![PayMonth] = Nz(rs!PayMonth)
                    rsOut![PayYear] = Nz(rs!PayYear)
                    rsOut![CollectionPeriod] = GetCharacterMonthFromNumber(Nz(rs!PayMonth)) & ", " & Trim(Str(Nz(rs!PayYear)))
                    rsOut![TaxTypeID] = Nz(rs!TaxTypeID)
                    rsOut![CountyID] = Nz(rs!CountyID)
                    rsOut![TotFullPay] = Round(Nz(rs!TotFullPay), 2)
                    rsOut![TotPartPay] = Round(Nz(rs!TotPartPay), 2)
                    rsOut![CheckNum] = Nz(rs!CheckNum)
                    rsOut![VoucherNum] = Nz(rs!VoucherNum)
                    rsOut![TAReceipt] = Nz(rs!TAReceipt)
                    rsOut![TAReceiptSeq] = Nz(rs!TAReceiptSeq)
                    rsOut![PropAddName] = Nz(rs!PropAddName)
                    rsOut![JTSAddr1] = Nz(rs!JTSAddr1)
                    rsOut![PayTaxAuthID] = Nz(rs!PayTaxAuthID)
            '        rsOut![Total] = Round(Nz(rs!SumFace) + _
            '                              Nz(rs!SumPenaltyAmt) + _
            '                              Nz(rs!SumInterestAmt) + _
            '                              Nz(rs!SumSvcChgAmt) + _
            '                              Nz(rs!SumCostAmt), 2)
        End If
        Set rs = Nothing
        Set rsOut = Nothing
    ' You could now assign it to a form
    Set .ActiveConnection = Nothing
End With
Set cmd = Nothing
End Sub

Open in new window

Any ideas on why my tried and true method of creating the connection string is not working?
Who is Participating?
BitsqueezerConnect With a Mentor Commented:

what connection string did you use?
(If you post it here, change the server name and user/password if you use SQL Server user.)

In the meantime you should try to find the right one here:



are you sure that you created the ODBC connection in the right version?
In 64Bit OS like Win7/8/10 you are starting the 64Bit version of the ODBC tool if you start it from the start menu.

Access is usually 32Bit so you need a 32Bit version of the ODBC tool which you can find in C:\Windows\SysWow64\odbcad32.exe

I would recommend not to use an ODBC connection but instead create a connection string directly in the code or (in case of linked tables) use a DSNless connection.

This describes how to do that:


mlcktmguyAuthor Commented:
Thanks Christian.  I di use the 32 bit module you identified when I created the DSN.  I put the '32' suffix on it to remind myself which one it was.

I don't have any experience with DSNless but I'll look at it.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

mlcktmguyAuthor Commented:
MNo luck with the DSNless, I oculd not make it work.  Any other ideas?
mlcktmguyAuthor Commented:
The connections string that works connecting to my linked tables is:

"ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion"

I try the same connections string when establishing a connection for my Stored procedures

    .ActiveConnection = "ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion"    'gConnection  '"ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion"

and get the error:

Connection Error
This string was created with the 32 bit exe
I will also look at the reference you provided
mlcktmguyAuthor Commented:
Thanks you, I was able to create the needed connection string using the link
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.