Using custom function in VBA

[stepone]

Hi,

I have used a custom function (ConcatRelated - from the Allen Browne website I think) to concatenate text fields in a Group By query and it works great !

Now I need to extract that query from Access and paste into Excel. So, I am doing this from EXCEL vba. First I setup my SQL string and then open a recordset as follows ;

Set m_rstSnapshot = p_dbData.OpenRecordset(m_strSQLstring, dbOpenSnapshot)

This gives a run-time error 3085 - "Undefined function 'ConcatRelated' in expression".

I have had the same issue before, relating to the 'Nz' function, but that was an update Query, and I got around it by calling the query using Docmd.openquery. In this case, however, I need to assign the query to a recordset in order to copy it over using the 'copyfromrecordset' method.

Does anyone know how I can call this from Excel, or is it impossible ?

Thanks very much,
StepOne

 

[CJ_London]

the problem is concatreleated is in the access code, not excel.

I've not tried it, but you could try adding your access db as a library to excel - you may need to do things like change currentdb to codedb in concatreleated so it looks in the right location - see this link

https://msdn.microsoft.com/en-us/library/office/aa221161(v=office.11).aspx

or google 'access vba codedb' for more data