Solved

Problem accessing MS access database from MS Word

Posted on 2016-08-29
14
43 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 36

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 36

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 36

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 36

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

756 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