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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
mlcktmguyAuthor Commented:
MNo luck with the DSNless, I oculd not make it work.  Any other ideas?
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

BitsqueezerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.