MS Access 2016 FunctionCode Error

Question

I have a macro that worked wonderful in MS Access 2010.  Our department upgraded to Office365 and now I continuously have issues with it.  I am getting this error message when running my Macro to update the DB.  

It stops on the RunCode; Function Name GetUserInfo().  The purpose of this macro is to run other macros that connect to an Oracle DB on the server to return the agency cases received counts and input them into the table.

I've tried everything I have researched on the internet and nothing has worked.  These are the references that are checked:

Can someone help me please?  This database is used to track our agency data submissions.  I'm not an expert in VB.  

Thank you in advance.

Answer 1

Have you done as Tom advised and set a breakpoint on the top line of the GetUserInfo function?  You'll then be able to step into the code with the F8 key when the function is called, enabling you to identify on which line the error is occurring.

Answer 2

Here is the Oracle Module

Option Compare Database

Option Explicit

'======================================================================================================

'Name: modOracle

'Created: 3/28/2008

'Author: Eric Robbins

'Description: This module contains the procedure for connecting to the MICR database,

'             and for updating the yearly totals. This module will also update the monthly totals for

'             a given year in this database.

'Dependencies: This module requires reference to the Microsoft ActiveX Data Objects 2.0 Library

'Issues: The provider string used for the connections to the MICR database contains the userid and

'        password for connecting to the database. Hard coding it is not secure. May want to look into

'        alternate options for passing this information.

'======================================================================================================

'Updated: 2/25/2009

'By:      Eric Robbins

'Notes:   Removed hardcoded userid and password, and created function to prompt user for their

'         MICR userid and password.

'======================================================================================================

'Module level constants

Dim strUID As String                       'User's MICR DB user id

Dim strPass As String                      'User's MICR DB password

Dim strProvider As String                  'This is the provider string used to connect to the MICR Oracle DB.

Public Function GetUserInfo()

'======================================================================================================

'Procedure Name: GetUserInfo

'Created: 2/25/2009

'Author: Eric Robbins

'Description: This procedure prompts the user for their MICR Oracle DB user name and password. This info is

'             then used for the connection string used in this module.

'Inputs: None

'Issues: None

'======================================================================================================

    'strUID = InputBox("Enter Your MICR Database User ID", "MICR User ID")

    'strPass = InputBox("Enter Your MICR Database Password", "MICR Password")

    strUID = "easterw"

    strPass = "   "  (I removed the password that is stored here)

    strProvider = "Driver={Microsoft ODBC for Oracle};Server=msp006pd;" & _

                              "Uid=" & strUID & ";Pwd=" & strPass & ";"

End Function

Public Function CallORI_Totals_Procedure(ByVal intInputYear As Integer)

'======================================================================================================

'Procedure Name: CallORI_Totals_Procedure

'Created: 3/28/2008

'Author: Eric Robbins

'Description: This procedure connects to the Oracle database and executes the stored procedure

'             that updates the ORI_YEAR_TOTALS table.

'Inputs: Requires an integer input variable designating the year.

'Issues: None

'======================================================================================================

'Create ADO database variables for connecting to Oracle database

Dim cnnOracle As ADODB.Connection    'the connection object

Dim cmdExecute As ADODB.Command      'the command object for executing the server side procedure

Dim strProcedure As String           'the string for the name of the server side procedure being executed

Dim prmYear As ADODB.Parameter       'the parameter object to store the year passed to the server side procedure

'Error handling

On Error GoTo CallORI_Totals_Procedure_Err

'set the connection oject and connection string, then open the connection

Set cnnOracle = New ADODB.Connection

cnnOracle.ConnectionString = strProvider

cnnOracle.Open

'this code group sets the command object and the name of the server side procedure being called

Set cmdExecute = New ADODB.Command

cmdExecute.ActiveConnection = cnnOracle

strProcedure = "micrdba.ori_totals_pkg.calculate_ori_totals"

cmdExecute.CommandText = strProcedure

cmdExecute.CommandType = adCmdStoredProc

'set the parameter object

Set prmYear = New ADODB.Parameter

prmYear.Type = adInteger

prmYear.Direction = adParamInput

prmYear.Value = intInputYear

'append parameter to the command object

cmdExecute.Parameters.Append prmYear

'Execute the server side procedure

cmdExecute.Execute

'clean up and exit procedure

CallORI_Totals_Procedure_Exit:

If Not prmYear Is Nothing Then

    Set prmYear = Nothing

End If

If Not cmdExecute Is Nothing Then

    Set cmdExecute = Nothing

End If

If Not cnnOracle Is Nothing Then

    If Not cnnOracle.State = adStateClosed Then

       cnnOracle.Close

    End If

    cnnOracle.ConnectionString = ""

    Set cnnOracle = Nothing

End If

Exit Function

'Error handling

CallORI_Totals_Procedure_Err:

    MsgBox Err.Number & " : " & Err.Description, vbOKOnly + vbCritical, "Error Calling MICR Procedure"

    Resume CallORI_Totals_Procedure_Exit

End Function

Public Function UpdateMonthlyTotals(ByVal strTableName As String)

'======================================================================================================

'Procedure Name: UpdateMonthlyTotals

'Created: 3/31/2008

'Author: Eric Robbins

'Description: This procedure connects to the MICR database and grabs the required information from

'             the ORI_YEAR_TOTALS table. This information is then put into the appropriate table in this

'             database.

'Inputs: Requires a string input variable designating the table name of the table being updated.

