Multiple users/4servers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have 4 sites using the same application. Each site has a single front end
and multiple back ends (tables). We would like to have one front end for all
4 sites. How could we program access to open a certain form, give a report a
certain heading, etc., based on the server the user logs into?

I would appreciate any help in the right direction.
 
Dionne,

I'm not at all sureI understand what you are trying to do... the right
scheme is a common back-end and a separate front end for each user;
also, back end <> table, a back end may hold many tables. I suspect you
are not very clear on thse terms. Perhaps if you care to explain better
what you are trying to achieve, you'll get some real help.

In the meantime, for what it's worth, the way to retrieve a user's logon
server is:

Environ("LogonServer")

HTH,
Nikos
 
Nikos,

I was trying to brief in my description, but will now explain alittle more
in detail. We have several mdb files on the various servers which hold the
tables. Each user has an mdb file on their hard drive (front end). The
master front end also resides on the server and is copied down to the users
hard drive when updates are needed.

We have been maintaining a separate front end and back ends for our four
offices in Md, Tx, PR, and Va. We woud like to produce a front end that all
4 sites could use. However, each form, report etc., titles will need to
change based on the server the user logs into.

For example, if a user logs into a server in TX the forms and the reports
would be titles TXNPSC, etc.

Do this make more sense? Thank you for trying to assist me.

Diionne
 
Dionne,

Yes, it makes more sense now. It's very easy to retrieve the user's
logon server at runtime, and retrieve whatever based on that through the
following (general) scheme:

Assume a table called, say, tblLocalObjects, with fields like:

ServerName FormNPSC ReportX etc

and records like:

MDServer MDNPSC MDReportX ...
TXServer TXNPSC TXReportX ...

etc. The form's name can be retrieved by using a DLookup on this table like:

DLookup("[FormNPSC]","tblLocalObjects","[ServerName]='" &
Environ("LogonServer") & "'")

(in one line!)

If the server names and object names are consisitent and the first to
letters in all of them are the state code, then it becomes much simpler,
no table required; the form's name becomes simply:
Left(Environ("LogonServer"), 2) & "NPSC"

etc.

HTH,
Nikos
 
Nilos' solution for getting the info you need is good. But, I think you want
to change info displayed on your forms and reports. Once you have the local
server name, you can add some code to the object you want to change. For
example in a Form's Open event:

Dim strDisplay as String
Select Case LocalServerName
Case is = "TXNPSC"
strDisplay = "Display For Texas"
Case is = "Another Location"
strDisplay = "Display For Another Location"
.....
End Select
Me.txtDisplay = strDisplay
 
Do you every merge data between the 4 sites? or are the backend *.mdbs
free-standing where the data in one is never shared with any or all of
the others? If so, you may want to reevaluate how things are setup and
move to a single *.mdb. Otherwise...

To display which database the data is extracted from, create a table
named tblDBSystemData with two fields, txtDBSystemValueDescriptionLong,
txtDBSystemValueDescriptionShort (key) & txtDBSystemValue. This table
will hold various data pertaining to the specific backend and might
include values such as..

txtDBSystemValueDescriptionLong txtDBSystemValue
MDB Location Long Texas
MDB Location Short TX
MDB Front End Version Date 4/1/2005

txtDBSystemValueDescriptionShort
MDBLocLong
MDBLocShort
MDBFEVersion

The create a function to lookup the various values that you need using
DLookup().

function getSystemValue(strValue)

getSystemValue = DLookup(txtDBSystemValue, "tblSystemData",
"txtDBSystemValueDescriptionShort = " & strValue)

end function

From there its just a matter of deciding where & how to use the value
returned by getSystemValue(). One posssibility would be a control in a
report page footer with the value ="Data retrieved from the " &
getSystemValue("MDBLocLong") & " database."

(The values supplied would obviously be for the TEXAS site.)

David H
 
Back
Top