Unable to open data source

[spy_234432]

This is not a mailmerge question, but I thought that someone might be
able to help since I have seen similar questions here. I am trying to
pull information out of an Access database into my word doc.

This won't work

{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select Priority from
\"case_summary_table\" }

But this will

{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select * from \"case_summary_table
\" }

The difference in the second one is that I am querying all of the
fields in the table.

Thanks in advance for your help.

 

[Peter Jamieson]

The syntax rules for Jet queries (whether you're connecting via DATABASE or
as a merge data source) are not at all clear - i.e. I have no idea why the
\" \" quoting syntax works when you use * but not otherwise.

However, my general experience so far is that
a. syntax using double-quotes seems to be a relatively recent innovation,
and it does not always seem to work as you might hope. The fact that the
double-quotes have to be quoted using \ in fields may also be a factor.
b. in this case any of the following would probably work:
no quotes (but you would need /some/ quoting if the table name contained
spaces:
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select Priority from
case_summary_table }
square brackets (Jet syntax AFAIK):
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select [Priority] from
[case_summary_table] }
back quotes (older? Jet syntax)
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select `Priority` from
`case_summary_table` }

Since the square brackets approach seems to work in most cases, I'd stick
with that. However, if you were connecting via OLE DB rather than ODBC you
might find that you also had to create and use a table alias.

Peter Jamieson

 

[Peter Jamieson]

BTW as far as I can tell, Word 2003 never uses ODBC directly to get data
from Access when it executes a DATABASE field. I suspect, but am not
completely sure, that it uses the OLE DB Provider for ODBC Drivers to do it.
The sreason I suspect that is because when you use OLE DB to open an Access
Mailmerge data source, Access YesNo field values come through as "True" and
"False". With ODBC the values are 1 and 0. If you set up a .odc that uses
the OLEDB Provider for ODBC Drivers, you see True and False (which I think
means that the result set is treeated differently by Word, or perhaps some
automatic formatting is done in the Provider). When you use a DATABASE
field, the values always appear to be True and False, never 1 and 0.

If you're trying to maintain maximum compatibility between versions of Word,
then using the ODBC approach is probably going to work as well as anything
(bearing in mind that the results may be formatted differently in different
versions of Word). Otherwise, you might be better off leaving out the
connection string altogether (unless you have to specify something in it,
such as a system/workgroup database) and just specifying the .mdb. Word
should then use OLE DB to open it.

Peter Jamieson

The syntax rules for Jet queries (whether you're connecting via DATABASE
or as a merge data source) are not at all clear - i.e. I have no idea why
the \" \" quoting syntax works when you use * but not otherwise.

However, my general experience so far is that
a. syntax using double-quotes seems to be a relatively recent innovation,
and it does not always seem to work as you might hope. The fact that the
double-quotes have to be quoted using \ in fields may also be a factor.
b. in this case any of the following would probably work:
no quotes (but you would need /some/ quoting if the table name contained
spaces:
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select Priority from
case_summary_table }
square brackets (Jet syntax AFAIK):
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select [Priority] from
[case_summary_table] }
back quotes (older? Jet syntax)
{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select `Priority` from
`case_summary_table` }

Since the square brackets approach seems to work in most cases, I'd stick
with that. However, if you were connecting via OLE DB rather than ODBC you
might find that you also had to create and use a table alias.

Peter Jamieson

This is not a mailmerge question, but I thought that someone might be
able to help since I have seen similar questions here. I am trying to
pull information out of an Access database into my word doc.

This won't work

{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select Priority from
\"case_summary_table\" }

But this will

{ DATABASE \d "C:\\mckdata\\June2006.mdb" \c "DSN=MS Access Database;
DBQ=C:\\mckdata\\June2006.mdb" \s "select * from \"case_summary_table
\" }

The difference in the second one is that I am querying all of the
fields in the table.

Thanks in advance for your help.