Database Field SQL Statement

[mico]

OS: XP Pro SP1, Word 2003 SP2

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT * FROM
'tblAirCard' "

I’ve been unable to change the select statement above to work properly.
I’ve tried to use tic, backslash and tic and backslash to denote the columns
names and I’ve tried using the tablename.fieldname, tablename!fieldname and
just the field name by itself and everything generates the generic Word error
unable to open data source. Any advice is appreciated.

 

[Peter Jamieson]

Do you really need to use ODBC? If OLE DB would be enough,

1. You should remove the \c parameter, i.e.

\c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

altogether - Word 2003 should use the OLE DB provider by default and it
doesn't look as if you need to specify a particular login name etc.

Then the following should work:

SELECT * FROM tblAirCard"

Then for column names that do not contain spaces you really should be able
to use

\s "SELECT columnname FROM tblAirCard"

Even the table name should not need to be quoted unless it has unusual
characters..I'm just doing this here, right now, so can check rather easily!

However, if you need ODBC, does the straightforward "SELECT * FROM" that you
already have work properly - i.e. is it only when you try to select
individual fields that you run into trouble?

Peter Jamieson

 

[mico]

Thanks for the advice however I'm still not able to execute beyond a simple
select all sql statement. I was able to determine the connect string or \c
parameters were not needed. The previous {Database} statemnet did work, I
just couldn't select column names and I still need to add a where clause to
the sql statement.

Thanks again for the advice.

 

[mico]

PS Even after eliminating the connect string, \c parameter, I still need the
tic marks around the table name for the Database statement to work, otherwise
I get the unable to open data source.

 

[Peter Jamieson]

OK, so let's be clear this works OK?

Does this work OK?

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \s "SELECT * FROM tblAirCard'"

NB, in most cases backquotes (which is what you have around your table name)
can be replaced by [], e.g.

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access
Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT * FROM
[tblAirCard]"

but not by straight quotes, e.g.

Is tblAirCard a table in the .mdb or a linked table?

Peter Jamieson

 

[mico]

Yes, this works:
DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT *
FROM [tblAirCard] "

Also, it appears you can use just about anything in place of the tic mark
but you must use something to enclose the tblAirCard.

