Front End/Back End DBs?

[Iram Alvarez]

Why do some programers use Front End/Back End (in other words two .mdb files linked) rather than using one .mdb
What is the whole point

 

[Kevin3NF]

In a multi-user environment, this helps performance, locking and keeps the
likelihood of corruption to a minimum.
--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

Why do some programers use Front End/Back End (in other words two .mdb

files linked) rather than using one .mdb?

 

[Cheryl Fischer]

In addition to the good points made by Kevin, the objects in the front-end
of a database (Queries, Reports, Forms, Modules) are more prone to need
design modification or having new objects added than is the back-end. It
is *much* easier (not to mention less costly) to deliver a modified
front-end to multiple users who use a front-end linked to a back-end than to
follow the usual steps required to make an update to these objects in a
single, monolithic database, which may include, but are not limited to <g>:

1. Send an email to all users asking them to get out of the database so you
can make modifications.
2. Track down the users who did not get or ignored your email message
3. Try to kick them out programmatically
4. Go directly to their pc's and log them out of the database

or

1. Send an email asking all users to close the database at the end of the
day.
2. Come in an hour or so before normal starting time
3. Track down the users who left work with the database open
4. Try to kick them out programmatically
5. Go directly to their pc's and log them out of the database

You get the picture!
--

Cheryl Fischer, MVP Microsoft Access

Why do some programers use Front End/Back End (in other words two .mdb

files linked) rather than using one .mdb?

 

[TC]

Also the front-end can close, compact, then re-open, the back end from code.

HTH,
TC

Why do some programers use Front End/Back End (in other words two .mdb

files linked) rather than using one .mdb?

 

[onthuhlist]

Also the front-end can close, compact, then re-open, the back en
from code.

HTH,
TC

I'd like to see said code. I've searched the internet and haven't foun
code to do this. How do you close a back end when the back en
automatically opens when the front end is opened simply by virtue o
its tables being linked into the front end. I'm talking with all th
front end's forms, queries, everything closed. Just with the Acces
2002 database window open for the front end. You go to the folder fo
the back end, and you see that it has a .ldb file open, signifying tha
the back end is in use, and therefore you will be unable to compact it
And an Access 97 solution doesn't count here. I'm talking about usin
Access' built-in compact and repair method.

Grrrr. Microsoft really gets my goat. If you drove a car manufacture
by Microsoft, the engine would run for 50 seconds after you remove th
key, all the while displaying a message, "car is shutting down..." Wha
lunacy


-
onthuhlis

 

[Douglas J. Steele]

Also the front-end can close, compact, then re-open, the back end
from code.

I'd like to see said code. I've searched the internet and haven't found
code to do this. How do you close a back end when the back end
automatically opens when the front end is opened simply by virtue of
its tables being linked into the front end. I'm talking with all the
front end's forms, queries, everything closed. Just with the Access
2002 database window open for the front end. You go to the folder for
the back end, and you see that it has a .ldb file open, signifying that
the back end is in use, and therefore you will be unable to compact it.
And an Access 97 solution doesn't count here. I'm talking about using
Access' built-in compact and repair method.

You should only see the locking file (LDB) if you're actually working with
the data: running a query, opening a form that's looking up data in a table
(either as a bound form, or say combo boxes bound to a query) or the like.
The mere act of opening the front end should NOT cause the locking file to
be created.

That having been said, the code to have the front-end compact the back-end
is fairly straight-forward.

Something like the following untested air-code

Dim strBackup As String
Dim strDatabase As String
Dim strLockingFile As String

' FindBackendName is a function to return the name of the
' back-end database.
' This can be as simple as looking at the Connect property
' of one of your tables.
strDatabase = FindBackendName()
strLockingFile = Left$(strDatabase, Len(strDatabase) - 4) & ".ldb"
strBackup = Left$(strDatabase, Len(strDatabase) - 4) & Format$(Date(),
"yyyymmdd") & ".bak"

' Does the locking file exist?
' Only continue if it doesn't.
If Len(Dir$(strLockingFile)) = 0 Then

' Does the backup file name already exist?
' If so, delete it.
If Len(Dir$(strBackup)) > 0 Then
Kill strBackup
End If

' Rename the current backend
Name strDatabase As strBackup

' Compact the backend
DBEngine.Compact strBackup, strDatabase
End If

 

[onthuhlist]

news[email protected]...[vbcol=seagreen]

You should only see the locking file (LDB) if you're actually workin
with
the data: running a query, opening a form that's looking up data in
table
(either as a bound form, or say combo boxes bound to a query) or th
like.
The mere act of opening the front end should NOT cause the lockin
file to
be created.

Although the mere act of opening the front end should NOT cause th
locking file to be created, yesterday this was actually happening.
wrote a module procedure to close all forms, then called this procedur
from a form button. Therefore, the code could close the calling form.
put a breakpoint in the code after all the forms had been closed, an
the .ldb file was still there. Even after 5 minutes sitting ther
scratching my head (and cleaning up the dandruff afterward). Well
today I'm trying the same thing, and the .ldb IS NOW CLOSING! What's u
with this? Microsoft Access isn't even behaving repeatably... Sigh
This is so frustrating! Again, the back end is on my PC, so it's not
multi-user issue.

Things you can't do with Access:
1. Programmatically resize an embedded webbrowser control.
2. Put a rich text control on a page of a tab control


-
onthuhlis

 

[onthuhlist]

*Although the mere act of opening the front end should NOT cause th
locking file to be created, yesterday this was actually happening.
wrote a module procedure to close all forms, then called thi
procedure from a form button. Therefore, the code could close th
calling form. I put a breakpoint in the code after all the forms ha
been closed, and the .ldb file was still there. Even after 5 minute
sitting there scratching my head (and cleaning up the dandruf
afterward). Well, today I'm trying the same thing, and the .ldb I
NOW CLOSING! What's up with this? Microsoft Access isn't eve
behaving repeatably... Sigh. This is so frustrating! Again, the bac
end is on my PC, so it's not a multi-user issue.

Things you can't do with Access:
1. Programmatically resize an embedded webbrowser control.
2. Put a rich text control on a page of a tab control. *

Here's what I'm finding. I run my code to close all forms. Then I put
breakpoint in the code which is about to copy and compact the backed
so I can manually watch the .ldb file for the backend database close
But the .ldb file for the backend database remains open indefinitely
even though I run the DoEvents method in the code before my breakpoint
Then, still in debug mode, I click "Reset" to stop code execution, an
the .ldb file finally goes away. Well, I need the .ldb file to go awa
WHILE the code is running, so that I can programmatically copy an
compact the backend.

Any thoughts of how to resolve the issue? Again, I've used the DoEvent
method unsuccessfully


-
onthuhlis

 

[Douglas J. Steele]

Here's what I'm finding. I run my code to close all forms. Then I put a
breakpoint in the code which is about to copy and compact the backed,
so I can manually watch the .ldb file for the backend database close.
But the .ldb file for the backend database remains open indefinitely,
even though I run the DoEvents method in the code before my breakpoint.
Then, still in debug mode, I click "Reset" to stop code execution, and
the .ldb file finally goes away. Well, I need the .ldb file to go away
WHILE the code is running, so that I can programmatically copy and
compact the backend.

Any thoughts of how to resolve the issue? Again, I've used the DoEvents
method unsuccessfully.

Sorry. I've never had a problem, so I'm not aware of any workarounds.