Access automation with Word (Access97->XP)

Topic

New Reply

I want to use Word to get at some data stored in an Access 97 Jet database. I bet there’s a lot of overhead opening Access to get the data and I’m wondering if I use an ADO connection string (instead of DAO), will this mean I’m not really opening Access and therefore mean lower overhead/faster access? I’m running Office 97, but am willing to upgrade if ADO will give me better performance in this scenario.
Many thanks,
Gwenda

Reply | Quote

Replies

Hi Gwenda,
DDE is slow, but you could try using an ODBC data source – they are a fair bit quicker. ADO and Access97 don’t provide much capability, and I’m not sure how you would even force the use of ADO using Word Mail Merges. On the other hand, Office XP and its OLE DB data source are quicker than either. Unfortunately there are some issues with security and the Word mail merge. Just to clarify, I presume you are driving the automation from the Access database, but running the merge in Word. If that’s not what you are doing, explain and I’ll do my best to help.

Reply | Quote

Dearest lightning-fast Wendell,
I’m not doing a mail merge. I have VBA code in a Word template that needs to open a parameters table in Access to get the name of the writer, business name, and a bunch of other stuff that gets plugged into the letter. The data is stored in Access as part of a larger Access application. I’ll just open the recordset once, grab the data I need and load it into variables, close the recordset and go on with my processing in Word.

From your message, I judge that I should perhaps try an ODBC data source (I know that DDE stands for Dynamic Data Exchange, but I don’t know what it means). Or do you think I should upgrade to Office XP?
Thanks again,
Gwenda

Reply | Quote

ARGH! I’m so fast I assumed you were doing Mail Merges. Yes, where you are doing an automated generation of a document in Word, you are not doing DDE stuff, so it isn’t terribly sluggish when compared to Mail Merges. Actually, I don’t think you will get much of a speed boost using ADO as compared to DAO. Are you doing a one-off creating of a single document, or are you doing a multi-record document? We found that in doing a 600 page Word document based on about 4000 records of data, the automation solution created something that looked identical to a Word catalog style Mail Merge, and took less than 25% of the time. In that case we were using DAO and ODBC linked SQL Server tables. From your description, I gather you are going the other way and loading Access data into Word using VBA in Word – is that correct?

Reply | Quote

Gwenda,

If you use DAO (or ADO for that matter), you aren’t actually opening Access to get at your data. You are just using the Jet Engine.

All you have to do is set a reference, in Word, to Microsoft DAO 3.51 Object Library and Use code like this to access the data:

Dim db As Database
Dim strSQL As String
Dim rst As Recordset

‘Open database
Set db = OpenDatabase(“C:fullpathtoyourdatabasefile.mdb”)
strSQL = “SELECT * FROM tblContact”
‘Open Recordset
Set rst = db.OpenRecordset(strSQL)
‘Loop through the recordset
Do While Not (rst.EOF)
With Selection
.InsertAfter rst.Fields(1)
.InsertParagraphAfter
End With
rst.MoveNext
Loop

‘Close and release pointers
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Reply | Quote

Dear Wendell and Bryan,
Thank you so much for your help! Between your responses I have all the information I need. I’ve learned so much at Woody’s and am ever grateful to you.
Gwenda

Reply | Quote

Thinking about your performance question while I was out, the real issue doing a one-off document is that you have to start the Jet database engine each time. You might get a significant speed boost by migrating to 2000 or 2002 and using the Desktop Engine, which is really SQL Server in disguise. It runs as a background task or service, and would thus not require starting up. It does also have some inherent speed advantage in returning data, but that would likely be in the noise level, for what it’s worth. Have a good day.

Reply | Quote

Thanks Wendell. That’s exactly my concern. I’m going to try it first with DAO as Bryan suggested. Then if performance is poor, I’ll try it with the Desktop Engine (MSDE).

Reply | Quote