Rename Linked Table (If Needed)

I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great
ideas and tips from these boards which have kept this DB extremely functional
and lightweight. Now I need another inspiration.

This DB will be used as a FE/BE app about 70% of the time. The remaining 30%
it will have to be used as a standalone due to the inevitability of no
network connectivity. No matter which way it's used, I'm looking at anywhere
from 2-10 concurrent users.

I have a single linked table which will be the key to this project (tblSRP).
I also have a local table (tblSRP_Temp). In the event of standalone mode, I
need a way to:
1 - test for network connectivity (recordset.count or Dir(Path)...?
2 - If no network, then rename these tables to each other's name (flip-flop)
so the user is using a local 'tblSRP'.

Along this thinking, a hidden form would reset their names on DB close. This
is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then
append it to the 'tblSRP' housed in the backend. I'm wanting to keep this
transparent to the user while keeping things easy and compact.

So, in a nutshell:
- on the network, do nothing
- not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to
'tblSRP'
- on hidden form close, ensure the linked table is named 'tblSRP' and the
other is name 'tblSRP_Temp'.

Thanks for the great tips and ideas...hope you can help with this one. If
there's another way I can tackle this, I'm open to any/all ideas.

On Wed, 29 Oct 2008 09:51:02 -0700, David Mulholland
wrote:

I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great
ideas and tips from these boards which have kept this DB extremely functional
and lightweight. Now I need another inspiration.

This DB will be used as a FE/BE app about 70% of the time. The remaining 30%
it will have to be used as a standalone due to the inevitability of no
network connectivity. No matter which way it's used, I'm looking at anywhere
from 2-10 concurrent users.

I'd use it as a FE/BE at ALL times. The backend can be on the same computer as
the frontend (in fact that's how I do almost all my development work).

There would then be no need to mess around with the structure - just relink to
the current location of the backend, whether that's on the network or just in
a folder on the local computer.
--

John W. Vinson [MVP]

I do use FE/BEs with all my other databases. It's just this one case i'm
trying to anticipate for. In the event of no network connectivity, my
multiple users won't be able to use a BE anyway. Even if we could get our IT
department to create an ad hoc network for these users, it's just begging for
trouble.

I'm just wanting to do this not oanly for ease of use, but also as a
knowledge exercise.

"John W. Vinson" wrote:

On Wed, 29 Oct 2008 09:51:02 -0700, David Mulholland
wrote:

I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great
ideas and tips from these boards which have kept this DB extremely functional
and lightweight. Now I need another inspiration.

This DB will be used as a FE/BE app about 70% of the time. The remaining 30%
it will have to be used as a standalone due to the inevitability of no
network connectivity. No matter which way it's used, I'm looking at anywhere
from 2-10 concurrent users.

I'd use it as a FE/BE at ALL times. The backend can be on the same computer as
the frontend (in fact that's how I do almost all my development work).

There would then be no need to mess around with the structure - just relink to
the current location of the backend, whether that's on the network or just in
a folder on the local computer.
--

John W. Vinson [MVP]

I'd suggest a different approach which avoids having to rename the tables at
all.

Wherever in the application you currently reference the table, instead
reference a query, qrySRP:

SELECT * FROM tblSRP;

Then at start-up amend the query's SQL property if a recordset object cannot
be established on the basis of the linked table:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySRP")
On Error Resume Next
Set rst = dbs.OpenRecordset("tblSRP")
If Err.Number = 0 Then
qdf.SQL = "SELECT * FROM tblSRP
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendToSRP"
DoCmd.SetWarnings True
Else
qdf.SQL = "SELECT * FROM tblSRP_Temp"
End If


The qryAppendToSRP query would append any outstanding rows from tblSRP_Temp
to tblSRP once its established that access to that table in the back end is
available via the linked tabledef object. By calling the OpenQuery method to
do this and setting warnings temporarily off only rows which do not violate
any key or index constraints will be appended. The SQL from this query can
therefore remain constant as:

INSERT INTO tblSRP (column list)
SELECT column list FROM tblSRP_Temp;

inserting the column list as appropriate.

On close-down there would be no need to amend the SQL property back to
selecting rows from tblSRP as the query's SQL property is updated at start-up
before any other action is taken.

The above should work whether the tblSRP_Temp is in the front end or a
linked table on the local machine as John suggests. I'd concur with his view
that the latter is preferable.

Ken Sheridan
Stafford, England

"David Mulholland" wrote:

I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great
ideas and tips from these boards which have kept this DB extremely functional
and lightweight. Now I need another inspiration.

This DB will be used as a FE/BE app about 70% of the time. The remaining 30%
it will have to be used as a standalone due to the inevitability of no
network connectivity. No matter which way it's used, I'm looking at anywhere
from 2-10 concurrent users.

I have a single linked table which will be the key to this project (tblSRP).
I also have a local table (tblSRP_Temp). In the event of standalone mode, I
need a way to:
1 - test for network connectivity (recordset.count or Dir(Path)...?
2 - If no network, then rename these tables to each other's name (flip-flop)
so the user is using a local 'tblSRP'.

Along this thinking, a hidden form would reset their names on DB close. This
is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then
append it to the 'tblSRP' housed in the backend. I'm wanting to keep this
transparent to the user while keeping things easy and compact.

So, in a nutshell:
- on the network, do nothing
- not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to
'tblSRP'
- on hidden form close, ensure the linked table is named 'tblSRP' and the
other is name 'tblSRP_Temp'.

Thanks for the great tips and ideas...hope you can help with this one. If
there's another way I can tackle this, I'm open to any/all ideas.

After testing this out, it looks like it's doing what I need it to do. Great
idea. Nice, clean and elegant. I doubt I would've thought about dynamically
changing the base SQL statement. I knew there was a reason I frequent these
boards. THANKS!!

"Ken Sheridan" wrote:

I'd suggest a different approach which avoids having to rename the tables at
all.

Wherever in the application you currently reference the table, instead
reference a query, qrySRP:

SELECT * FROM tblSRP;

Then at start-up amend the query's SQL property if a recordset object cannot
be established on the basis of the linked table:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySRP")
On Error Resume Next
Set rst = dbs.OpenRecordset("tblSRP")
If Err.Number = 0 Then
qdf.SQL = "SELECT * FROM tblSRP
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendToSRP"
DoCmd.SetWarnings True
Else
qdf.SQL = "SELECT * FROM tblSRP_Temp"
End If


The qryAppendToSRP query would append any outstanding rows from tblSRP_Temp
to tblSRP once its established that access to that table in the back end is
available via the linked tabledef object. By calling the OpenQuery method to
do this and setting warnings temporarily off only rows which do not violate
any key or index constraints will be appended. The SQL from this query can
therefore remain constant as:

INSERT INTO tblSRP (column list)
SELECT column list FROM tblSRP_Temp;

inserting the column list as appropriate.

On close-down there would be no need to amend the SQL property back to
selecting rows from tblSRP as the query's SQL property is updated at start-up
before any other action is taken.

The above should work whether the tblSRP_Temp is in the front end or a
linked table on the local machine as John suggests. I'd concur with his view
that the latter is preferable.

Ken Sheridan
Stafford, England

"David Mulholland" wrote:

I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great
ideas and tips from these boards which have kept this DB extremely functional
and lightweight. Now I need another inspiration.

This DB will be used as a FE/BE app about 70% of the time. The remaining 30%
it will have to be used as a standalone due to the inevitability of no
network connectivity. No matter which way it's used, I'm looking at anywhere
from 2-10 concurrent users.

I have a single linked table which will be the key to this project (tblSRP).
I also have a local table (tblSRP_Temp). In the event of standalone mode, I
need a way to:
1 - test for network connectivity (recordset.count or Dir(Path)...?
2 - If no network, then rename these tables to each other's name (flip-flop)
so the user is using a local 'tblSRP'.

Along this thinking, a hidden form would reset their names on DB close. This
is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then
append it to the 'tblSRP' housed in the backend. I'm wanting to keep this
transparent to the user while keeping things easy and compact.

So, in a nutshell:
- on the network, do nothing
- not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to
'tblSRP'
- on hidden form close, ensure the linked table is named 'tblSRP' and the
other is name 'tblSRP_Temp'.

Thanks for the great tips and ideas...hope you can help with this one. If
there's another way I can tackle this, I'm open to any/all ideas.

On Wed, 29 Oct 2008 11:07:02 -0700, David Mulholland
wrote:

I do use FE/BEs with all my other databases. It's just this one case i'm
trying to anticipate for. In the event of no network connectivity, my
multiple users won't be able to use a BE anyway.

You are mistaken.

You *can* use a FE/BE architecture without a network.
The user will *not* need network connectivity.
A single user, on a single laptop, with no ethernet cable and with wi-fi
turned off, CAN use a FE/BE database - just have the FE and the BE on the same
computer, even in the same folder.
--

John W. Vinson [MVP]

True...I wasn't quite clear in my last post. I should have added that since
I'm looking at having up to 10 disconnected users...if each had FE/BE on
their comps, the workload to ensure to sync their data to the 'master' would
be a pain. This due to users who would input their data, then for whatever
reason, be relased and fail to get that data synced. Been there, done that.
Juggling this seemed more trouble than it's worth.

I tried Ken Sheridan's approach and it's doing what I need it to do. I
appreciate your time and experience.

"John W. Vinson" wrote:

On Wed, 29 Oct 2008 11:07:02 -0700, David Mulholland
wrote:

I do use FE/BEs with all my other databases. It's just this one case i'm
trying to anticipate for. In the event of no network connectivity, my
multiple users won't be able to use a BE anyway.

You are mistaken.

You *can* use a FE/BE architecture without a network.
The user will *not* need network connectivity.
A single user, on a single laptop, with no ethernet cable and with wi-fi
turned off, CAN use a FE/BE database - just have the FE and the BE on the same
computer, even in the same folder.
--

John W. Vinson [MVP]