1

I have an MS Access 2003 database that contains the following query: 

SELECT Replace(Trim(TABLE_A.Field_01), "XXX", "YYY") AS FLD01 FROM TABLE_A

If I do an "Import Data" with Excel from this Access database, I can't find the name of this query that is defined in the database.

If I change the query by removing the Trim function, then I can see the query in Excel. 

SELECT RTrim(LTrim(TABLE_A.Field_01)) AS FLD01 FROM TABLE_A

Has anyone had a similar experience? I think there's a limitation on what kind of function one can apply to a query in MS Access.

It looks like there is a problem with MS Jet SQL, which doesn't support the Replace() function - searching the key words "Jet Sql Replace Function" in google gives a lot of references with various issues with the same root cause, but I haven't found a decent solution yet...

pnuts
  • 58,317
  • 11
  • 87
  • 139
Gao Wei
  • 79
  • 1
  • 11
  • 4dmonster has your answer. Practically, you need to export it from Access rather than import it in Excel. – Chris Rolliston Oct 28 '13 at 08:42
  • My real use case is to have MS Word to do the MailMerge. It would be troublesome to always manually export the excel file and do the MailMerge, while Word MailMerge can read the MS Access query directly. But the issue here is that when I use trim() function in the query, the query is not visible to MS Word anymore, neither to Excel... – Gao Wei Oct 28 '13 at 09:58
  • You can use temporary tables – 4dmonster Oct 28 '13 at 10:05
  • I guess temporary table in MS Access can't be created without some "Marcos". What I want to achieve is a Query sitting inside MS Access, and I just do one click in Word with MailMerge to get the report I need, without the users opening MS Access. – Gao Wei Oct 28 '13 at 10:35

2 Answers2

4

Trim() function is not a part of SQL (resides in VBA.Strings library) so couldn't be called outside MS Access.

So you can use any SQL function but none of "external".

4dmonster
  • 3,012
  • 1
  • 14
  • 24
  • Thanks for the hints! Where can I find a list of widely accepted SQL functions to accomplish the same functions? – Gao Wei Oct 28 '13 at 09:55
  • I found I can use LTrim() and RTrim() instead of Trim(). But the Replace() function still doesn't work – Gao Wei Oct 28 '13 at 10:36
2

For what it's worth, the Replace() function is supported by the Access Database Engine 2010 (a.k.a. "ACE", the successor to "Jet"), available here. To verify that I created a table named [SomeTable] in an Access 2003 database file:

ID  s
--  ----------------------------
1   Everybody loves tofu!
2   Nobody really liked Raymond.

...and I created a saved query named [stockReplaceQuery]:

SELECT ID, Replace([s],"tofu","bacon") AS s1
FROM SomeTable;

When I tested a Jet ODBC connection using the following VBScript

Option Explicit
Dim con, rst
Set con = CreateObject("ADODB.Connection")
con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Users\Public\2003test.mdb;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT s1 FROM stockReplaceQuery WHERE ID = 1", con
WScript.Echo rst(0).Value
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

...I got

C:\__tmp>cscript /nologo repl.vbs
C:\__tmp\repl.vbs(6, 1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

When I tested an ACE ODBC connection by changing the con.Open line to

con.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Public\2003test.mdb;"

...I got

C:\__tmp>cscript /nologo repl.vbs
Everybody loves bacon!

It might be worth a try to install the ACE engine/drivers and see if that helps any.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • It seems to me there may also an ACE version issue. I can't get it to work with Office 2007 ACE, but it works as you described with ACE from Office 2010. Have you found any MS documentation about this? – HansUp Oct 29 '13 at 14:18
  • 1
    @HansUp Thanks for the tip re: 2007 vs. 2010. Unfortunately no, I haven't come across any Microsoft documentation on this. – Gord Thompson Oct 29 '13 at 14:28
  • OK, thanks. This was surprising and interesting. I wish it could work from a default Access 2007 install. But I may use it elsewhere anyway. – HansUp Oct 29 '13 at 14:33