Re: Word 2003 Closes upon update of sort order of table using Acce

It is amazing that so much has
changed from 2000 to 2003, as the workgroup was not problem, nor was the
number of records when we were using 2000.

Yes, I can only guess but suspect that when the developers tried to incorporate OLE DB, they probably found themselves with a lot more problems than they bargained for and probably too little time to design a better way through them. The vast majority of the changes occurred between Word 2000 and Word 2002, BTW.

For an OLE DB data source you really need a .udl or .odc file rather than a DSN (which are for ODBC data sources). You don't actually have to have either, as long as your users do not need to set up their data sources from scratch (and you may find that they have to) - in that case, using a .odc can make things rather easier for them. .udl s can also be used but cannot embed a table or query name.

Whether either ODBC or OLE DB can "see" your query depends on the type of query. For OLE DB, I have verified that a simple TOP 10 query that references a single local table in a .mdb is visible to OLE DB, but if your query has other complications it might well be "invisible". You obviously need to ensure that whatever Access login you use has all the necessary permissions to run the query successfully.

If you want to try to connect with ODBC, you usually have to ensure that
a. the database name is correctly selected when you see the tables/queries dialog box. It can be hard to tell because you can't inspect long file names within the database name text box.
b. you have clicked the "options" button and selected all the options in there

Both OLE DB and ODBC connections from Word can also suffer from another problem - if the pathnames of the access database and the workgroup security database (mdw) are too long, you may have problems when you try to connect, or more likely, when you try to re-open the mail merge main document. This is because Word can only save a 255/256-character connection string, and the way Word works with OLEDB, there is no way to keep that string short by specifying only the settings you need either in VBA or a .odc- Word always adds in a bunch of standard settings as well. If you encounter that problem then I suspect that the only way through it is either to ensure that your ..mdb and .mdw are located at short path names or perhaps to create a further ..mdb which (e) has a short path name, (d) uses the same .mdw and (e) links to your first .mdb.

Anyway, let's step through the basic approach needed to make the connection in OLE DB /without/ setting up a .odc:

