Access as Excel Datasource: Undefined Function error - no missing references!

[timmychap]

OK, this is driving me a little bit mad.

I have a database in Access which I am trying use as a datasource for some excel pivot tables.

This works fine, except for queries that use a public function that I have developed (it's a a simple classification process based off a case statement with various Instr checks for specific string occurrences - see code below, although I don't think it's the issue).

I have found various apparent fixes to this issue based around missing references, or references in incorrect locations.

I have checked references, and none are marked as missing. I have checked the location of the references, and they seem to be pointing to the right location for the reference.

The database was developed in Office 2007, and I am testing on the same pc that it was developed on. It is not being shared with other users.

It has only ever had Win7 and Office2007 installed on it, so should have no issues with prior versions/old references. All recent service packs are also installed.

Completely lost where to go with this - any general hints on another area to look into would be greatly appreciated.

Cheers

Tim

Option Compare Database

Option Explicit

Public Function Retail_SubChannel(Retailer As String)

Select Case True
    Case InStr(Retailer, "a ") > 0
        ePay_SubChannel = "Type A"
    
    Case InStr(Retailer, "b ") > 0
        ePay_SubChannel = "Type B"
.
.
.
.
    Case InStr(Retailer, "z ") > 0
        ePay_SubChannel = "Type Z"
    
    Case Else
        ePay_SubChannel = "Type Other"
End Select
    
End Function

 

[Norie]

Tim

Is this a function you are using in the query that's the data source of the pivot table?

 

[timmychap]

Hey Norie,

Yes, I'm using the function inside an Access Query, then creating a union of it (and other queries) to create a single aggregated data source.

I'm then trying to access that aggregated data source, to provide the basis of some excel pivot tables.

If I try and access any query in access that uses this function (either the base or aggregate query), I get the error message. Any query not using the function is fine.

Cheers

Tim

 

[Kreszch68]

How do you access the query from Excel?
If you want to use the function you created in Access, this will only work if you create an object that references your database.

To give you an idea on how to do this, see code below. This is a routine that opens an Access Workspace, grabs a recordset from a querdef and copies the recordset to a range in Excel (in it simplest form). This will enable the use of functions created within the database you specify.

Public Sub SetDB()
'Access stuff
Dim oAppAccess As DAO.Workspace
Dim dbs As DAO.Database
Dim qDef As DAO.QueryDef
Dim oRS As DAO.Recordset
'Excel stuff
Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("A1")
Set oAppAccess = CreateWorkspace("", "admin", "", dbUseJet)
With oAppAccess
    
        Set dbs = .OpenDatabase("[COLOR=red][B]PathANDName of your database",[/B][/COLOR] False)
        Set qDef = dbs.QueryDefs("[COLOR=red][B]YourQueryName[/B][/COLOR]")
        Set oRS = qDef.OpenRecordset(dbOpenDynaset, dbReadOnly)
        
            'Copy the recordset to the range
            rng.CopyFromRecordset oRS
            
        oRS.Close
        dbs.Close
        
End With
Set qDef = Nothing
Set oRS = Nothing
Set dbs = Nothing
Set oAppAccess = Nothing
End Sub

 

[timmychap]

Thanks for the response Kreszch68.

I was hoping to avoid needing to use VBA to update the excel range, as ultimately this side will be handed off to non-technical users. And it's just another break point. If that's the answer though, I can certainly run down that path.

I'm using some ADO connections to update to Access from periodic vendor supplied data files, but was hoping that the user facing connection could be a simpler (native) set-up.

My thought would have been that the function has been used in the base Access query, and by the time I looked at a subsequent iteration of the data, it would not need to be recalculated. I.e. I am not trying to use the function in Excel, simply use the Access output of it in the excel reports.

I tried connecting using:
Microsoft.ACE.OLEDB.12.0 (using Tables and SQL queries)
both via the Excel Access Data connection wizard, and MS Query.


Guess another approach could be have the function run on import to access, then have it stored as a value in Access. I'm sure the classifications will never change!

May try the opposite way tomorrow: i.e. use a custom function in excel, then try and link it into access and see how it handles it. Won't fix my issue, but interested to see if its a connection problem, or access.

Cheers

Tim

 

[Norie]

Tim

If you connect using DAO/ADO it's not going to like the use of a UDF in the query.

I was actually thinking of replacing it with an expression that uses 'standard' functions.

Hard to tell if that would be possible without seeing some sample data though.

An alternative might be to create a make table query in Access.

You could then use the table created by that as the source of the pivot table.

Then there's importing the query without the field that uses the UDF then running code in Excel against the imported data.

The last 2 could be done with code without the user knowing.

 

[timmychap]

Thanks Norie,

suspect I will run down that path.

Reckon that perhaps a make table query that refreshes each time updates are loaded may well be the simplest solution.

Cheers

Tim

 

[TSDPE]

How do you access the query from Excel?
If you want to use the function you created in Access, this will only work if you create an object that references your database.

To give you an idea on how to do this, see code below. This is a routine that opens an Access Workspace, grabs a recordset from a querdef and copies the recordset to a range in Excel (in it simplest form). This will enable the use of functions created within the database you specify.

Public Sub SetDB()
'Access stuff
Dim oAppAccess As DAO.Workspace
Dim dbs As DAO.Database
Dim qDef As DAO.QueryDef
Dim oRS As DAO.Recordset
'Excel stuff
Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("A1")
Set oAppAccess = CreateWorkspace("", "admin", "", dbUseJet)
With oAppAccess
    
        Set dbs = .OpenDatabase("[COLOR=red][B]PathANDName of your database",[/B][/COLOR] False)
        Set qDef = dbs.QueryDefs("[COLOR=red][B]YourQueryName[/B][/COLOR]")
        Set oRS = qDef.OpenRecordset(dbOpenDynaset, dbReadOnly)

HERE I GET An Error masage "Application-defined or object-defined error"

Any Ideas?

 

[Micron]

Where is this function that is called by the query? I apologize if this was stated and I missed it. It has to be in a standard module, otherwise you will get this error regardless of whether or not you declare it as public and no matter which query calls it. If it is in a standard module, try running the function directly from the vb editor and see if it returns a value. I don't see why running the function from Excel should be an issue if you've set up the automation part right. Once you've made the connection, anything in it that's configured properly in the database should work.

 

[TSDPE]

Where is this function that is called by the query? .

It is in standard module

...try running the function directly from the vb editor and see if it returns a value. I don't see why running the function from Excel should be an issue if you've set up the automation part right. Once you've made the connection, anything in it that's configured properly in the database should work.

Tryed to run a query without any function, and still getting this Err

I'm on Win 7 + Office 2010
Access mdb file version: 2007