assigning variable to DB field value

M

muyBN

In the following code (brackets [] replace actual path name, etc.), what is
the syntax for assigning the value of the database field to a variable?

And a few more questions:

What would be the equivalent code for doing the same but using a recordset
just to get the data field value then assign it to a variable? In other
words, this would just retrieve the field's value and not make the document
into a merge document, correct?

If I didn't want to use the .odc shown below, what would be the correct
syntax to just reference an Access database named DB.mdb? (I'm asking because
I recorded this from a macro; and I'd like to know all the options so I can
better understand the syntax and principles behind the code.)

ActiveDocument.MailMerge.OpenDataSource Name:="[path]\[datasource].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]\[database].mdb;DefaultDir=[path];" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;FILEDSN=[path]\[DSN].dsn;MaxBufferSize=2048;MaxScanRows=8;PageTi" _
, SQLStatement:="SELECT [field] FROM `[query]`", SQLStatement1:="",
SubType:=wdMergeSubTypeOther
[variable]=???
 
M

muyBN

Don't need you! Figured it out after hours of trial and error:

Dim strSQL As String, [variable] As String
Dim objConn As New ADODB.Connection, rsRecSet As New ADODB.Recordset

strSQL = "SELECT [field] FROM [query];"
Set objConn = CreateObject("adodb.connection")
objConn.ConnectionString = "data source=[path]\[file].mdb;
Provider=Microsoft.Jet.OLEDB.4.0;"
objConn.Open
Set rsRecSet = CreateObject("adodb.recordset")
rsRecSet.Open strSQL, objConn
[variable] = rsRecSet("[field]")
objConn.Close
rsRecSet.Close

--
Bryan


muyBN said:
Hey, all the questions around mine have been answered! What's wrong with mine?
--
Bryan


muyBN said:
In the following code (brackets [] replace actual path name, etc.), what is
the syntax for assigning the value of the database field to a variable?

And a few more questions:

What would be the equivalent code for doing the same but using a recordset
just to get the data field value then assign it to a variable? In other
words, this would just retrieve the field's value and not make the document
into a merge document, correct?

If I didn't want to use the .odc shown below, what would be the correct
syntax to just reference an Access database named DB.mdb? (I'm asking because
I recorded this from a macro; and I'd like to know all the options so I can
better understand the syntax and principles behind the code.)

ActiveDocument.MailMerge.OpenDataSource Name:="[path]\[datasource].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]\[database].mdb;DefaultDir=[path];" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;FILEDSN=[path]\[DSN].dsn;MaxBufferSize=2048;MaxScanRows=8;PageTi" _
, SQLStatement:="SELECT [field] FROM `[query]`", SQLStatement1:="",
SubType:=wdMergeSubTypeOther
[variable]=???
Click to expand...
Click to expand...
 
M

muyBN

OK, I will admit, I did what I needed with the recordset, but I still need
help on how to set a variable equal to the value of a merge field; something
like:

variable = activedocument.mailmerge.fields[WhatNext?]"""[field]"""
 
P

Peter Jamieson

variable = Activedocument.MailMerge.DataSource.DataFields("the name of the
field in the data source")

Peter Jamieson
muyBN said:
OK, I will admit, I did what I needed with the recordset, but I still need
help on how to set a variable equal to the value of a merge field;
something
like:

variable = activedocument.mailmerge.fields[WhatNext?]"""[field]"""
--
Bryan


muyBN said:
In the following code (brackets [] replace actual path name, etc.), what
is
the syntax for assigning the value of the database field to a variable?

And a few more questions:

What would be the equivalent code for doing the same but using a
recordset
just to get the data field value then assign it to a variable? In other
words, this would just retrieve the field's value and not make the
document
into a merge document, correct?

If I didn't want to use the .odc shown below, what would be the correct
syntax to just reference an Access database named DB.mdb? (I'm asking
because
I recorded this from a macro; and I'd like to know all the options so I
can
better understand the syntax and principles behind the code.)

ActiveDocument.MailMerge.OpenDataSource
Name:="[path]\[datasource].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]\[database].mdb;DefaultDir=[path];" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;FILEDSN=[path]\[DSN].dsn;MaxBufferSize=2048;MaxScanRows=8;PageTi"
_
, SQLStatement:="SELECT [field] FROM `[query]`",
SQLStatement1:="",
SubType:=wdMergeSubTypeOther
[variable]=???
Click to expand...
Click to expand...
 
M

muyBN

Ooh, missed it by that much! Then would you believe, by that much?

Thanks, Peter! Your answer was exactly what I needed.
--
Bryan


Peter Jamieson said:
variable = Activedocument.MailMerge.DataSource.DataFields("the name of the
field in the data source")

Peter Jamieson
muyBN said:
OK, I will admit, I did what I needed with the recordset, but I still need
help on how to set a variable equal to the value of a merge field;
something
like:

variable = activedocument.mailmerge.fields[WhatNext?]"""[field]"""
--
Bryan


muyBN said:
In the following code (brackets [] replace actual path name, etc.), what
is
the syntax for assigning the value of the database field to a variable?

And a few more questions:

What would be the equivalent code for doing the same but using a
recordset
just to get the data field value then assign it to a variable? In other
words, this would just retrieve the field's value and not make the
document
into a merge document, correct?

If I didn't want to use the .odc shown below, what would be the correct
syntax to just reference an Access database named DB.mdb? (I'm asking
because
I recorded this from a macro; and I'd like to know all the options so I
can
better understand the syntax and principles behind the code.)

ActiveDocument.MailMerge.OpenDataSource
Name:="[path]\[datasource].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]\[database].mdb;DefaultDir=[path];" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;FILEDSN=[path]\[DSN].dsn;MaxBufferSize=2048;MaxScanRows=8;PageTi"
_
, SQLStatement:="SELECT [field] FROM `[query]`",
SQLStatement1:="",
SubType:=wdMergeSubTypeOther
[variable]=???
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro asks which Access database file to use 4
mailmerge recorded macro 0
4198 debug 0
Insert variables into mailmerge documents 0
Use Merge Field in Email "To" field 2
macro 2 issues 0
Mail Merge Functionality 0
Mail Merge Programming problem 1

Top