bhuijsmans
New Member
- Joined
- Jul 8, 2008
- Messages
- 27
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:
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!
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:
Code:
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!