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
    setSQLConnection
    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"
        Else
            '
            While Not rs.EOF
                '
                rsOut.AddNew
                    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
                    Else
                        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)
                    Else
                        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)
                    '
                rsOut.Update
                '
                rs.MoveNext
            Wend
        End If
        '
        rs.Close
        Set rs = Nothing
        '
        rsOut.Close
        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?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
BitsqueezerConnect With a Mentor Commented:
Hi,

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:
https://www.connectionstrings.com

Cheers,

Christian
0
 
BitsqueezerCommented:
Hi,

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:
https://support.microsoft.com/en-au/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

Cheers,

Christian
0
 
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.
0
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?
0
 
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
0
 
mlcktmguyAuthor Commented:
Thanks you, I was able to create the needed connection string using the link

https://www.connectionstrings.com
0
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.