Regarding macros and function in Microsoft access

[SuperUser20]

Hi,

I have created one function in Microsoft access and I am trying to run that function using autoexec macro, but I am getting the error as shown below even if I am using the same function name that I have created. 

Idk what is the issue with this

Any help would be appreciated.

[#2]

Are you sure its a function and not a sub?
Do you have more than one function with that name - if so delete or rename any duplicates.
The function needs to be in a standard module (not a form module) and needs to be public (not private).

[#3]

@isladogs 

yes, it is a function not a Sub

and I am using standard module not a form module

And the function I am using is :

Function FetchData()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "data"

strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, ""

' Uncomment out the next code step if you want to delete the
DoCmd.DeleteObject Table, "data"
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function

[#4]

Hi,

You didn't tell how you refer to the function in the macro. Maybe you just didn't add the parenthesis, i.e. in the Function Name property of the RunCode action you should have: FetchData()

Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ: https://www.donkarl.com

[#5]

@Karl_Donaubauer 

I am using FetchData() only, still it not working

[#6]

Hi,

Another common reason for the message are security restrictions. Do you perhaps see yellow warning triangles on actions in the macro editor?

Is the database in a "trusted folder" or have you disabled macro security?

Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ: https://www.donkarl.com

[#7]

I tried disabling all security restrictions still it is not giving me the required output.

[#8]

Try changing to acSpreadsheetTypeExcel12xml - this is needed for .xlsx files

[#9]

Try defining the function as 'Public' and see if it's then visible to the autoexec macro.

Public Function FetchData()
.....(stuff).....
End Function

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statemen...

[#10]

For info, functions are always public unless specifically labelled as Private. The quote below is from the article that you linked

Also, the OP has already confirmed the function is Public

[#11]

Hi Colin,

I don't remember if we have already advised him to do a check inside VBA in order to see if the function is reachable at all:

1. Open the Immediate Window of VBA by pressing <Ctrl>+G anywhere in Access.

2. In the Immediate Window type the name of the function , i.e.

FetchData

and press <Enter> in this line

If the function executes, it is really public and should be reachable also for macros.

Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ (de/it): https://www.donkarl.com

[#12]

It does indeed say that and I'm guilty of explicit declarations whenever possible.
Incidentally, when Option Explicit was added to the OP function I was not able to get the code to compile.

The following line of code was flagged.
DoCmd.DeleteObject Table, "data"

It compiled when changed to:
DoCmd.DeleteObject acTable, "data"

I don't recall seeing the naming conventions of the Function and Module.
That can result in the same exact error message.
By chance, did the OP name the Function the same name as the Module?

[#13]

After intentionally naming the Function and Module the same exact name and running the Autoexec macro I was able to force the same error message as OP.

Assuming the OP has the same issue, it should throw Error 2425.