[AccessD] How to test with SQL Server data

Running out the door, but here are two TSQL scripts. One to back up, one to
restore:

BACKUP DATABASE [MachinesNew]
    TO  DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesNew.Bak' --Path on Server
    WITH  INIT ,  NOUNLOAD ,  NAME = N'MachinesNew backup',  NOSKIP ,  STATS
= 10,  NOFORMAT


RESTORE DATABASE [MachinesNew]
   FROM DISK =  'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MachinesNew.Bak'




On Fri, Aug 12, 2011 at 12:52 PM, Jim Dettman <jimdettman at verizon.net>wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>