Strategies for "play" databases?

[Laurel]

Is there an article somewhere that describes different approaches to
creating 'play' environments for clients? The way linked files are set up
you can't just let them pick which database they want to run against. I'm
using Jet, not MSS. The client can't be trusted to copy .mdb files back and
forth from folder to folder.

Along these lines, is it possible to execute a *.bat file from inside VB?

 

[Nikos Yannacopoulos]

Laurel,

If I understand correctly, what you want is to provide users with a test
environment besides the live production environment, and some means of
selecting which one to use?

Well, I'm not aware of an article that deals with your question, but I can
toss in an idea on how to do it... I would use a split front end / back end
database, and make a copy of the back end to use as a test BE. I would then
use some VB code to change the table links, depending on the user's choice
of environment. For the selection, I would use a small form with an option
group that pops up at FE startup and fires the code through a button, or
include it in a logon form if one exists (it seems some developers take this
approach), or link to production by default and give the user the ability to
change it by means of a button on the switchboard or main form (if one
exists) once the FE is loaded.

HTH,
Nikos

 

[Douglas J. Steele]

Nikos is right, and you can find sample code to do what he's describing at
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web"

 

[Laurel]

How do you change the table links? I thought they were definied in the sys
tables and were not writeable?

 

[Laurel]

This code looks perfect, but it requires that the FileOpenDialogue be
available. The code fails for me at that reason at this line - function not
defined.

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")

How do get access to FileOpenDialogue? I did drill down to the link
called GetOpenFileName, but I'm not sure how to use the information. For
instance, the first sentence says I can use the "common dialog control in
Access 97", or "the APIsdefined for this purpose." And then there's lots of
code below. It invites me to use it to understand what to do.... but I'm
afraid I need more hand-holding here.



Call the standard Windows File Open/Save dialog boxAuthor(s)

This can be done by either using the Common Dialog Control in Access 97 or
by using the APIs defined for this purpose.

 

[Cheryl Fischer]

Laurel,

PMFJI ...

The code found at:

Call the standard Windows File Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm supports the code used to
re-link tables found at http://www.mvps.org/access/tables/tbl0009.htm, so
there really isn't anything that you must *do* with it except to have it
available in a module.

Easiest way to get this working is to create a Module and paste the code
from "api0001.htm" into it; then, immediately following the code at
"api0001.htm" , paste in the code from "tbl0009.htm". Save the module.

Then, behind the command button you want to use, just insert the following
in the Click event of that button:

fRefreshLinks


hth,

 

[Nikos Yannacopoulos]

Laurel,

I have a FE/BE application that used to move a lot between office and home
while I was developing it, so I put together a small piece of code to change
the links depending on where I was. In that case the cirterion was the
Windows logon name, which was different ("G3488NY" in the code below is the
office logon name).
I believe you will find it straightforward to make the required adaptations.
Here it goes:

Function change_links()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()
usr = Environ("UserName") 'use your criterion here
If usr = "G3488NY" Then 'use your criterion here
cp = "C:\Documents\Sales Statistics\"
np = "K:\Common\Sales Statistics\"
Else
cp = "K:\Common\Sales Statistics\"
np = "C:\Documents\Sales Statistics\"
End If

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, cp, np)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next
End Function
Note: you will need to add DAO 3.X in Tools > References (DAO 3.51 for
Access97, DAO 3.6 for Access2K or later).

HTH,
Nikos

 

[Laurel]

THANKS TO ALL!