Updatiung Secured Back-end in code from Front-end in Runtime environment (A97)

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks

I have been using a technique where the front-end checks an update table
that indicates the Fields, Relationships and defaults etc to be updated in
the back-end, and goes ahead and makes the relevant changes.

However, two things have changed:

1. The app is now in runtime and MSAccess is not available

2. The back-end is now user-secured - using RWOP queries to access data.

Questions:

a. Is it possible to update the back-end structure in code in runtime?

b. If so, what techniques do I need to apply to get access to the back-end
tables etc from the Front-end (how to pass a password if necessary or create
a connection)?

c. Is there a better way to organise back-end structural changes?

Appreciate any feedback.
 
Questions:
a. Is it possible to update the back-end structure in code in runtime?

b. If so, what techniques do I need to apply to get access to the back-end
tables etc from the Front-end (how to pass a password if necessary or
create a connection)?

c. Is there a better way to organise back-end structural changes?
Click to expand...

ANSWERS to (a) and (b):

If you're familiar with DAO, you could adopt either of the following two
approaches. These should allow you to open the backend database
programatically as any user with the rights necessary to make changes.

1. If the backend uses the same Workgroup (MDW) file as the frontend (as
would seem likely), then you could use the DAO CreateWorkspace method to
create a new workspace - logging into the new Workspace as, say, the Admin
user. You could then open the backend in the new Workspace and manipulate it
as needed.

Private mobjWS As DAO.Workspace
Private mobjDB As DAO.Database
Set mobjWS = DAO.DBEngine.CreateWorkspace("MyWS", "Admin", "AdminPassword",
dbUseJet)
Set mobjDB = mobjWS.OpenDatabase("MyBackendDatabase")

2. If (as would seem unlikely) you need to open the backend using a
different Workgroup file, then you could use a private DBEngine object as
follows:

Private mobjDBE As DAO.PrivDBEngine
Set mobjDBE = New PrivDBEngine
mobjDBE.SystemDB = "MyMDWFile"
mobjDBE.DefaultUser = "Admin"
mobjDBE.DefaultPassword = "AdminPassword"
Set mobjDB = mobjDBE.OpenDatabase(...)

The above assume you have set a reference to DAO in the VBA editor (using
the Tools, References menu).

ANSWER to (c).

Don' t know, sorry.

Geoff
 
However, two things have changed:
1. The app is now in runtime and MSAccess is not available
Click to expand...

The above should not change your current code that opens up the back end,
and
modifies the data...

You don't mention how now you been doing this. However, ONE major thing does
change when you use
the runtime. (you can't use automaton to launch a 2nd copy because a file
parameter is required for the runtime, and you don't have that ability).

So, if your
current code uses automaton to launch 2nd copy that opens the back end...you
can NOT use this approach. (ie: runtime ms-access CAN NOT automate
a another copy of ms-access).

However, the standard meat and potato approach is it NOT use automaton. We
simply open the back end mdb file..and modify it....

eg:

Dim nT As DAO.TableDef
Dim db As DAO.Database
Dim strToDb As string

strToDb = "path to back end"
Set db = OpenDatabase(strToDb)

Set nT = db.TableDefs("tblOptionTypes")
nT.Fields.Append nT.CreateField("PercentCalc", dbBoolean)
nT.Fields.Append nT.CreateField("AddOn", dbBoolean)
nT.Fields.Refresh
db.Close
Set db = Nothing

The above would add two Boolean fields to a tablOptionTypes.

You can also use sql ddl statements (you don't mention how you been doing
this
back modifying already - that detail would have helped). I have to say that
most
wind up writing some code to modify the back end if you been deploying
software
to people where you can't get to their machines.....

Now, since the back end is secured, but we are *already* joined to the same
workgroup, then
you simply open the backend via the current workgroup.
So, just use:

Set db = DBEngine(0).OpenDatabase("C:\Program
Files\RidesXP\BackUp\RidesXP.mdb")

The rest of that example (or what you been using should th8s work).
 
Excellent Folks - exactly the feedback I was looking for - opening from a
currently opened Db (the front-end) in the same workgroup as the backend -
was what I was missing.

Cheers
 
Back
Top