Database engine cannot find the input table or query

[theConjurian]

I have an application that has been performing a mail merge successfully for some time. I recently performed the following structural changes to the database:
1. I converted from .mdb to .accdb
2. Later I split the application and database to frontend/backend which reverted the types to .mdb

In the process I started to get the error, "The Microsoft Access database engine cannot find the table or query 'contact_Select_Quer'

The relevant code follows:

With CurrentDb
If QueryExists("Contact_Select_Query") Then
.QueryDefs.Delete ("Contact_Select_Query")
End If

strContactSelect = "SELECT * FROM Contacts WHERE [Contacts].[ContactID] = " & Forms![Contacts Browse]!ContactID & ";"

Set qdfNew = .CreateQueryDef("Contact_Select_Query", strContactSelect)

.Close
End With

Call cmdFileDialog(doc, logCancelFlag)

If logCancelFlag = False Then
DoCmd.RunMacro "mcrHide"

Set objfso = CreateObject("Scripting.FileSystemObject")
Set objfile = objfso.GetFile(doc)
strFileName = objfso.GetFileName(objfile)

Set objWord = GetObject(doc)

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
SQLStatement:="SELECT * FROM Contact_Select_Query", _
SubType:=wdMergeSubTypeWord

' NOTE: The actual merge is executed manually

DoCmd.RunMacro "mcrShow"
End If

It appears as if Word is not getting the correct query name, even though I CAN connect to the query from Word after the connection fails. Can anyone suggest what might have changed?

 

[dhookom]

I would use faq701-7433 rather than deleting and recreating a query.

Duane
Hook'D on Access
MS Access MVP

 

[theConjurian]

Duane:
Although changing the query property may be a better approach to setting the query parameter, this does nothing to solve the problem.

When Word starts up, it presents the usual message:

Opening this document will run the following SQL command:
SELECT * FROM 'Contact_Select_Query'

Clicking on yes, Word then opens the document and throws the following error:

Error has occurred: The Microsoft Access database engine cannot find the input table or query 'contact_Select_Quer'

 

[dhookom]

Does the error message actually omit the "y" at the end or is that a typo?

What version of Office are you using?

Duane
Hook'D on Access
MS Access MVP

 

[theConjurian]

The message reads:

Error has occurred: The Microsoft Access database engine cannot find the input table or query 'ontact_Select_Quer'

The leading 'C' and the trailing 'y' are BOTH missing.

I'm runninng:
Windows 7
Office 2010

 

[dhookom]

How about if you change the code to:

SubType:=wdMergeSubTypeAccess

Duane
Hook'D on Access
MS Access MVP

 

[theConjurian]

Same error and Word opens two versions of the document (one is the full screen reading view).

 

[dhookom]

It's probably not the answer you want to hear but I typically create a delimited text file from Access and then have the Word document use it.

Duane
Hook'D on Access
MS Access MVP

 

[theConjurian]

As you said, Duane, that REALLY doesn't answer the question. Nor does it explain what Word is doing with the query.

 

[dhookom]

What it's also suggesting is that I don't work the way that you do and don't have time to create a test environment to see if it works for me. Have you attempted to simplify your code down to the basics and see if it works? Maybe hard code a simple table and document.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Since it is split my guess is that the CurrentDB is not pointing to the correct db therefore it cannot find the query. I think the name issue is just a problem in the error message and not a cause. I would be a little more specific in this case and error check this to see if it is correct. I think the problem line is here:

Name:=CurrentDb.Name

So try this

dim db as dao.database
dim dbName as string

set db = currentDb
dbname = db.name
'verify
debug.print dbName
If QueryExists("Contact_Select_Query") Then
   db.QueryDefs.Delete ("Contact_Select_Query")
End If
strContactSelect = "SELECT * FROM Contacts WHERE [Contacts].[ContactID] = " & Forms![Contacts Browse]!ContactID & ";"
Set qdfNew = db.CreateQueryDef("Contact_Select_Query", strContactSelect)
Call cmdFileDialog(doc, logCancelFlag)
If logCancelFlag = False Then DoCmd.RunMacro "mcrHide"
Set objfso = CreateObject("Scripting.FileSystemObject")
Set objfile = objfso.GetFile(doc)
strFileName = objfso.GetFileName(objfile)
Set objWord = GetObject(doc)
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
'To verify if my assumption is correct
debug.print "The currentDB is: " & currentdb.name
DbName, _
SQLStatement:="SELECT * FROM Contact_Select_Query", _
SubType:=wdMergeSubTypeWord
' NOTE: The actual merge is executed manually
DoCmd.RunMacro "mcrShow"
End If

There are some specific differences when you split a database how DAO references.

 

[MajP]

Also you may want to read this.

https://social.msdn.microsoft.com/F...ject-invalid-or-not-set-error?forum=accessdev

You need to be careful when working with currentdb. Currentdb is not an object, but a function. It is a function that returns a pointer to the current db. Especially when working with any automation, you may want to explicitly set a reference. The object no longer valid error, can drive you crazy if not aware of this.

 

[theConjurian]

Thank you MajP.

I'm not sure I get why the pointer would change, but that is for another time.

The code you posted, however, does not compile. It fails at

dbname = db.name

with the error "object required. What am I missing?

 

[dhookom]

I would simplify to the lowest point possible and rebuild. Check all of the merge fields and maybe remove them one at a time to see if the problem goes away.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What if you replace the currentdb with dbEngine.Workspaces(0).Databases(0)
The only way you could get that error if for some reason currentdb is not returning a pointer to the database. Could be some kind of corruption. I would create a new fresh front end. Then import in your forms, reports, queries, and modules. Compact and repair. Make sure to readd all references.