I am updating from Office 2003 to Office 2010 and moving from a .mdb to a .accdb. The problem I have encountered is that the code is not able to open the database. I get this Message "Word was unable to open the data source." I have confirmed that the .OpenDataSource Name and sqlstatement values are correct and that the document file exists. I have also tested the SQL to verify that it works and returns records. I have included below the function that is used to access Word and create the document. This code has worked inheeh earlier version.
What do I need to do to allow Word to access the database?
Public Function docPrinter(inDoc As String, inSQL As String, inDB As String, _
inCnt As Integer, inPref As Boolean)
' Prints (or opens) the selected document where
' inDoc is the fully-qualified location of the document to be printed,
' inSQL is the complete SQL statement that will retrieve the correct records,
' inDB is the fully-qualified location of the database to be queried,
' inCnt is the number of copies required (ignored if the document is to be opened), and
' inPref is true if direct printing is desired (false will open the document in Word).
' inDoc = "C:\2009 Non-Certified Contract.doc"
' inSQL = "SELECT * FROM Combined WHERE [Contract Type] in ('N')"
' inDB = "C:\$User\Business Office\EmployeeContractsData.mdb"
Dim myWord As Object
Dim i As Integer
Set myWord = CreateObject("Word.Application")
myWord.Documents.Open inDoc, , True
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=inDB, sqlstatement:=inSQL
.ViewMailMergeFieldCodes = False
' Determine the actions to take (Print or Open).
If inPref Then
' Print the document
.Destination = wdSendToPrinter
For i = 1 To inCnt
.Execute pause:=False 'This will cause the output to be collated in complete sets.
docPrinter = "Printing is Done"
' Open the document with the merge completed.
.Destination = wdSendToNewDocument
.DisplayAlerts = wdAlertsNone
.Visible = True
.WindowState = wdWindowStateMaximize
docPrinter = "Document is open"
Set myWord = Nothing