Execute stored query containing "function" in access db from excel

Question

I am trying to run a query stored in access database from excel vba. The query runs fine if I open and run it in access database but causing error while running it from VBA module. The query uses a stored function called "Getlist" which is in access database module. Below is the query.

SELECT paramconcat.parameter, GetList("Select concat From paramconcat As T1 Where T1.parameter = """ & [paramconcat].[parameter] & """","",", ") AS Concatenated INTO results
FROM paramconcat
GROUP BY paramconcat.parameter;

VBA Module

Sub test1()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open "C:\Users\erukhq\Documents\My Received Files\paramcheck.accdb"
End With
con.Execute "ParamconcatQ"
con.Close
End Sub

The error I am getting is as below.

ThunderFrame · Accepted Answer · 2016-01-07 19:13:34Z

1

You can only use built-in, native VBA functions in queries that are called from outside of Access, so your custom function will work when running the query inside Access, but it will not work when called via ADO.

You'll also need an ADODB.Command object.

'... Dim cmd as ADODB.Command Set cmd = New ADODB.Command Cmd.CommandText = "yourqueryname" Cmd.CommandType = acCommandUnknown 'you might need to try different values of the CommandType enum. Cmd.ActiveConnection = conn Set rst = new ADODB.record set Rst.open cmd

edited Jan 7, 2016 at 19:13

answered Jan 7, 2016 at 12:02

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ThunderFrame Over a year ago

You can use native VBA functions like Left and Mid, but you can't use custom functions, or Access functions, or functions from referenced libraries.

Aprilian8 Over a year ago

Is there anyway to convert the user defined function to native function?

ThunderFrame Over a year ago

I just noticed you're calling the query by name using the Execute method. I don't think the connection recognises it, and that's the case of your current error. You'll need to add an ADODB.Command object into the mix.

Aprilian8 Over a year ago

Set cmd = New ADODB.Command With cmd .ActiveConnection = conn .CommandText = "ParamconcatQ" .CommandType = adCmdStoredProc End With I get Error Undefined function getlist in expression

Collectives™ on Stack Overflow

Execute stored query containing "function" in access db from excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related