macro asks which Access database file to use

[muyBN]

As a side issue, when I sign in here to post a question, I designate the
option of the system remembering my user name and password, but I always have
to put in my password. Does anyone know what's up with that?

I have a macro that connects a Word document to an Access query then shows
the last fields in the query as merge fields in the document. However, when
the macro is running, it prompts me for the database name, even though it's
already written in the code (see code sample below). If someone can suggest
why this happens and how to correct the code (or the .odc or .dsn settings as
applicable) so that it connects to the database without a prompt, I'd
appreciate it.


With ActiveDocument
.MailMerge.MainDocumentType = wdFormLetters
.MailMerge.OpenDataSource Name:="path\ODCfile.odc" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="Provider=MSDASQL.1;Persist Security
Info=False;User ID=b;Extended Properties=""DBQ=path\DB.mdb;" & _
"DefaultDir=path;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS Access;" & _
"FILEDSN=C:\Program Files\Common Files\ODBC\Data
Sources\query." _
, SQLStatement:="SELECT * FROM `query`",
SQLStatement1:="", SubType:=wdMergeSubTypeOther
.MailMerge.EditMainDocument
.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField,
Text:="""dbField"""
.MailMerge.DataSource.ActiveRecord = wdLastRecord
.MailMerge.ViewMailMergeFieldCodes = wdToggle
End With

 

[Peter Jamieson]

I can't answer the "side issue".

You are certainly choosing a complicated route to connect to an Access .mdb,
i.e. going via a SQL Server database. Unless there is a good reason to
retain the approach you are using, simplify the connection first and see
what happens.

For an OLEDB connection try:

..MailMerge.OpenDataSource _
Name:="path\DB.mdb", _
SQLStatement:="SELECT * FROM `query`"

(As long as Word has enough info. to connect to the query/table and can do
so, this should be enough. Otherwise, you may need further info. in a
Connection string or in a .odc, but that .odc should specify the Jet
provider, not the SQL Server provider).

For an ODBC connection directly to the .mdb using your existing file dsn,
a. is you DSN file really called "query." ?
b. try

..MailMerge.OpenDataSource _
Name:="C:\Program Files\Common Files\ODBC\Data Sources\query.", _
Connection:="FILEDSN=C:\Program Files\Common Files\ODBC\Data
Sources\query.;", _
SQLStatement:="SELECT * FROM `query`", _
Subtype:=wdMergeSubtypeWord2000

You may need more info. in the Connection string (or the DSN).

Generally speaking, you will see a prompt if Word either cannot connect to
the query using the connection method you have chosen, or if you have not
provided enough of the security info. required by the data source. When you
try to connect directly to a .mdb without specifying exactly how to connect,
Word tries OLEDB first, then ODBC, then DDE. OLEDB and ODBC do not "see"
some types of query, (e.g. parameter queries) or will not return any records
(e.g. if the query contains old Jet SQL wildcards "*" or "?") . however,
that doesn't quite explain this situation assuming you are eventually able
to connect.

If you need to go via SQL Server the first thing I'd look at is the security
stuff. Are you using Integrated Security?

Peter Jamieson

 

[muyBN]

Thanks, Peter; your suggestion worked. I got my code from "recording" the
connection. After it was all recorded, the resulting code looked too
complicated--I guess I clicked SQL Server instead of Access while setting it
up.

 

[muyBN]

Another side issue that you will probably know how to answer:

MS help states that the following code will display a mergefield value but
it doesn't:
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = True

The only other choice I've been able to find is:

ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle,

but I would rather have something more "permanent" than toggle so it doesn't
alternate each time the macro comes through. Any suggestions?

 

[Peter Jamieson]

Actually, you probably need more VBA expertise than I have to unravel this
one.

The member is actually defined as a Long, and as far as I know if you set it
to -1
a. it is set to 65535 (which suggests it is actually an unsigned int)
b. you get to see the mergefield names
if you set it to 0
c. it is set to 0
d. you get to see the preview values
e. if you set it to wdToggle, it's set to 0 or 65535 with the corresponding
effect

Peter Jamieson