DAO or ADO

[#DIV/0]

I use Word as the user interface for accessing, searching and updating
databases. Up to now I've stored the data in Excel workbooks but the problems
there are multiple access and some trouble getting Excel to quit properly
(apparently a known 'bug').

First question: is mdb the best database format to use in this case ?

Second question: what method should I use for calling up the database from
VBA ? The examples I've found here seem to tend towards DAO but I've found
lots of references on the web to ADO being better, faster, easier to program
etc. Since my experience with either is limited, I'm prepared for some long
hours of studying but I want to know which to concentrate on for automation
from Word.

Any advice, anecdotes, links that could help me make up my mind ?

 

[Shauna Kelly]

Hi David

It seems to me that you have two related questions:
- what format should I use for the data
- what method should I use to access that data from Word.

In your considerations, it may be worth bearing in mind that they are two
different questions.

The following provide simple examples of how to access data in an Excel file
using DAO:
Load a ListBox from a Named Range in Excel using DAO
http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

and

Retrieving Data from a Named Range in Excel using DAO
http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

However, that method is read-only: you can't write to the file using DAO.

For examples of using ADO, see
http://www.computorcompanion.com/LPMArticle.asp?ID=136
and
http://support.microsoft.com/kb/257819


I would also suggest that you look through the microsoft.access newsgroups.
They can advise about the several different formats that Access can use,
including mdb. If you ask a question there, let them know what version(s) of
Office your people are using, how many concurrent users you're likely to
have, and describe the size and complexity of the data.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[#DIV/0]

Hi Shauna,
I did indeed post two questions and as you note they are related so I posted
them together so people have the whole picture.
As I said, I am already reading from and writing to Excel from Word. I'm
using binding and the inability to close Excel reliably is causing havoc.
That's why I thought of switching to Access. The article you link to by Dian
(and her site) were my starting point...

Is an ADO connection to Excel not an option ? That would simplify things
considerably.

I'll wait to post to the Access groups because there may be Word-specific
issues that I'll have to cope with first.

Thanks,
--
David M
WinXP - Office2003 (Italian)

Hi David

It seems to me that you have two related questions:
- what format should I use for the data
- what method should I use to access that data from Word.

In your considerations, it may be worth bearing in mind that they are two
different questions.

The following provide simple examples of how to access data in an Excel file
using DAO:
Load a ListBox from a Named Range in Excel using DAO
http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

and

Retrieving Data from a Named Range in Excel using DAO
http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

However, that method is read-only: you can't write to the file using DAO.

For examples of using ADO, see
http://www.computorcompanion.com/LPMArticle.asp?ID=136
and
http://support.microsoft.com/kb/257819


I would also suggest that you look through the microsoft.access newsgroups.
They can advise about the several different formats that Access can use,
including mdb. If you ask a question there, let them know what version(s) of
Office your people are using, how many concurrent users you're likely to
have, and describe the size and complexity of the data.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Jezebel]

DAO is the older technology. It works well, it's quite quick, and it's easy
to learn.

ADO has more bells and whistles. It's more complicated and a little bit
harder to use; but worth the extra effort if you need to extra features. For
some applications it is noticably slower; but unless your app is seriously
data-intensive, this is unlikely to be an issue either way.

However, for working with Excel, both are overkill. The 'trouble getting
Excel to suit' is nearly always the result of a bug in your own VBA code:
Excel is very sensitive to unhandled errors, and its usual response is to
sulk and refuse to quit. I've seen several instances where the problem was a
statement like

xlRange.Sort Key1:=Range("A1:C10")

Called from Word VBA, neither Word nor Excel reports an error on the invalid
Range argument; but Excel will subsequently fail to quit.

 

[#DIV/0]

The 'trouble getting
Excel to suit' is nearly always the result of a bug in your own VBA code:
Excel is very sensitive to unhandled errors, and its usual response is to
sulk and refuse to quit. I've seen several instances where the problem was a
statement like

xlRange.Sort Key1:=Range("A1:C10")

Called from Word VBA, neither Word nor Excel reports an error on the invalid
Range argument; but Excel will subsequently fail to quit.

.. . . makes trouble-shooting a real bitch.

A previous post here pointed me to 'incomplete' internal references - don't
take for granted that Excel will remember anything one line to the next. So
every step of code needs full reference to the object
(ActiveWorkbook.ActiveSheet.ActiveCell all over the place). I also removed
and worked around any With statements because that can also upset Excel when
called from Word, and I tried leaving Excel visible.
None of that helped and I was looking at how well another form I made worked
so well with ADO and an mdb file. It's almost certainly overkill, but if it
works I'll go for it. I can explore the full potential of ADO at my leisure
but at least the thing will be running without problems.

 

[Shauna Kelly]

Hi David

don't take for granted that Excel will remember anything one line to the
next

Nor should we expect that Excel will remember anything from one line in Word
to the next.

If you run a line of code in Excel that is, say,
ActiveCell.Font.Bold = True
then it will work in Excel *only* because Excel is hard-wired into the VBA
in Excel.

Word knows nothing of such things. It, too, has Fonts and they have Bold
properties. But it hasn't a clue what an ActiveCell is. It may sometimes
guess, which is worse than your telling it what to do properly.

For what it's worth, I've written apps that read from Excel using DAO, and
apps that read and write to and from Excel using VBA where Excel was not
visible, where the file was opened read-only unless absolutely required,
could therefore be read by several users simultaneously, and with lots and
lots and lots of With...End With usage. I'm not sure that's my preferred way
of working, but it is quite robust once you get all the references sorted
out.

If your Excel code has trouble quitting Excel, then the first thing I'd
recommend is to make sure you have properly Unloaded all forms with Unload
MyForm, and set all module-wide object variables to Nothing.

For what it's worth, if you use DAO or ADO to read a data file from Word,
you'll need to use full referencing to their object models, just as you have
to use full referencing to Excel's object model when running code in Word.
For example, both DAO and ADO have a Recordset object, but Word won't know
which kind your code is referring to unless you tell it explicitly.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[#DIV/0]

Thanks Shauna,
I'll review ALL my refernces again and check all unloads, closes and quits.
I'd certainly prefer to keep things within Word and Excel as no-one in the
company knows Access but there are enough other people here who can get
around Excel that if something goes wierd, they can still get at their past
data.
The whole reason for me originally getting this project was the only person
who worked in Access had set up a load of little apps for different
departments and then he quit.

--
David M
WinXP - Office2003 (Italian)

Hi David

don't take for granted that Excel will remember anything one line to the
next

Nor should we expect that Excel will remember anything from one line in Word
to the next.

If you run a line of code in Excel that is, say,
ActiveCell.Font.Bold = True
then it will work in Excel *only* because Excel is hard-wired into the VBA
in Excel.

Word knows nothing of such things. It, too, has Fonts and they have Bold
properties. But it hasn't a clue what an ActiveCell is. It may sometimes
guess, which is worse than your telling it what to do properly.

For what it's worth, I've written apps that read from Excel using DAO, and
apps that read and write to and from Excel using VBA where Excel was not
visible, where the file was opened read-only unless absolutely required,
could therefore be read by several users simultaneously, and with lots and
lots and lots of With...End With usage. I'm not sure that's my preferred way
of working, but it is quite robust once you get all the references sorted
out.

If your Excel code has trouble quitting Excel, then the first thing I'd
recommend is to make sure you have properly Unloaded all forms with Unload
MyForm, and set all module-wide object variables to Nothing.

For what it's worth, if you use DAO or ADO to read a data file from Word,
you'll need to use full referencing to their object models, just as you have
to use full referencing to Excel's object model when running code in Word.
For example, both DAO and ADO have a Recordset object, but Word won't know
which kind your code is referring to unless you tell it explicitly.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word