[JimG]

We have both Word 2003 and Access 2007 published on a Citrix Server and
cannot upgrade Word to 2007 (easy solution I know). When we create a mail
merge in Word 2003 it does not see an Access 2007 accdb database (we cannot
change to a Access 2003 mdb format - the other easy solution). Is there an
add-in or update that will allow Word 2003 to connect to an Access 2007 accdb
database?

[Peter Jamieson]

1. I can't test this myself but assuming
a. you are trying to connect using OLE DB (the default for Access
databases in general)
b. have Access 2007 installed on the user's machine (and therefore
have the ACE OLE DB provider installed

then I think you will be able to open these database using a .udl or
..odc file. Typically you would need one such file for each database,
although with some VBA you could probably avoid that.

For example, suppose your database is called c:\mydbs\myacedb.accdb.
Then I think you will be able to...
a. use Notepad to create a text file containing the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydbs\myacedb.accdb;

(the Provider string should all be on one line, and there is a single
blank line after it)

b. rename it to be a .udl file
c. when you connect to a data source, select that file in Select Data
Source.

A .udl only lets you specify the database. A .odc lets you specify
either the database (so that the user gets to select a table) or a table
within a database. FWIW I haven't been able to get .udl s to work on
Word 2007/Vista so perhaps .odc is a better long term way to go.

You can create a suitable .odc in Word 2003 by...
a. start the procedure to connect tot he data source
b. in Select Data Source, click the New Source... button at the bottom
of the dialog box
c. In the Data COnnection Wizard, select Other/Advanced and click the
Next button
d. In the Provider tab, select the provider you need - in this case
the ACE 12.0 provider - and click Next
e. In the Connection tab, enter/browse for and select the database
name. Click OK (try the Test Connection button first if you like)
f. read the following dialogs carefully and respond as appropriate.
g. eventually you get to save a .odc file, which you are then
immediately prompted to select. Select it!

The .odc file is an HTML file with some XML islands and some "Behaviour"
code for use with IE. Once you have made one to connect to a database,
and one to connect to a table, you should be able to figure out how to
make others using Notepad, if that's what you need. In fact you can
strip a lot of the stuff (e.g. Behaviour code) out of it. One wrinkle is
that for some reason Word wrongly gets the table name from the

meta name=Table content="whatever"

element rather than using the CommandText element.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

JimG wrote:
We have both Word 2003 and Access 2007 published on a Citrix Server and
cannot upgrade Word to 2007 (easy solution I know). When we create a mail
merge in Word 2003 it does not see an Access 2007 accdb database (we cannot
change to a Access 2003 mdb format - the other easy solution). Is there an
add-in or update that will allow Word 2003 to connect to an Access 2007 accdb
database?

[Graham Mayor]

This sounds promising, but the OP may not have ACE OLEDB 12.0 available. If
not it can be downloaded from
http://www.microsoft.com/downloads/d...displaylang=en -
and it is then listed as Microsoft Office 12.0 Access Database Engine OLE DB
Provider.
That aside it certainly works with Word 2003 to connect the accdb file via
the odc file.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
1. I can't test this myself but assuming
a. you are trying to connect using OLE DB (the default for Access
databases in general)
b. have Access 2007 installed on the user's machine (and therefore
have the ACE OLE DB provider installed

then I think you will be able to open these database using a .udl or
.odc file. Typically you would need one such file for each database,
although with some VBA you could probably avoid that.

For example, suppose your database is called c:\mydbs\myacedb.accdb.
Then I think you will be able to...
a. use Notepad to create a text file containing the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydbs\myacedb.accdb;

(the Provider string should all be on one line, and there is a single
blank line after it)

b. rename it to be a .udl file
c. when you connect to a data source, select that file in Select Data
Source.

A .udl only lets you specify the database. A .odc lets you specify
either the database (so that the user gets to select a table) or a
table within a database. FWIW I haven't been able to get .udl s to
work on Word 2007/Vista so perhaps .odc is a better long term way to
go.
You can create a suitable .odc in Word 2003 by...
a. start the procedure to connect tot he data source
b. in Select Data Source, click the New Source... button at the
bottom of the dialog box
c. In the Data COnnection Wizard, select Other/Advanced and click the
Next button
d. In the Provider tab, select the provider you need - in this case
the ACE 12.0 provider - and click Next
e. In the Connection tab, enter/browse for and select the database
name. Click OK (try the Test Connection button first if you like)
f. read the following dialogs carefully and respond as appropriate.
g. eventually you get to save a .odc file, which you are then
immediately prompted to select. Select it!

The .odc file is an HTML file with some XML islands and some
"Behaviour" code for use with IE. Once you have made one to connect
to a database, and one to connect to a table, you should be able to
figure out how to make others using Notepad, if that's what you need.
In fact you can strip a lot of the stuff (e.g. Behaviour code) out of
it. One wrinkle is that for some reason Word wrongly gets the table
name from the
meta name=Table content="whatever"

element rather than using the CommandText element.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

JimG wrote:
We have both Word 2003 and Access 2007 published on a Citrix Server
and cannot upgrade Word to 2007 (easy solution I know). When we
create a mail merge in Word 2003 it does not see an Access 2007
accdb database (we cannot change to a Access 2003 mdb format - the
other easy solution). Is there an add-in or update that will allow
Word 2003 to connect to an Access 2007 accdb database?

[JimG]

Thanks Peter
I will pass your suggestions on to the Citrix techs. They have to implement
this type of change.
Jim

"Peter Jamieson" wrote:

