Solved

Problem accessing MS access database from MS Word

Posted on 2016-08-29
14
32 Views
Last Modified: 2016-09-12
I am using the following code in MS Word to access a MS Access database:
strDatabaseName = "C:\AccessReferences\Patient Information.mdb"
strSQL = "SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & "" "" & [Contacts].[LastName])=""" & strName & """) OR ((Contacts.CompanyName)=""" & strName & """)"
With ActiveDocument.MailMerge.OpenDataSource strDatabaseName, wdOpenFormatText, False, True, True    ', , , , , , , , strSQL
GetFaxNumber = ActiveDocument.MailMerge.DataSource.DataFields("FaxNumber").Value

Open in new window

This has been working fine for years but a few months back, I intermittently get these three popups for each lookup (I click on OK or Cancel on each popup and the data is still retrieved from the database):First popupSecond popupThird popupHow do I stop these popups?  Thanks.
0
Comment
Question by:thenelson
14 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 41774565
<<How do I stop these popups?  Thanks.>>

 Are there any other processes using the DB besides this one?

Jim.
0
 
LVL 39

Author Comment

by:thenelson
ID: 41774573
The database is open in MS Access.  Sometimes I can stop the popups in subsequent calls by closing then reopening MS Access.

BTW: the "WIth" on line 3 of the code was included here in error. It is not in the actual code.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41775726
Put a stop in the code so you can print out the contents of strSQL.  Post the generated string if you don't see the issue.
0
 
LVL 39

Author Comment

by:thenelson
ID: 41776149
here is a sample strSQL:
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sandra Smith") OR ((Contacts.CompanyName)="Sandra Smith")
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41776360
That can't be the actual contents of strSQL.  Please put a stop in the code and paste the contents after strSQL has been populated.
0
 
LVL 39

Author Comment

by:thenelson
ID: 41776423
This is an actual sample of the contents of strSQL that works at getting the correct results  from the Contacts table in the database. I copied it after a code stop after the line:
strSQL = "SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & "" "" & [Contacts].[LastName])=""" & strName & """) OR ((Contacts.CompanyName)=""" & strName & """)"
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41776564
The concatenation should have been resolved.  We should not be seeing it in the string.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Author Comment

by:thenelson
ID: 41776904
Concatenation is valid in a SQL query.
Here is that SQL string converted to design view:
design viewAnd here are the results of that SQL string:
datasheet view
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 41776918
Yes, concatenation is allowed but we should not be seeing strName  in the query so you didn't post the strSQL as the query engine is seeing it.  You posted the code that builds it.
0
 
LVL 39

Author Comment

by:thenelson
ID: 41777168
strName  is not in strSQL when it is sent to Access.
Here is strSQL as sent to Access again:
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sandra Smith") OR ((Contacts.CompanyName)="Sandra Smith")
0
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 166 total points
ID: 41778197
Try to see whether this helps you:
https://support.microsoft.com/en-us/kb/303257
0
 
LVL 39

Author Comment

by:thenelson
ID: 41778270
xtermie,

I don't think that article applies here. I have the latest edition of the Jet Service Pack; I am not getting the error mentioned in the article and as you see, I have no joins in the query.
0
 
LVL 39

Accepted Solution

by:
thenelson earned 0 total points
ID: 41788432
I have resolved the problem by referencing the back end instead of the front end.
0
 
LVL 39

Author Closing Comment

by:thenelson
ID: 41793920
I found the solution
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now