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

OK, glad we seem to have got somewhere useful.

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

"KMikaela" <KMikaela@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E2907BC3-D16E-475C-A403-D7797C77A4FD@xxxxxxxxxxxxxxxx
Dear Peter,

So far (knock wood) this has been very successful. What I did was save the
data connection file created to the database folder and created a dummy user
so all users could open templates with out the password issue. This
connection is much faster and seems far more stable. Thank you so much for
your help, I appreciate it greatly as do the many users.

"KMikaela" wrote:

Dear Peter,

Thank you so much for your time and expertise. I will try this process
today and let you know how it goes.

Mikaela

"Peter Jamieson" wrote:

> > 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

.