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

replace the wildcard * character with % and it should work.

 

[bhuijsmans]

well, it getting weirder and weirder..

It seems to work for Excel, but when I view the results of the queries in my Access application, there the results aren't correct anymore.

That Access return incoorect data seems to be logical because the % is a single character wildcard, but I need a 'string search' (ex: when a field contains the string *dumm*, then I should not count it).

Are the symbols twisted or something? Is there a logical explanation for this? (Or a workaround?)

 

[Richard Schollar]

From Excel, you need to use %, but if in Access you need to use *. In each case, the wildcard is for 0 or more of any character.

 

[Makrini]

Sorry to hijack the thread...

Can I from Excel VBA - pass a parameter to an Access Query and return the results?

i.e - my access query sums and calculates some things to create a dynaset from 2 tables.

I would love Excel to be able to query that dynaset passing a date range ( or other dynamic criteria) to the query.

Possible?

 

[bhuijsmans]

The % seens to do the trick! Thx!
I created a query with 2 the same where clauses, only one wth * as wildcart and one with % as wildcard. The used negative logic to combine the two (not x and not y).

@Makrini: Interesting problem, it would make life easier, but I don't know how to do this. But I'm curious though.