Query an Access query with VBA function from Excel

[bhuijsmans]

Hello,

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

    With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
        Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
        .RowNumbers = True
        .Refresh BackgroundQuery:=False
        iResultRowCount = .ResultRange.Rows.Count
    End With

When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

So my question is; why can't I query a query with a VBA function in Access from Excel?

when the answer is like "because it can't be done", my next question will be:
Who can give me a suggestion for a work-around to achieve the same results?

thanx!

 

[Richard Schollar]

Hi

You can't use UDFs when querying Access from Excel (or even Access-native functions like Nz()). Bummer isn't it?

What does the function do? If it is fairly simple, then it may be possible to just do the processing in Excel (or maybe replace the function with standard Access SQL).

 

[bhuijsmans]

Hi,

thanx for your quick reply!

Bummer indeed!

In my application the user only uses my Excel applications. The Access application is only accessed by my Excel applications.

So with your answer, can I conclude that all the VBA functionalities from Access are useless for my application (since the Access application will never be opened by an user / will only be remotely accessed by Excel sheets)?

So there's also no possibility to create something like 'stored procedures' in Access?

 

[Richard Schollar]

As long as you you don't use any Access-specific functions, you would be OK. So you can use SQl statements with Iif(), DateValue() and CInt() for example, but not Nz(). You can't use UDFs at all (AFAIK). I don't think you can execute stored procedures thru Excel (may be wrong though).

 

[bhuijsmans]

The function I wrote was a check for two values (field values); a real value and a target value. My UDF values the result of the actual value accoring to the taget value.

I this it could be possible to do this with SQl statements, but then I have to hardcode the limits into the statements. It will be a horror to update the limits... (Or is it possible to use variables/constants in queries?)

 

[DonkeyOte]

you could build the limits into variables and build the SQL statement accordingly prior to it's execution:

eg

Dim val1 As Long: val1 = 1000
Dim crit1 As String: crit1 = "Test"
Dim sqlstr As String
sqlstr = "SELECT fieldz * "  & val1 & " FROM table WHERE 1=1 AND x = '" & crit1 & "'"

 

[bhuijsmans]

Hi Lasw10,

thank you for your reply, but I don't understand what you are trying to explain with that query...

 

[DonkeyOte]

I was just trying to illustrate how you could use variables to build a sql string prior to execution (so you mentioned writing your own SQL but wanting to put various components in variables to save time)

my example:

Dim val1 As Long: val1 = 1000
Dim crit1 As String: crit1 = "Test"
Dim sqlstr As String
sqlstr = "SELECT fieldz * " & val1 & " FROM table WHERE 1=1 AND x = '" & crit1 & "'"

was just to show that when it came time to use sqlstr in an Execute statement (ie Execute(sqlstr)) the sqlstr variable would be executed as follows:

SELECT fieldz * 1000 FROM table WHERE 1=1 AND x = 'Test'

 

[bhuijsmans]

Ah, ok!

But what I tried to ask was if it's possible to use constants in an Access query is such a manner that it is still posible to query this query from an Excel application. Without being bothered by the disability of MS Query to query an Access query with VBA functions (UDF's).

 

[bhuijsmans]

Now I have the same problem with an Access query containing a WHERE clause with a LIKE statement with wildcards:

SELECT field FROM table WHERE field LIKE "*xxx*"

when I query this query (with the LIKE statement) from Excel, it ignores the LIKE statment.

But when I change the Access query to (without wildcards):

SELECT field FROM table WHERE field LIKE "xxx"

it works fine...

Have LIKE statements with wildcards the same problem as UDF's when query them from Excel?