1. I can't test this myself but assuming
a. you are trying to connect using OLE DB (the default for Access
databases in general)
b. have Access 2007 installed on the user's machine (and therefore
have the ACE OLE DB provider installed

then I think you will be able to open these database using a .udl or
..odc file. Typically you would need one such file for each database,
although with some VBA you could probably avoid that.

For example, suppose your database is called c:\mydbs\myacedb.accdb.
Then I think you will be able to...
a. use Notepad to create a text file containing the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydbs\myacedb.accdb;

(the Provider string should all be on one line, and there is a single
blank line after it)

b. rename it to be a .udl file
c. when you connect to a data source, select that file in Select Data
Source.

A .udl only lets you specify the database. A .odc lets you specify
either the database (so that the user gets to select a table) or a table
within a database. FWIW I haven't been able to get .udl s to work on
Word 2007/Vista so perhaps .odc is a better long term way to go.

You can create a suitable .odc in Word 2003 by...
a. start the procedure to connect tot he data source
b. in Select Data Source, click the New Source... button at the bottom
of the dialog box
c. In the Data COnnection Wizard, select Other/Advanced and click the
Next button
d. In the Provider tab, select the provider you need - in this case
the ACE 12.0 provider - and click Next
e. In the Connection tab, enter/browse for and select the database
name. Click OK (try the Test Connection button first if you like)
f. read the following dialogs carefully and respond as appropriate.
g. eventually you get to save a .odc file, which you are then
immediately prompted to select. Select it!

The .odc file is an HTML file with some XML islands and some "Behaviour"
code for use with IE. Once you have made one to connect to a database,
and one to connect to a table, you should be able to figure out how to
make others using Notepad, if that's what you need. In fact you can
strip a lot of the stuff (e.g. Behaviour code) out of it. One wrinkle is
that for some reason Word wrongly gets the table name from the

meta name=Table content="whatever"

element rather than using the CommandText element.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

JimG wrote:
We have both Word 2003 and Access 2007 published on a Citrix Server and
cannot upgrade Word to 2007 (easy solution I know). When we create a mail
merge in Word 2003 it does not see an Access 2007 accdb database (we cannot
change to a Access 2003 mdb format - the other easy solution). Is there an
add-in or update that will allow Word 2003 to connect to an Access 2007 accdb
database?

[JimG]

Thanks Graham
I will pass your suggestions and link on to the Citrix techs. They have to
implement this type of change.
Jim


"Graham Mayor" wrote:

This sounds promising, but the OP may not have ACE OLEDB 12.0 available. If
not it can be downloaded from
http://www.microsoft.com/downloads/d...displaylang=en -
and it is then listed as Microsoft Office 12.0 Access Database Engine OLE DB
Provider.
That aside it certainly works with Word 2003 to connect the accdb file via
the odc file.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
1. I can't test this myself but assuming
a. you are trying to connect using OLE DB (the default for Access
databases in general)
b. have Access 2007 installed on the user's machine (and therefore
have the ACE OLE DB provider installed

then I think you will be able to open these database using a .udl or
.odc file. Typically you would need one such file for each database,
although with some VBA you could probably avoid that.

For example, suppose your database is called c:\mydbs\myacedb.accdb.
Then I think you will be able to...
a. use Notepad to create a text file containing the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydbs\myacedb.accdb;

(the Provider string should all be on one line, and there is a single
blank line after it)

b. rename it to be a .udl file
c. when you connect to a data source, select that file in Select Data
Source.

A .udl only lets you specify the database. A .odc lets you specify
either the database (so that the user gets to select a table) or a
table within a database. FWIW I haven't been able to get .udl s to
work on Word 2007/Vista so perhaps .odc is a better long term way to
go.
You can create a suitable .odc in Word 2003 by...
a. start the procedure to connect tot he data source
b. in Select Data Source, click the New Source... button at the
bottom of the dialog box
c. In the Data COnnection Wizard, select Other/Advanced and click the
Next button
d. In the Provider tab, select the provider you need - in this case
the ACE 12.0 provider - and click Next
e. In the Connection tab, enter/browse for and select the database
name. Click OK (try the Test Connection button first if you like)
f. read the following dialogs carefully and respond as appropriate.
g. eventually you get to save a .odc file, which you are then
immediately prompted to select. Select it!

The .odc file is an HTML file with some XML islands and some
"Behaviour" code for use with IE. Once you have made one to connect
to a database, and one to connect to a table, you should be able to
figure out how to make others using Notepad, if that's what you need.
In fact you can strip a lot of the stuff (e.g. Behaviour code) out of
it. One wrinkle is that for some reason Word wrongly gets the table
name from the
meta name=Table content="whatever"

element rather than using the CommandText element.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

JimG wrote:
We have both Word 2003 and Access 2007 published on a Citrix Server
and cannot upgrade Word to 2007 (easy solution I know). When we
create a mail merge in Word 2003 it does not see an Access 2007
accdb database (we cannot change to a Access 2003 mdb format - the
other easy solution). Is there an add-in or update that will allow
Word 2003 to connect to an Access 2007 accdb database?

[Peter Jamieson]

Thanks for the confirmation and handy extra info.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Graham Mayor wrote:
This sounds promising, but the OP may not have ACE OLEDB 12.0 available. If
not it can be downloaded from
http://www.microsoft.com/downloads/d...displaylang=en -
and it is then listed as Microsoft Office 12.0 Access Database Engine OLE DB
Provider.
That aside it certainly works with Word 2003 to connect the accdb file via
the odc file.