Access from Word wildcard - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access from Word wildcard

Brian Crawford

2 years ago

I have VBA code in Microsoft Word that uses SQL to pull data from Access to refresh data in MS Word Tables.  The code uses a ADODB.Connection using ACE.OLEDB.12.0.  This allows me to have Word tables linked to Access tables or queries for easy edits and refresh.  This has been work well UNTIL I found an incompatibility between the JET database SQL and SQL-92 that I can't get around.

If I have an Access query that includes wildcards condition such as conditions using the "LIKE" and a wildcard, then the MS Word recordset query always returns rs.EOF (no records).

Suppose I have a Query (Query1)  that shows all records with the field CodeVal LIKE 'UNK*'
In Access, I will see all records with a CodeVal starting with UNK, but if I use my Word VBA code to query "SELECT * FROM Query1;", I get no records back because in Access the query uses the "*" wildcard, which is not allowed in the SQL-92 standard.  SQL-92 wants a "%" instead of a "*", then the Access query has no records to pass.

	Richard Rost @Reply 2 years ago
Yeah, you're running into the same problem working with SQL Server. You have to use the different wildcard characters.

Kevin Yip @Reply

2 years ago

Instead of ADO, you should probably use DAO, which is more suitable for Access.  At least that was what I did in my old job.  Here is a code sample from my old database.  I could use Access-style wildcards just fine:

    Dim ws As Workspace, db As Database, r As Recordset
    Set ws = CreateWorkspace("", "admin", "")
    Set db = ws.OpenDatabase("C:\MyDB.accdb")
    Set r = db.OpenRecordset("SELECT * from [query: styles] WHERE dt_style_headers.style_no Not Like '????7*' ORDER BY rank")

To use DAO, go to the VBA editor -> Tools -> References -> check "Microsoft DAO 3.6 Object Library".

Brian Crawford OP @Reply

2 years ago

The problem that I am having is that I am using VBA to pull data from an Access-side query that uses the LIKE operator, but the MS Word-side query to pull from that Access query does not. It is literally "SELECT * FROM Query1". It has no WHERE clause at al, and yet it fails. My code is a limited function that uses a parameter to provide the table (or query) name so that a SELECT * FROM call can be executed.

My intent is to be able to setup Access queries so that less technical users can reach into the Access to retrieve data that is contained in a list of existing tables and queries that they can draw from. What concerns me is that if the Access-side query uses a WHERE clause with a wildcard, it will return no data even if the the query does return data locally, plus there is no way from the Word VBA-side to retrieve the Access-side SQL to know if there is a problem.

	Richard Rost @Reply 2 years ago
I've never encountered this problem before, but it might merit some experimentation. Could make a good video. I'll add it to my list when I get some free time to check it out.

Kevin Yip @Reply

2 years ago

Hi Brian, as I said, use DAO for this, because DAO supports *native* Access SQL syntax as shown in my code sample. ACE.OLEDB.12.0 is an OLE DB driver, not an Access driver. There is no native support for Access there. ADO is most often used in client-server environment. For local Access environment, I always use DAO to avoid the headaches you are experiencing. I use ADO only when I deal with non-local access, such as in web apps that need online remote access. That's why SQL Server, which is made for online access, has a native OLE DB driver for ADO ("Provider=SQLOLEDB; ...")

Brian Crawford OP @Reply

2 years ago

Kevin, Thanks for the recommendation. I don't know if my Admin's security policy is blocking me or not, but the reference file "Microsoft DAO 3.6 Object Library" is available on the checklist, but will not load. I'm thinking it is missing from my MS Office distribution.

HOWEVER, I seem to have found an undocumented workaround that allows me to continue to use ACE.OLEDB.12.0. Apparently, Access has an (undocumented) alternative operator for "LIKE" named "ALIKE" Using ALIKE allows you to use the SQL-92 wildcards in the query. I replaced my query LIKE 'UNK*' with ALIKE 'UNK%", and it seems to be working now.

	Richard Rost @Reply 2 years ago
Ah, you know, this is one of those things where if you don't use it, you forget it. And as soon as you mentioned the "ALike" keyword, I remembered that I covered this in SQL seminar part 3 lesson 7. Here you go.

Brian Crawford OP @Reply

2 years ago

Thanks Richard! The is very useful information. More often now, I have the need to connect to access from external tools. I find that I occasionally get strange can't-get-there-from-here issues due to MS Access SQL limitations.

I am wondering if using SQL-92 will make connectivity to Tableau better, because I often run into crashes there. My workaround was to have Access export to CSV and then have Tableau connect to the CSV. My guess is that Access with SQL-92 will be better, but I will have to test it out what the realities are.

	Richard Rost @Reply 2 years ago
Never used Tableau, so I'm afraid I can't be much help there.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View