topic Re: open access table in VBA Forum

open access table

Anonymous — Wed, 07 Sep 2005 17:01:22 GMT

I've had great luck with the info provided here, thanks to all.
now my question
How can I tell if a table in Access is already open using VBA?

Re: open access table

Anonymous — Wed, 07 Sep 2005 17:33:44 GMT

Scott,

Not sure if this points you in the right direction but all Access Objects have an IsLoaded property.

You might have to be a little more specific as to whether you are using DoCmd.OpenTable, TableDef, ADO, DAO, etc...

Good luck,

Bob Coward
CADS, Inc

Re: open access table

Anonymous — Wed, 07 Sep 2005 17:47:17 GMT

Uhmm, the ADO model has a connection object
and a recordset object, and each has properties and methods
There is no table object in that model and
I believe a table does not have an open property
You read and write to a any table once you open the database

Maybe you meant if it's already created ??
Or if the database is already openned by someone else ??

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

wrote in message news:4949286@discussion.autodesk.com...
I've had great luck with the info provided here, thanks to all.
now my question
How can I tell if a table in Access is already open using VBA?

Re: open access table

Anonymous — Wed, 07 Sep 2005 17:50:09 GMT

No..I meant outside of an Access Object and an IsLoaded property a more specirfic question might be required

Re: open access table

Anonymous — Wed, 07 Sep 2005 18:06:44 GMT

Sorry Bob, I did not see your reply until now.
I was answering to the OP not to your reply

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

wrote in message news:4949349@discussion.autodesk.com...
No..I meant outside of an Access Object and an IsLoaded property a more
specirfic question might be required

Re: open access table

Anonymous — Wed, 07 Sep 2005 19:11:48 GMT

Actually the table already exists.
I just want to know if I can write to it as is (if it is open) or if it's not open so I nedd to open it.
Btw, I'm using ADO

Re: open access table

Anonymous — Wed, 07 Sep 2005 19:54:05 GMT