FWIW, I seem to be able to use Excel as a data source correctly and was even
able to use a Mergefield in a where clause of the SQL statement. This brings
up another question. Word does not automatically update the table fields
based upon the next Mergefield record. When I move from one record to the
next I have to press F9 to see the update fields. Below is the work around
noted in April 2006:
==========================================
OK, I've just checked here. You'll need to add a DWORD value called
FieldCalcSecurityLevel and set the value to 0, under the following registry
key:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options
(Follow the instructions for the other versions of Word, but substitute 11.0
in the appropriate place. The instructions are in
http://support.microsoft.com/kb/330079/EN-US/
================================

Is this still the only way to work around this issue?
Thanks again.

 

[Peter Jamieson]

Is this still the only way to work around this issue?

As far as I know, yes. When you merge the results should be correct. I've
never understood the rationale for this behaviour (if it's OK to do the
query when you merge, why not when you preview?)

Also, it appears you can use just about anything in place of the tic mark
but you must use something to enclose the tblAirCard.

For one thing, I am forgetting myself: in Word 2003 you get an OLE DB
connection even where you specify an ODBC DSN in the \c parameter. If you
need to use ODBC, you have to specify a suitable file DSN in the \d
parameter (you can usually spot the difference in the results because ODBC
displays dates as date/times in YYYY-MM-DD hh:mm:ss format). I usually use a
FILEDSN in the \c parameter when I do that, e.g. \c
"FILEDSN=c:\\temp\\verizonaircard.dsn;", but I don't think it's actually
essential.

Other thn that, I have no problem with either [ ] or ` ` surrounding the
table name here (whether it's ODBC or OLE DB). I also don't have trouble
with a MERGEFIELD in the WHERE clause, as long as the clause is
syntactically correct for the type of data I'm comparing (e.g.

WHERE CustomerID = '{ MERGEFIELD CustomerID }'
for a text ID but
WHERE CustomerID = { MERGEFIELD CustomerID }
for a numeric ID.

At the moment I can't spot anything else that would make the thing /not/
work, sorry. The only suggestion I can make is to send me a Word doc. with
the DATBASE field and query you really want and I'll see if I can spot
anything (not easy without the database as well, but perhaps worth a try (or
create a test database and send a working and non-working examples.

Peter Jamieson

Yes, this works:
DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT *
FROM [tblAirCard] "

Also, it appears you can use just about anything in place of the tic mark
but you must use something to enclose the tblAirCard.

FWIW, I seem to be able to use Excel as a data source correctly and was
even
able to use a Mergefield in a where clause of the SQL statement. This
brings
up another question. Word does not automatically update the table fields
based upon the next Mergefield record. When I move from one record to the
next I have to press F9 to see the update fields. Below is the work
around
noted in April 2006:
==========================================
OK, I've just checked here. You'll need to add a DWORD value called
FieldCalcSecurityLevel and set the value to 0, under the following
registry
key:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options
(Follow the instructions for the other versions of Word, but substitute
11.0
in the appropriate place. The instructions are in
http://support.microsoft.com/kb/330079/EN-US/
================================

Is this still the only way to work around this issue?
Thanks again.

OS: XP Pro SP1, Word 2003 SP2

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access
Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT * FROM
'tblAirCard' "

I’ve been unable to change the select statement above to work properly.
I’ve tried to use tic, backslash and tic and backslash to denote the
columns
names and I’ve tried using the tablename.fieldname, tablename!fieldname
and
just the field name by itself and everything generates the generic Word
error
unable to open data source. Any advice is appreciated.

 

[mico]

This finally worked.

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp; DriverId=25; FIL=MS
Access; MaxBufferSize=2048; PageTimeout=5; UID=admin;" \s "SELECT [ID],
[ACCT_NUM] FROM [tblAirCard] "

I used the connect string generated by the udl.sql (MS data link properties
app) and copy & paste the string into the Database function. The string
generated has no spaces after the semicolons betweem the various parameters.
I added spaces after each semicolon and it started to work. I don't believe
I made any other changes. If this is truly what was wrong with my syntax
could you explaing what might have cause this IMO odd behavior? Just
curious, all that matter is it is finally working.

I also notice your last reply was around 9pm. If truly so, maybe you should
work a little less.

 

[mico]

As a final follow up this is what I used:

{ MERGEFIELD "UserID"}

{DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \s "SELECT [ACCT_NUM], [Phone
Number], [EMIN], [ESN_NUM], [User First Name], [User Last Name], [Location],
[Contact # 1], [Contact #2], [Supervisor Name], [Supervisor Number],
[Multiple Users] FROM [tblAirCard] WHERE [UserID] = '{ MERGEFIELD UserID}' "
\h}

This generates specific data in a table base on the UserID. In my case the
UserID is the person’s email address and the data generated in the table was
based on the email address.

As Peter said I didn’t need the connect string below:
\c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp; DriverId=25; FIL=MS
Access; MaxBufferSize=2048; PageTimeout=5; UID=admin;"

I believe the erratic result I was getting was from multiple merge documents
open with multiple data links plus I was not familiar with the F9(Update
Field ) and ALT F9(Toggle Field Codes) function. I think each time I hit F9
a new data link properties panel would appear and I would complete it but the
merge continued to fail. Also being new to the {Database field} function the
“unable to open data source†error made me think there was a problem with the
connect string but this errors appears anytime there is anything wrong with
any part of the Database field statement. IE If you misspell a column name
or if anything is wrong with the SQL statement the “unable to open data
source†error will appear when you hit F9.

I hope this helps and that Peter corrects any misleading information in my
note.

 

[Peter Jamieson]

Thanks for posting back - it'a always useful to have examples that worked
because as you say it's pretty hard to get this field to work.

Something else to bear in mind is that sometimes Word can get very confused
between the merge data source and te DATABASE data source when you have a
merge and a DATABASE field (as you obviously do when you're using a DATABASE
field in this way). When it happens, Word starts saying that such-and-such a
field doesn't existi n the data source. However, I don't know exactly what
the conditions for this to happen are: it certainly doesn't happen all the
time.

As for my working habits (see your other reply!), they vary all the time,
for one reason and another

Peter Jamieson

As a final follow up this is what I used:

{ MERGEFIELD "UserID"}

{DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \s "SELECT [ACCT_NUM], [Phone
Number], [EMIN], [ESN_NUM], [User First Name], [User Last Name],
[Location],
[Contact # 1], [Contact #2], [Supervisor Name], [Supervisor Number],
[Multiple Users] FROM [tblAirCard] WHERE [UserID] = '{ MERGEFIELD UserID}'
"
\h}

This generates specific data in a table base on the UserID. In my case
the
UserID is the person's email address and the data generated in the table
was
based on the email address.

As Peter said I didn't need the connect string below:
\c "DSN=MS Access Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp; DriverId=25; FIL=MS
Access; MaxBufferSize=2048; PageTimeout=5; UID=admin;"

I believe the erratic result I was getting was from multiple merge
documents
open with multiple data links plus I was not familiar with the F9(Update
Field ) and ALT F9(Toggle Field Codes) function. I think each time I hit
F9
a new data link properties panel would appear and I would complete it but
the
merge continued to fail. Also being new to the {Database field} function
the
"unable to open data source" error made me think there was a problem with
the
connect string but this errors appears anytime there is anything wrong
with
any part of the Database field statement. IE If you misspell a column
name
or if anything is wrong with the SQL statement the "unable to open data
source" error will appear when you hit F9.

I hope this helps and that Peter corrects any misleading information in my
note.

OS: XP Pro SP1, Word 2003 SP2

DATABASE \d "C:\\Temp\\VerizonAirCard.mdb" \c "DSN=MS Access
Database;DBQ=
C:\\Temp\\VerizonAirCard.mdb;DefaultDir= C:\\Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" \s "SELECT * FROM
'tblAirCard' "

I've been unable to change the select statement above to work properly.
I've tried to use tic, backslash and tic and backslash to denote the
columns
names and I've tried using the tablename.fieldname, tablename!fieldname
and
just the field name by itself and everything generates the generic Word
error
unable to open data source. Any advice is appreciated.