A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Front End/Back End DBs?



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2004, 08:56 PM
Iram Alvarez
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

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?

  #2  
Old May 14th, 2004, 09:44 PM
Kevin3NF
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

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.

"Iram Alvarez" wrote in message
...
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?



  #3  
Old May 14th, 2004, 11:23 PM
Cheryl Fischer
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

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



"Iram Alvarez" wrote in message
...
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?



  #4  
Old May 15th, 2004, 08:20 AM
TC
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

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

HTH,
TC


"Iram Alvarez" wrote in message
...
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?



  #5  
Old May 25th, 2004, 09:43 PM
onthuhlist
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?


TC wrote:
[b]Also the front-end can close, compact, then re-open, the back end
from code.

HTH,
TC




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.

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



--
onthuhlist
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message675816.html

  #6  
Old May 26th, 2004, 01:41 AM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

"onthuhlist" wrote in message
news

TC wrote:
[b]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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



  #7  
Old May 26th, 2004, 05:11 PM
onthuhlist
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?


Douglas J. Steele wrote:
[b]"onthuhlist" wrote in message
news
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.


Although the mere act of opening the front end should NOT cause the
locking file to be created, yesterday this was actually happening. I
wrote a module procedure to close all forms, then called this procedure
from a form button. Therefore, the code could close the calling form. I
put a breakpoint in the code after all the forms had been closed, and
the .ldb file was still there. Even after 5 minutes sitting there
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 up
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 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.



--
onthuhlist
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message675816.html

  #8  
Old May 26th, 2004, 07:26 PM
onthuhlist
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?


onthuhlist wrote:
*Although the mere act of opening the front end should NOT cause the
locking file to be created, yesterday this was actually happening. I
wrote a module procedure to close all forms, then called this
procedure from a form button. Therefore, the code could close the
calling form. I put a breakpoint in the code after all the forms had
been closed, and the .ldb file was still there. Even after 5 minutes
sitting there 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 up 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 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 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.



--
onthuhlist
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message675816.html

  #9  
Old May 27th, 2004, 01:37 AM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default Front End/Back End DBs?

"onthuhlist" wrote in message
news
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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.