You might want to take a look at www.aspfaq.com (there are a few other great
ones, but that's usually where I go first for such information).

I know the question you asked has nothing to do with ASP, but that site has
some excellent info on database calls via ADO. The syntax is 95% identical,
whether from ASP or VBA, etc, so it's mostly valid information. Just watch
out for data types, as ASP is all variants.

wrote in message news:4949479@discussion.autodesk.com...
Actually the table already exists.
I just want to know if I can write to it as is (if it is open) or if it's
not open so I nedd to open it.
Btw, I'm using ADO

Re: open access table

Anonymous — Wed, 07 Sep 2005 20:06:18 GMT

Most likely, as you said that you just want to read/write data to the
database, you would use ADO ( or DAO) to access the database from you r VBA
code. Oddly enough, it has nothing to do with MS Access (the application),
you do not even neet is being installed.

Assume you use ADO. You connect to the database with ADO.Connection object,
which has Connection.Open() and Connection.Close() method. You can also
examine Connection.State property to see if it is open or not.

The code to connect to a database (*.mdb) will look like:

Dim cn AS ADODB.Connection
Set cn=New ADODB.Connection

Dim strConnectionString As String
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\nyuan\Desktop\db1.mdb;Persist Security
Info=False"

cn.Open strConnectionString

Then you can run SELECT/UPDATE/INSERT sql statement with this open
connection.

If you use DAO, the code is not the same, but similar.

So, you usally do not have to worry if the "table is open or not". You
simply open connection to the database as you need it, and close it when you
done. A database is meant to be shared by many users. Ideally, your VBA code
should handle connection open/close on its own.

wrote in message news:4949479@discussion.autodesk.com...
Actually the table already exists.
I just want to know if I can write to it as is (if it is open) or if it's
not open so I nedd to open it.
Btw, I'm using ADO

Re: open access table

Anonymous — Wed, 07 Sep 2005 20:59:38 GMT

... also lookup the CursorType and LockType properties for
ADODB.Recordset

- Jose

Norman Yuan wrote in
news:4949551@discussion.autodesk.com:

> Most likely, as you said that you just want to read/write data to the
> database, you would use ADO ( or DAO) to access the database from you
> r VBA code. Oddly enough, it has nothing to do with MS Access (the
> application), you do not even neet is being installed.
>
> Assume you use ADO. You connect to the database with ADO.Connection
> object, which has Connection.Open() and Connection.Close() method. You
> can also examine Connection.State property to see if it is open or
> not.
>
> The code to connect to a database (*.mdb) will look like:
>
> Dim cn AS ADODB.Connection
> Set cn=New ADODB.Connection
>
> Dim strConnectionString As String
> strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Documents and Settings\nyuan\Desktop\db1.mdb;Persist
> Security Info=False"
>
> cn.Open strConnectionString
>
> Then you can run SELECT/UPDATE/INSERT sql statement with this open
> connection.
>
> If you use DAO, the code is not the same, but similar.
>
> So, you usally do not have to worry if the "table is open or not". You
> simply open connection to the database as you need it, and close it
> when you done. A database is meant to be shared by many users.
> Ideally, your VBA code should handle connection open/close on its own.
>
> wrote in message news:4949479@discussion.autodesk.com...
> Actually the table already exists.
> I just want to know if I can write to it as is (if it is open) or if
> it's not open so I nedd to open it.
> Btw, I'm using ADO

Re: open access table

Anonymous — Wed, 07 Sep 2005 23:05:00 GMT

Actually what you need to know is if the database is open
For this you'll need the connection object

if cn.State = adstateopen then
'database is open, you can read/write
else
'it's closed, you need to open it
cn.open "Your open string here"
end if

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

wrote in message news:4949479@discussion.autodesk.com...
Actually the table already exists.
I just want to know if I can write to it as is (if it is open) or if it's
not open so I nedd to open it.
Btw, I'm using ADO

Re: open access table

Anonymous — Fri, 09 Sep 2005 11:46:14 GMT

Also notice some other properties that are useful for checking ADO objects.
The recordset also has a State property. The connection has an errors
collection containing information specific to ADO errors. Some errors will
also show up in the VBA Err object. The recordset has a Supports property,
which can tell you if the current configuration supports updatesback to the
base table. The recordset CursorType, CursorLocation, and LockType
properties all influence update behavior. Last, the recordset should include
a primary key column to be updatable.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

wrote in message news:4949286@discussion.autodesk.com...
I've had great luck with the info provided here, thanks to all.
now my question
How can I tell if a table in Access is already open using VBA?

Re: open access table

Anonymous — Sat, 10 Sep 2005 02:37:02 GMT

Sorry to disagree, John, but you do not need a
primary key field in the table to make it upgradeable.

But you are correct, that sdanis just needs to
check recordset.State to see if the recordset is open and you can do
the same for the connection, if a separate connection has been
made.

Lennart

"John Goodfellow" <
href="mailto:bitbucket@verizon.net">
size=2>bitbucket@verizon.net> wrote in
message
size=2>news:4951479@discussion.autodesk.com
size=2>...

Also notice some other properties
that are useful for checking ADO objects.
The recordset also has a State
property. The connection has an errors
collection containing
information specific to ADO errors. Some errors will
also show up
in the VBA Err object. The recordset has a Supports property,
which can
tell you if the current configuration supports updatesback to the
base
table. The recordset CursorType, CursorLocation, and
LockType
properties all influence update behavior. Last, the recordset should
include
a primary key column to be updatable.
--
John
Goodfellow
irtfnm
use john at goodfellowassoc dot
com

<sdanis> wrote in message
href="news:4949286@discussion.autodesk.com">
size=2>news:4949286@discussion.autodesk.com
size=2>...
I've had great luck with the info provided here, thanks to
all.
now my question
How can I tell if a table in Access is already open
using VBA?

Re: open access table

Anonymous — Sat, 10 Sep 2005 13:49:15 GMT

Yeah, that was imprecisely stated. Single table updates don't require a
primary key. In joined table recordsets, I have had instances where I
needed to add a primary key to one table and include that column in the SQL
command text to get the recordset to write back to the table via the
UpdateBatch method. This is usually using the Jet OLEDB 4.0 with a static,
client-side, batch optimistic mode cursor. I still have to read the manual
frequently .
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

"Lennart Nielsen" wrote in message
news:4952541@discussion.autodesk.com...
Sorry to disagree, John, but you do not need a primary key field in the
table to make it upgradeable.

But you are correct, that sdanis just needs to check recordset.State to see
if the recordset is open and you can do the same for the connection, if a
separate connection has been made.

Lennart

"John Goodfellow" wrote in message
news:4951479@discussion.autodesk.com...
Also notice some other properties that are useful for checking ADO objects.
The recordset also has a State property. The connection has an errors
collection containing information specific to ADO errors. Some errors will
also show up in the VBA Err object. The recordset has a Supports property,
which can tell you if the current configuration supports updatesback to the
base table. The recordset CursorType, CursorLocation, and LockType
properties all influence update behavior. Last, the recordset should include
a primary key column to be updatable.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

wrote in message news:4949286@discussion.autodesk.com...
I've had great luck with the info provided here, thanks to all.
now my question
How can I tell if a table in Access is already open using VBA?

Re: open access table

Anonymous — Mon, 12 Sep 2005 11:59:24 GMT

Great stuff!!
Thanks all.

Re: open access table

Anonymous — Thu, 14 Dec 2006 08:51:49 GMT

DoCmd.OpenTable
TableDef,
how descirbe " docmd.opentable " after
docmd.opentable.->