Link to home
Start Free TrialLog in
Avatar of thenelson
thenelson

asked on

Problem accessing MS access database from MS Word

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):User generated imageUser generated imageUser generated imageHow do I stop these popups?  Thanks.
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thenelson
thenelson

ASKER

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.
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.
here is a sample strSQL:
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sandra Smith") OR ((Contacts.CompanyName)="Sandra Smith")
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.
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 & """)"
The concatenation should have been resolved.  We should not be seeing it in the string.
Concatenation is valid in a SQL query.
Here is that SQL string converted to design view:
User generated imageAnd here are the results of that SQL string:
User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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")
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found the solution