[AccessD] How to test with SQL Server data

John,

<<Am I going to have to run a program that edits the link data?>>

  Possibly.  If your not using a DSN, then yes you'll need to change the
server and/or database your pointing to by modifying the tabledef.connect
property.  If your using DSN's, you can either change the DSN your pointing
to or can change the DSN itself and where it's pointing to.

<<I know that the Tabledef has the server / username stuff it in at K can if
necessary edit that to point to the right server IP, or perhaps a different
database on that server.  OTOH that seems to be the easy part.>>

  It is.

<<How do I cause changes in the table to be reflected in the "local"
database?>>

  With in Access, Refreshing the table links will get you the current view
of a linked tables design.  To actually move changes you've made in one DB
and apply them in another, you can script a stored procedure.  Which version
of SQL are you dealing with?  Most of the scripting is done via a right
click on an object.

  What you'll quickly realize with SQL is just about anything and everything
is done with SP's.

<<How do I get the most recent data? >>

  Delete your test DB, restore the production data to test.

<<Do I backup / restore the database to a new (local) name?>>

  Yup or you can detach the production db (which takes it off line), do a
file copy, reattach it, then reattach the copy under another SQL instance.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, August 12, 2011 02:50 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] How to test with SQL Server data

When I use an Access BE I use batch files to perform drive mapping to cause
X: to map to a shared 
drive on my local system (MapLocal) and to map to the live data location
(MapLive).  Once I have the 
map correct I simply link the FE to the BE through drive X and I am in
business.

I use

NetUse /Delete X:
NetUse X: \\DiscoSvr\DiscoProd

to map to the live and I use

NetUse /Delete X:
NetUse X: \\DisabilityINS\Dev

to map local to my workstation.

I can then keep the live FE / BE and libraries and copy them to my
workstation, MapLocal and voila I 
am working on local data on my system.  Make a change to the live BE means I
have to copy that to my 
workstation.  Make a change to the FE, send it to test and then they
eventually push it to LIVE.

Works well.

I am about to migrate them to SQL Server and then I will (initially) be
linked to a specific SQL 
Server / database / set of tables.  I can no longer just change the mapping
of drive X to switch 
between live and local data.

So how do I achieve the same effect, have a live data store and a local data
store so that I can 
test on my "local" data without fear of destroying "live" data?

Am I going to have to run a program that edits the link data?  I know that
the Tabledef has the 
server / username stuff it in at K can if necessary edit that to point to
the right server IP, or 
perhaps a different database on that server.  OTOH that seems to be the easy
part.  How do I cause 
changes in the table to be reflected in the "local" database?  How do I get
the most recent data? 
Do I backup / restore the database to a new (local) name?

I am just beginning to address these issues in preparation for the
migration.  Any wisdom will be 
appreciated.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com