Executing a VBA function in a ADODB SQL query on Access from Excel

[bradyboyy88]

So I use excel as my main application and use ADO to connect to an access database. Well something I wanted to accomplish which was concatenating rows in a database field is not possible without a vba function. Well I was wondering, if I save a module in the access database with the function in there, can I call that function using my ADO sql query?

An example of what I am trying to call is shown here:
https://stackoverflow.com/questions...nse-multiple-lines-in-a-table/5174843#5174843

Thanks

 

[Fazza]

this looks like https://www.mrexcel.com/forum/excel...se-quotations-ado-sql-string.html#post5007510
though now connectiing to Access

did you sort that out? joining/concatenating some text should be simple

please explain specifically what is wanted

 

[Fazza]

PS. please give some sample data.
with things like table name, field names & record values. the data type too if it isn't obvious.
both source data and the results wanted.
if you ask a better question you'll get a better answer

 

[bradyboyy88]

this looks like https://www.mrexcel.com/forum/excel...se-quotations-ado-sql-string.html#post5007510
though now connectiing to Access

did you sort that out? joining/concatenating some text should be simple

please explain specifically what is wanted

Thanks for the reply Fazza. Yea I apologize, my question may not have been specific enough. I did sort out that error. It turned out my sql code had a syntax error because there was not a space between where my vba editor cut off the line to create a new one (the query was a lot longer than the simpler query I posted). I had suspected it was a quotation error but it was not.

What I am trying to do here is a bit different. I am not trying to use a excel vba function in my sql query, I was curious if its possible to use a sql query that utliizes a function saved in the access database I am connecting through via ADO. The reason being is my research has shown that concatenating a row of strings in an access database via ado is not possible with SQL alone. I have posted about it here which includes my sample data/example:

https://stackoverflow.com/questions...ess-sql?noredirect=1#comment84593321_48779141

So I found in that link about the GetList function (in the link of my OP) which should be pasted into the access module when running the sql code. The only difference is I am using ado from excel to do this so I was just curious about accessing functions in the access database when doing the queries via ADO vs running them directly in access. I was not sure if this concept is possible and if so then do all I need to do is paste it in the module like i stated or is that too good to be true? If you do know a sql only method of doing what I want then you are a genius haha. Ive been wrapping my mind around it trying to figure out a creative way but nothing..

 

[Norie]

Why not try setting up a query in Access that uses your VBA function and then try and use the via ADODB?