a. ensure Word Tools|Options|General|Confirm conversions at open is checked
b. start the process of connecting to a data source
c. when the Select Data Source dialog opens, select the .mdb and click Open. A Confirm Data Source dialog box should display - probably showing OLE DB, ODBC and DDE options.
d. Select OLE DB Database files and click OK A Data Link Properties dialog box should display, with the COnnection tab open.
e. (The first time you do this, just so you fully understand what's going on, click the Provider tab and ensure that "Microsoft Jet 4.0 OLE DB Provider" is selected. Then click the Connection tab again
f. enter the name of your .mdb or select it using the ... button
g. enter the user name
h. uncheck blank password and enter the Password.
i. (If you click Test connection at this point, the connection will probably fail. If it does not, it suggests that someone has configured some Jet registry properties so that the workgroup security database for your database is the default "system database". However, for the purposes of this experiment, you should probably enter the database name as shown in the next few steps)
j. click the All tab and double-click on the property named "Jet OLEDB:System database". Enter the full path name of your workgroup security file. Select the pathname and use ctrl-C to copy it to the clipboard.
k. go back to the Connection tab and click the Test connection button. If you don't see "Test connection succeeded" then please let us know what message you do see (and I may not be able to get you any further)
l. Click OK. For the benefit of anyone else reading this, this is the point where it is easy to get into difficulties and which I have never understood very well before. What happens is that Word, or OLE DB (not sure which) tries to retrieve the list of tables and queries in the specified database. However, what is potentially very confusing is that if the database only contains a single visible table, the process selects that table without tellng you, and the dialog box redisplays, but with your login info wiped out. What's more, filling in your login info again doesn't help. But if that is what happens, skip to step (). Otherwise, if there is more than one visible table/query, you should see a Select Table dialog box.
m. If the query you need to connect to is not listed, we may not be able to use this approach. But for now it may be worth completing this process and selecting one that is listed. Click OK
n. The Data Link Properties dialog box is redisplayed. However, most of the stuff we entered before has now been tossed away - i.e. it was only there to allow us to select a specific database and table.
o. fill in the correct user name again, uncheck blank password again and fill in the password again
p. click the All tab and double-click on the property named "Jet OLEDB:System database" again. Use ctrl-v to insert the full path name of your workgroup security file that you copied to the clipboard in step (j). Or re-enter the pathname the hard way...
q. click Test connection again if you like. Let's hope you see "Success"
r. You now have one final choice to make. You can either check "Allow saving password" or leave it unchecked. Either way, when you check OK, Word should connect tot he data source. However, when you save and close and re-open the mail merge main document, if you /do/ check this box then the login info. will be stored in the .doc and the user should not see the Data Link Properties dialog box. If you do not check the box, the information is not stored and the user wil be faced with the data link properties dialog when they re-open the mail merge main document. However, at that stage, they should only have to enter the user name, uncheck blank password, and enter their password - all the other info (provider name, database name, security database name) should have been retained. But I think you need to verify this on your own system before subjecting users to it. Also, since it appears that the user could easily check thiis box when they open the document and then save the document and transmit it, there may be further security implications.
s. Click OK. If all has gone well, you should be connected to the data source and be able to proceed inthe usual way.

Phew!

If you got that far, well done! If you still couldn't see the query in step (m), it is still just about possible that the same thing can be done using a ..odc or VBA (i.e. I have sometimes discovered that even though the query/table is not listed, you can still connect to it). But maybe you could post the query code here?

If you want or need to create a .odc, let's leave that for another time.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:86341D97-1831-45B1-84BB-605D02A69685@xxxxxxxxxxxxxxxx
Dear Peter,

Yes the security thing is why I haven't used OLE DB as I wasn't seeing the
query when I used this, despite setting up a DSN. It walks through the
process, and sees the database, and then in the end does not connect, and
doesn't give an error.

We are using a workgroup file, and not a user password, and opening the
database does bring up the user log in. It is amazing that so much has
changed from 2000 to 2003, as the workgroup was not problem, nor was the
number of records when we were using 2000. All other features work, although
there have been some issues with 2000 users, as we have had a partial roll
out of 2003. In testing, all worked well, I believe it was because the data
was static.

"Peter Jamieson" wrote:

> sorry for adding the other threads, wasn't sure of
> the process.

No problem - people sometimes do it when they don't get a reply, but
sometimes that can be because the original responder is away for a while, as
in this case.

> Yes this is still a DDE

OK, I don't think the situation has changed for DDE from the one I described
in my earlier message. i.e. what you probably have to do to avoid all the
problems is both the "TOP 1000" /and/ change to OLE DB. I suspect in your
case the problem then is that you are using a secure database and in that
case there is an additional problem because you may have to use a .odc file
to get Word to connect to the database, and/or end up embedding the
necessary security informaiton somewhere inside the Word document.

But can you confirm/deny that the secure database thing is the reason why
you haven't moved to OLE DB? If not, what is? Are you using workgroup
security rather than a database password?
--
Peter Jamieson
http://tips.pjmsn.me.uk

"KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D1102DB5-ABDB-4432-82AA-45F55F10D17E@xxxxxxxxxxxxxxxx
> Yes this is still a DDE, sorry for adding the other threads, wasn't > sure
> of
> the process.
>
> Mikaela
>
> "Peter Jamieson" wrote:
>
>> Is this still DDE (as your other messages suggest) or is it OLE DB >> now?
>>
>> -- >> Peter Jamieson
>> http://tips.pjmsn.me.uk
>>
>> "KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:84E1CE35-562D-435A-AE68-098E2ED42296@xxxxxxxxxxxxxxxx
>> > Dear Peter,
>> >
>> > It seems the "Find Entry" button on the Mail merge tool bar only >> > works
>> > after
>> > you open the "mail merge recipeients" Is this because the items in >> > the
>> > data
>> > source have changed and it is a query? Is there a way to set this >> > to
>> > do
>> > automatically or are users going to have to refresh this each time >> > they
>> > enter
>> > a template? I have well over 200 templates in use, and I am at a >> > loss
>> > for
>> > why there are so many issues with mail merge in Word 2003.
>> >
>> > Many thanks,
>> >
>> > Mikaela
>> >
>> > "KMikaela" wrote:
>> >
>> >> Hi Peter,
>> >>
>> >> > Have you tried defining an Access query that selects the >> >> > necessary
>> >> > 1000
>> >> > records and using that as the data source?
>> >>
>> >> This is exactly what I'm doing, so it is working well.
>> >>
>> >> Thank you.
>> >>
>> >> "Peter Jamieson" wrote:
>> >>
>> >> > Hello Mikaela
>> >> >
>> >> > > Also, I have been
>> >> > > using the Find record button on the Mail Merge tool bar, sorry >> >> > > for
>> >> > > not
>> >> > > being
>> >> > > clearer.
>> >> >
>> >> > FWIW It is not your fault or my fault that it does not work, and
>> >> > that
>> >> > this
>> >> > kind of predefined limit is not specified.
>> >> >
>> >> > <<
>> >> > > as I had tried the OLE
>> >> > > DB connection and it doesn't see the table in Access at all So >> >> > > I
>> >> > > will
>> >> > > just
>> >> > > need to filter all tables as we only search the
>> >> > > most recently entered records.
>> >> >
>> >> > Have you tried defining an Access query that selects the >> >> > necessary
>> >> > 1000
>> >> > records and using that as the data source?
>> >> >
>> >> > -- >> >> > Peter Jamieson
>> >> > http://tips.pjmsn.me.uk
>> >> >
>> >> > "KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> > news:B843E890-6826-4845-A75C-E60E3AE12F75@xxxxxxxxxxxxxxxx
>> >> > > Dear Peter,
>> >> > >
>> >> > > Thank you so much for your detailed and quick reply. I had
>> >> > > thought
>> >> > > it
>> >> > > might
>> >> > > be the number of records in Word 2003 but had not found any >> >> > > data
>> >> > > to
>> >> > > indicate
>> >> > > the total capacity of records (they really should write that >> >> > > down
>> >> > > some
>> >> > > where
>> >> > > - but I gues most people don't use mail merge for more than >> >> > > 10,000
>> >> > > records),
>> >> > > although it worked beautifully in Word 2000, ah well. Also, I
>> >> > > have
>> >> > > been
>> >> > > using the Find record button on the Mail Merge tool bar, sorry >> >> > > for
>> >> > > not
>> >> > > being
>> >> > > clearer.
>> >> > >
>> >> > > Prior to receiving your email I had tried putting a filter on >> >> > > the
>> >> > > table to
>> >> > > only pull the top 1,000 records and that worked well, as I had
>> >> > > tried
>> >> > > the
>> >> > > OLE
>> >> > > DB connection and it doesn't see the table in Access at all, so >> >> > > it
>> >> > > is
>> >> > > unable
>> >> > > to connect. So I will just need to filter all tables as we >> >> > > only
>> >> > > search
>> >> > > the
>> >> > > most recently entered records.
>> >> > >
>> >> > > I do appreciate your respons, I am very glad I could just >> >> > > confirm
>> >> > > what the
>> >> > > problem was, as there was nothing out there saying how many
>> >> > > records
>> >> > > the
>> >> > > dialogue could hold, I now now how to proceed.
>> >> > >
>> >> > > Many thanks,
>> >> > >
>> >> > > Mikaela
>> >> > >
>> >> > > "Peter Jamieson" wrote:
>> >> > >
>> >> > >> Unfortunately, there is a cluster of unhelpful problems in >> >> > >> this
>> >> > >> area -
>> >> > >> i.e.,
>> >> > >> solving one usually means that you face another.
>> >> > >>
>> >> > >> To recap...
>> >> > >>
>> >> > >> 1. Using the Find in Field function from (e.g.) the Mailmerge
>> >> > >> toolbar
>> >> > >> fails, certainly with this type of data source and connection
>> >> > >> type
>> >> > >> (DDE).
>> >> > >>
>> >> > >> 2. Using the Find button from within the Mail Merge >> >> > >> Recipients
>> >> > >> dialog
>> >> > >> box
>> >> > >> seems to work OK here, but
>> >> > >> a. it can take a long time to populate the dialog box
>> >> > >> b. the dialog box only holds 10,000 records, and Word can
>> >> > >> crash
>> >> > >> if
>> >> > >> you
>> >> > >> have more (in fact,Word's processing clearly changes at the
>> >> > >> 10,000
>> >> > >> record
>> >> > >> mark in other places that do not have anything to do with this
>> >> > >> dialog box
>> >> > >>
>> >> > >> 3. You can solve (1) and 2(a) by using OLE DB to connect.
>> >> > >> However,
>> >> > >> a. the dialog box still only holds 10,000 records and the
>> >> > >> Find
>> >> > >> button
>> >> > >> in
>> >> > >> the box will not find a record beyond that
>> >> > >> b. there are potentially several reasons why an OLE DB
>> >> > >> connection
>> >> > >> will
>> >> > >> not work for you
>> >> > >>
>> >> > >> 4. You can work around 3(a) by using the Find button on the
>> >> > >> MailMerge
>> >> > >> toolbar rather than the Find button in the Mail Merge >> >> > >> Recipients
>> >> > >> Dialog
>> >> > >> box.
>> >> > >> You may also notice that the dialog itself is different - with
>> >> > >> DDE,
>> >> > >> you
>> >> > >> have
>> >> > >> to select a field to search.
>> >> > >>
>> >> > >> 5. The question is, can you work around all the problems >> >> > >> alluded
>> >> > >> to
>> >> > >> in
>> >> > >> 3(b),
>> >> > >> which include:
>> >> > >> a. can you connect to a workgroup-secured database via OLE >> >> > >> DB?
>> >> > >> If
>> >> > >> so,
>> >> > >> can
>> >> > >> you do so in a way that does not significantly undermine
>> >> > >> workgroup
>> >> > >> security
>> >> > >> (e.g. by embedding plain text login/password info in the Word
>> >> > >> document or
>> >> > >> ..odc file)?
>> >> > >> b. can OLE DB "see" the tables/queries you want to use - e.g.
>> >> > >> are
>> >> > >> they
>> >> > >> linked tables or parameter queries? And if they are parameter
>> >> > >> queries,
>> >> > >> how
>> >> > >> do you get Word to ask for the paramters?
>> >> > >> c. does OLE DB return the records you expect (e.g. it >> >> > >> probably
>> >> > >> won't if
>> >> > >> you
>> >> > >> are using wildcard characters in LIKE expressions in queries.
>> >> > >> d. perhaps other stuff that I have forgotten.
>> >> > >>
>> >> > >> 6. So can you tell us which of those problems you would need >> >> > >> to
>> >> > >> overcome
>> >> > >> or
>> >> > >> any other reasons why you couldn't move to OLE DB?
>> >> > >>
>> >> > >> -- >> >> > >> Peter Jamieson
>> >> > >> http://tips.pjmsn.me.uk
>> >> > >>
>> >> > >> "KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in >> >> > >> message
>> >> > >> news:3D376B8B-51CE-42AF-A9FB-732AD93CBABF@xxxxxxxxxxxxxxxx
>> >> > >> >I also have the issue of word closing, and have recently
>> >> > >> >upgraded
>> >> > >> >from
>> >> > >> > Windows 2000 to Windows XP and from Office 2000 to Office >> >> > >> > 2003.
>> >> > >> > In
>> >> > >> > templates
>> >> > >> > that functioned well before, the "find" command no longer
>> >> > >> > locates
>> >> > >> > the
>> >> > >> > record
>> >> > >> > and when I try to save the sort order on the Access data, it
>> >> > >> > causes
>> >> > >> > word
>> >> > >> > to
>> >> > >> > close. The database table has over 19,000 records with 78
>> >> > >> > field
>> >> > >> > names,
>> >> > >> > so
>> >> > >> > I'm not sure if this poses a problem for word 2003, as it
>> >> > >> > worked
>> >> > >> > well
>> >> > >> > in
>> >> > >> > 2000. I am using a secure database with a DDE connection so
>> >> > >> > I'm
>> >> > >> > not
>> >> > >> > sure
>> >> > >> > if
>> >> > >> > that is the issue either. I have also installed the mso.dll
>> >> > >> > fix,
>> >> > >> > but
>> >> > >> > still
>> >> > >> > having the same issue. We use over 20 templates with mail
>> >> > >> > merge
>> >> > >> > to
>> >> > >> > this
>> >> > >> > table and users are having to scroll through endless records
>> >> > >> > any
>> >> > >> > assitance
>> >> > >> > would be greatly appreciated.
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>>
>>



.