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?

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" <anonymous@discussions.microsoft.com> wrote in message

news:71120010-CDDE-4418-B7E3-3E686701ED72@microsoft.com...

> 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?

>

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" <anonymous@discussions.microsoft.com> wrote in message

news:71120010-CDDE-4418-B7E3-3E686701ED72@microsoft.com...

> 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?

>

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



HTH,

TC





"Iram Alvarez" <anonymous@discussions.microsoft.com> wrote in message

news:71120010-CDDE-4418-B7E3-3E686701ED72@microsoft.com...

> 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?

>

	
Quote:
	

	

		

			
				Originally posted by TC 

[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" <onthuhlist.16u6s1@mail.mcse.ms> wrote in message

news:onthuhlist.16u6s1@mail.mcse.ms...

>

> TC wrote: 

>

> 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!)

	
Quote:
	

	

		

			
				Originally posted by Douglas J. Steele 

[b]"onthuhlist" <onthuhlist.16u6s1@mail.mcse.ms> wrote in message

news:onthuhlist.16u6s1@mail.mcse.ms...[vbcol=seagreen]



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.

	
Quote:
	

	

		

			
				Originally posted by onthuhlist 

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" <onthuhlist.16w1g8@mail.mcse.ms> wrote in message

news:onthuhlist.16w1g8@mail.mcse.ms...

> 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!)