[Ian McKeag[_2_]]

I am using the DATABASE field code to insert data from an Access table which
is fine.. However, I am inerting two of these, which opens the DB for both,
leaving two instances of the same DB open.

Is there anyway to only open the database once for both queries or even not
at all?

Many thanks,
Ian McKeag

[Peter Jamieson]

Assuming you are trying to stop the Access program from opening (in
effect, the Jet/ACE database always opens) you need a DATABASE field
that opens the database using either ODBC or OLE DB (neither of which
will start Access). You may also have to work a bit to deal with locking
issues if you need two separate connections.

How to do that depends on precisely which versions of Word and Access
you are using, whether or not you are using a database password and/or
workgroup security, and so on, but for example if it's Word/Access 2007
Word might generate an DATABASE field such as

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \c
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\\mydbs\\mydb.mdb;Mode=Read;Extended Properties=\"\";Jet
OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine
Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet
OLEDB:Support Complex Data=False" \s "SELECT * FROM `mytable`" \h }

which (in that case) you could probably shorten by eliminating the
connection string, i.e. by cutting it to

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \s "SELECT * FROM `mytable`" \h }


If you need to select certain fields, make sure you alias your table
name - it shouldn't be necessary syntactically but Word seems to insist:

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \s "SELECT m.a, m.b FROM `mytable`
m" \h }

If that doesn't help, post your Word/Access version and other info.
about exactly what you are trying to connect to, whether you have a
workgroup security file etc., and I'll see what I can do.

You may also find the examples at

http://tips.pjmsn.me.uk/t0004.htm

useful, although that page discusses a specialised use of DATABASE fields.


Peter Jamieson

http://tips.pjmsn.me.uk

Ian McKeag wrote:
I am using the DATABASE field code to insert data from an Access table which
is fine.. However, I am inerting two of these, which opens the DB for both,
leaving two instances of the same DB open.

Is there anyway to only open the database once for both queries or even not
at all?

Many thanks,
Ian McKeag

[Ian McKeag[_2_]]

Peter, that is fantastic!!!! Thanks very much, that has helped me out a
lot!!!!

"Peter Jamieson" wrote:

Assuming you are trying to stop the Access program from opening (in
effect, the Jet/ACE database always opens) you need a DATABASE field
that opens the database using either ODBC or OLE DB (neither of which
will start Access). You may also have to work a bit to deal with locking
issues if you need two separate connections.

How to do that depends on precisely which versions of Word and Access
you are using, whether or not you are using a database password and/or
workgroup security, and so on, but for example if it's Word/Access 2007
Word might generate an DATABASE field such as

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \c
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\\mydbs\\mydb.mdb;Mode=Read;Extended Properties=\"\";Jet
OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine
Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet
OLEDB:Support Complex Data=False" \s "SELECT * FROM `mytable`" \h }

which (in that case) you could probably shorten by eliminating the
connection string, i.e. by cutting it to

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \s "SELECT * FROM `mytable`" \h }


If you need to select certain fields, make sure you alias your table
name - it shouldn't be necessary syntactically but Word seems to insist:

{ DATABASE \d "C:\\mydbs\\mydb.mdb" \s "SELECT m.a, m.b FROM `mytable`
m" \h }

If that doesn't help, post your Word/Access version and other info.
about exactly what you are trying to connect to, whether you have a
workgroup security file etc., and I'll see what I can do.

You may also find the examples at

http://tips.pjmsn.me.uk/t0004.htm

useful, although that page discusses a specialised use of DATABASE fields.


Peter Jamieson

http://tips.pjmsn.me.uk

Ian McKeag wrote:
I am using the DATABASE field code to insert data from an Access table which
is fine.. However, I am inerting two of these, which opens the DB for both,
leaving two instances of the same DB open.

Is there anyway to only open the database once for both queries or even not
at all?

Many thanks,
Ian McKeag