Solved

Problem accessing MS access database from MS Word

Posted on 2016-08-29
14
46 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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
Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

 
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 37

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 37

Expert Comment

by:PatHartman
ID: 41776564
The concatenation should have been resolved.  We should not be seeing it in the string.
0
 
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 37

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 18

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

734 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