'Issues: None

'======================================================================================================

'Declare variables for connecting to the MICR DB

Dim cnnOracle As ADODB.Connection    'the connection object

Dim cmdExecute As ADODB.Command      'the command object for executing the server side procedure

Dim strSQL As String                 'the SQL statement string for retrieving data.

Dim rstTotals As ADODB.Recordset     'recordset returned from query of MICR DB

'Declare variables for connecting to the appropriate DB in Access

Dim dbCurrent As DAO.Database

Dim rstAccessTotals As DAO.Recordset

'Declare other variables and constants

Dim strMonth(1 To 12) As String     'This array is used to grab the appropriate field from the totals table

Dim intMonth As Integer             'This is the month, stripped from the table name passed in to the procedure

'Error Handle

On Error GoTo UpdateMonthlyTotals_Err

'Populate the month array

strMonth(1) = "JAN_CNT"

strMonth(2) = "FEB_CNT"

strMonth(3) = "MAR_CNT"

strMonth(4) = "APR_CNT"

strMonth(5) = "MAY_CNT"

strMonth(6) = "JUN_CNT"

strMonth(7) = "JUL_CNT"

strMonth(8) = "AUG_CNT"

strMonth(9) = "SEP_CNT"

strMonth(10) = "OCT_CNT"

strMonth(11) = "NOV_CNT"

strMonth(12) = "DEC_CNT"

'Strip the month from the passed table name by stripping out the last two digits from the name

intMonth = CInt(Right(strTableName, 2))

'Create the SQL string for required data

strSQL = "Select MIC1_ORI, " & strMonth(intMonth) & " From MICRDBA.ORI_YEAR_TOTALS"

'Set the connection object for MICR connection and open it

Set cnnOracle = New ADODB.Connection

cnnOracle.ConnectionString = strProvider

cnnOracle.Open

'Set the command object and properties

Set cmdExecute = New ADODB.Command

cmdExecute.ActiveConnection = cnnOracle

cmdExecute.CommandType = adCmdText

cmdExecute.CommandText = strSQL

'Set the recordset by executing the command object

Set rstTotals = cmdExecute.Execute

'Set connections to table passed in to the function

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

Set rstAccessTotals = dbCurrent.OpenRecordset(strTableName, DB_OPEN_DYNASET)

'Check to ensure the MICR recordset is at the begining, if not then move the cursor

If Not rstTotals.BOF Then

    rstTotals.MoveFirst

    Do While Not rstTotals.EOF

    'While not the end of the MICR recordset, loop through and add the

    'records to the table passed in to the function

       rstAccessTotals.AddNew

       rstAccessTotals.Fields(0) = rstTotals.Fields(0) 'The fields are not named in this function, as

       rstAccessTotals.Fields(1) = rstTotals.Fields(1) 'the months will change, meaning changed field

       rstAccessTotals.Update                          'names for each month tallied.

       rstTotals.MoveNext

    Loop

End If

'Clean up connections, commands and recordsets and exit function

UpdateMonthlyTotals_Exit:

If Not rstTotals Is Nothing Then

    Set rstTotals = Nothing

    If Not cmdExecute Is Nothing Then

        Set cmdExecute = Nothing

    End If

    If cnnOracle.State = adStateClosed Then

        cnnOracle.Close

        cnnOracle.ConnectionString = ""

        Set cnnOracle = Nothing

    End If

End If

If Not rstAccessTotals Is Nothing Then

    Set rstAccessTotals = Nothing

End If

If Not dbCurrent Is Nothing Then

    Set dbCurrent = Nothing

End If

Exit Function

'Error handling

UpdateMonthlyTotals_Err:

    MsgBox Err.Number & " : " & Err.Description, vbOKOnly + vbCritical, "Error During Update Process"

    Resume UpdateMonthlyTotals_Exit

End Function

Answer 3

Yes and yes.  

Here is One module to write the LDB file.

Option Compare Database

Option Explicit

Function WriteFile()

         ' These variables store the text of the file.

         Dim Linedata As String

         Dim WholeFile As String

         Dim Crlf

         Dim MyChar As String

         Dim User As String

         Crlf = Chr$(13) & Chr$(10)

         ' Open the file.

         Open "S:\Lansing3\CJIC\Crime_Reporting_Section\Agency_File\MicrTracking_Database\MICRTracking.ldb" For Input As #1

         ' Continue reading until end-of-file.

         Do While Not EOF(1)

                MyChar = Input(36, #1)   ' Get 36 characters, the length of the computer name and its rights in the .ldb file.

                User = User & MyChar & Crlf   'Adds a return and line break after the user and rights.

                WholeFile = User    ' Store each user in the WholeFile variable.

         Loop

         ' Transfer contents of file to text box on form.

         Forms!LMfrmLDBfile![My_Text1] = WholeFile

         ' Close the data file.

         Close #1

      End Function

Answer 4

Does your code compile? Hard to believe.

Do you have "Option Explicit" at the top of each module. If not, add it now and compile again.

Once you pass these hurdles, set a breakpoint at the top of GetUserInfo and carefully step through.

Answer 5

I guess it would let me post the pictures.

Message:  The expression contains an ambiguous name.  Verify that each name in the expression refers to a unique object. 

References checked:

Visual Basic For Applications

Microsoft Access 16.0 Object Library

OLE Automation

Microsoft ActiveX Data Objects 6.1 Library

Microsoft DAO 3.6 Object Library.

Thanks.