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:
![First popup]()
![Second popup]()
![Third popup]()
How do I stop these popups? Thanks.
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
How do I stop these popups? Thanks.
Microsoft AccessMicrosoft WordMicrosoft OfficeMicrosoft ApplicationsVisual Basic Classic
Last Comment
thenelson
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
here is a sample strSQL:
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sa
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sa
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.
ASKER
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])="""
strSQL = "SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & "" "" & [Contacts].[LastName])="""
The concatenation should have been resolved. We should not be seeing it in the string.
ASKER
Concatenation is valid in a SQL query.
Here is that SQL string converted to design view:
![design view]()
And here are the results of that SQL string:
![datasheet view]()
Here is that SQL string converted to design view:
And here are the results of that SQL string:
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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])="Sa
Here is strSQL as sent to Access again:
SELECT DISTINCT * FROM Contacts WHERE (([Contacts].[FirstName] & " " & [Contacts].[LastName])="Sa
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I found the solution
BTW: the "WIth" on line 3 of the code was included here in error. It is not in the actual code.