1

I currently have a data source of a large table, sitting in workbook1. From workbook2, which is currently empty, I wish to set up a DSN connection to workbook1, so that I can query it from workbook 2.

In the SQL query result, I wish to display extra columns which are calculated using User-Defined VBA functions, the arguments of which will be other fields from the data source.

Example:

Workbook1 is Field1, F2, F3 and F4. I wish to query this and display all records, but additionally I wish to have F5=UDF(F3,F4).

I have been advised already that the solution to this is:

SELECT UDF(F3,F4) as F5 
FROM \SourceWorkBookLocation\SourceWorkBook

IN ACCESS: The problem I am having in access is not at the top of my list right now, relates to data types and trying to determine if a number in a string is <25. But the main problem is in MS Query:

IN EXCEL/MS QUERY:

The function is just not recognized; "undefined function" I am not sure how to get it to see the function? My end goal here is to build a front end in excel, and have vba querying appropriately using user input variables passed to the queries. The querying will be done on a separately updated workbook.

Any ideas on how to get MS Query to see my UDF and accept what I am doing? Could it be a driver issue? There are a range of excel drivers to choose from.

Thanks

Community
  • 1
  • 1
user2025011
  • 11
  • 1
  • 1
    the query you are passing to MS Query is being passed to the SQL server, which has no idea what an Excel spreadsheet or Access database is, so it can't see your UDF function. If the function was rewritten as a stored procedure in SQL Server, then you could use the format you have now – SeanC Jan 30 '13 at 19:14

1 Answers1

0

Looking at the info you have provided, you have tried to use two Excel workbooks as tables to query using Excel VBA UDF. Now I assume you are going to use these workbooks as your tables but in MS Access.

All most all databases is able to read standard SQL. See the thing is that each database is able to handle functions writting in their own space. In your case please write your UDF in Access VBA. Then try to execute to the same.

This is a common issue sometimes people do face, either tyring to access MS Access UDFs from Excel or vise versa. In a nutshell, when you're running MS Access, queries can call back into VBA. But when you're going through ODBC or ADO, the JET engine doesn't have the whole VBA model to draw on because it's simply not running.

You could try to do something like this:

Dim objExcl As Object     
Set objExcl = CreateObject("Excel.Application")     
objExcl.OpenCurrentDatabase "ExcelFileName/Path"     
objExcl.Run ("UDFName")    
objExcl.CloseCurrentDatabase     
Set objExcl = Nothing

Frankly I prefer moving the UDF in to Acces..

References:

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • thanks, currently trying to incorporate function into SQL Statement itself – user2025011 Feb 04 '13 at 14:33
  • How can you incorporate a function into SQL Statement without writing it in an accessible place? :) – bonCodigo Feb 04 '13 at 14:36
  • I am not sure what you mean by accessible place? – user2025011 Feb 04 '13 at 16:27
  • Is it possible to retag your question specifying the correct RDBMS you are using? Whether it's MS Access, SQL Server? – bonCodigo Feb 04 '13 at 16:34
  • I dont think so; it appears too late to do that. But I am referring to MSQuery from Excel. I cannot get it to see locally defined UDFs in VBA. I am no longer sure I am going about this in the best way, but I need to basically look at the data source, and create a load of new columns using logical operations which are defined in UDFs. These are not complex; they are basically string tests and if statements, so there may be some way to port to SQL? – user2025011 Feb 06 '13 at 09:55
  • http://www.xtremevbtalk.com/showthread.php?t=159823 please check on this link it has some relevant info on ADO Excel SQL, UDF... – bonCodigo Feb 06 '13 at 12:04