Solved

Problem accessing MS access database from MS Word

Posted on 2016-08-29
14
35 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 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

20 Experts available now in Live!

Get 1:1 Help Now