By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,448 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Tricky Scneario

P: n/a
Have a MS Access db with queries and tables. There are SQL Pass-thru
queries which is used to get data from the production SQL Server db.
The local access tables are populated using these pass-thru queries.
Local access tables are used for high level local reporting purpose.
Everything works fine when connected and within network.

Need a solution when not connected or on VPN. The SQL Server box is not
open to VPN, unable to connect and run the pass-thru queries. What will
be the best possible way to handle this.

Thanks in advance
su****@gmail.com or su****@yahoo.com

Jan 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Puthisu wrote:
Need a solution when not connected or on VPN. The SQL Server box is not
open to VPN, unable to connect and run the pass-thru queries. What will
be the best possible way to handle this.


The answer you don't want to here is:

It depends.

If, when you are not connected to your network, all that is being done
is reporting on existing data and *NOT* any back end data changes (ie,
entering new transactions, changing of other values, etc), then it's a
simple matter of creating a bunch of local tables that represent a
"snapshot" of the server data. I use the terms "static snapshot" in
this context when I'm talking to users who understand at a gut level
what this means, though of course, in Access, "snapshot" refers to a
report output format.

I've done many, many applications in the past which are just reporting
apps that ran a "data snapshot" routine to update data in local tables
from server tables (in my case, Oracle, not SQL Server). But I haven't
done something that runs alternately on local tables or on network data
from the client server database such as what you're attempting to do.

Perhaps, when a user knows s/he will be discnnected from the network,
it's a simple matter to run a "Obtain Recent Data Snapshot" procedure or
set of procedures to update the snapshot tables.

However, the above introduces a whole new level of consideration in your
application which largely consists of how to change recordsources,
rowsources and any VBA code referencing the tables. In other words, in
network connection mode, a transaction table name might be called
Tbl_Transactions, while the snapshot mode equivalent is
Tbl_Transactions_Snapshot.

A possible but untried by me approach to this would be determining at
start up if one is in snapshot or network mode. Iterating through the
tables collection and renaming the linked network tables to
Tbl_Transaction_Network, then renaming Tbl_Transaction_Snapshot to
Tbl_Transaction might work. I don't see why this could not be done, but
again, I've never done it, so it's just a first guess for me.

If you try this, EXPERIMENT ON A COPY OF YOUR MDB FIRST!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jan 11 '06 #2

P: n/a
Tim, appreciate your reponse. Guess I should have added this **key ***
issue is when there is a case of an emergency and the b'folks had to
work from home via VPN. Yes, they could keep refreshing the data
everyday and have it copied locally or have someone run this and have
the mdb mailed, but still the b'folks requires this additional option.

Thanks again.

Jan 11 '06 #3

P: n/a
In message <11**********************@g49g2000cwa.googlegroups .com>,
Puthisu <su****@gmail.com> writes
Need a solution when not connected or on VPN. The SQL Server box is not
open to VPN, unable to connect and run the pass-thru queries. What will
be the best possible way to handle this.


You appear to have two choices. One is to fix the connection problem so
that you always have a connection.

The second is to live without the connection, which involves using
whatever data you have already got, even if it is out of date. When you
connect, take a snapshot of the live data. Cache that locally. When your
system is disconnected then use the local cached version, but bear in
mind that it isn't fresh.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

Jan 11 '06 #4

P: n/a
Possible solutions IF some conditions are met.

1) Your users have XP Pro on their office desktop

2) You system admin will allow them to VPN into those computers

Setup remote desktop on the office computer and allow them access though the
VPN. The performance would should be acceptable even though a dial-up
internet connection at home.

"Puthisu" <su****@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Have a MS Access db with queries and tables. There are SQL Pass-thru
queries which is used to get data from the production SQL Server db.
The local access tables are populated using these pass-thru queries.
Local access tables are used for high level local reporting purpose.
Everything works fine when connected and within network.

Need a solution when not connected or on VPN. The SQL Server box is not
open to VPN, unable to connect and run the pass-thru queries. What will
be the best possible way to handle this.

Thanks in advance
su****@gmail.com or su****@yahoo.com

Jan 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.