?
Solved

Problem accessing MS access database from MS Word

Posted on 2016-08-29
14
Medium Priority
?
60 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 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 668 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 42

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 42

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 42

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 42

Assisted Solution

by:PatHartman
PatHartman earned 668 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 19

Assisted Solution

by:xtermie
xtermie earned 664 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The best software application must always have an error handling tool
The most emerging queries of the user is to upload Outlook OST mailboxes to Office 365 account. So, here we will provide the manual as well as an automated solution for to transfer OST file to MS Office 365 in an absolute manner.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

569 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