"Disconnect" back end from front end using VBA

[Kelii]

Currently I have a button that allows the user to "Close Company" - at
the moment it doesn't do anything :D I would like the button to
"disconnect" the back end then show my Open Company form.

My question is:
1. Short of closing the application or deleting the linked tables, is
there a way to disconnect the user's front end from the common back
end using VBA?
2. Does disconnecting the user serve any purpose? I've read a lot of
posts about back end corruption, and kicking the user out of the
application, but nothing about disconnecting the back end.

Given the lack of discussion on the topic in this newsgroup (I haven't
found a thing) my assumption is that this is not possible. However,
would be happy if someone could confirm my suspicion.

Thanks to everyone in advance,

Kelii

[ARC]

Hi Kelii,

I've never heard of code to disconnect from the back-end, however, there are
cases where you would want to disconnect, for example, if you had a menu
option to compact the back-end database. For compacting, you could not have
any tables linked to the back-end, so you would need to disconnect. The
only trick I know is to have a second database, which is blank, that
contains all the same tables, etc. Basically, you would refresh the links of
all tables to the blank database, then compact the true back-end db, then
refresh the links back to the real back-end.

As for serving a purpose, I don't think disconnecting (connecting to a blank
db) would serve any better purpose then simply closing the FE database
program with a macro, or vba, etc.

Hope that helps,

Andy

"Kelii" <ke****@yahoo.comwrote in message
news:32**********************************@x35g2000 hsb.googlegroups.com...

Currently I have a button that allows the user to "Close Company" - at
the moment it doesn't do anything :D I would like the button to
"disconnect" the back end then show my Open Company form.

My question is:
1. Short of closing the application or deleting the linked tables, is
there a way to disconnect the user's front end from the common back
end using VBA?
2. Does disconnecting the user serve any purpose? I've read a lot of
posts about back end corruption, and kicking the user out of the
application, but nothing about disconnecting the back end.

Given the lack of discussion on the topic in this newsgroup (I haven't
found a thing) my assumption is that this is not possible. However,
would be happy if someone could confirm my suspicion.

Thanks to everyone in advance,

Kelii

[Tony Toews [MVP]]

Kelii <ke****@yahoo.comwrote:

>Currently I have a button that allows the user to "Close Company" - at
the moment it doesn't do anything :D I would like the button to
"disconnect" the back end then show my Open Company form.

My question is:
1. Short of closing the application or deleting the linked tables, is
there a way to disconnect the user's front end from the common back
end using VBA?
2. Does disconnecting the user serve any purpose? I've read a lot of
posts about back end corruption, and kicking the user out of the
application, but nothing about disconnecting the back end.

Given the lack of discussion on the topic in this newsgroup (I haven't
found a thing) my assumption is that this is not possible. However,
would be happy if someone could confirm my suspicion.

I have a button on my Fleet Management app which allows the user to do a backup of
the backend data MDB. I also have two other buttons which zip and email the backend
data MDB to either themselves for backup purposes or to me for support purposes. So
the problem here is the same.

However any open forms or reports will lock the back end MDB file so a rename and
compact won't happen.

As I do not know which forms and reports the user might have left open when the click
on that button I run through some code that closes all open forms and reports, then
run the code doing the rename, compact back to the same file name and, if requested
do the zip and email.

' close all forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm
DoEvents
For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
Next rpt
DoEvents

DoEvents seems to be necessary to give things time to close. But maybe not
neccesary.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

Kelii <ke****@yahoo.comwrote:

>2. Does disconnecting the user serve any purpose? I've read a lot of
posts about back end corruption, and kicking the user out of the
application, but nothing about disconnecting the back end.

Corruption only happens in that brief moment of time when a record is updated on a
form. That is when the record selector changes from a triangle to a pencil or from a
pencil back to a triangle or when the form is close. If a user is editing a record
on the computer screen but isn't actually saving that record then corruption won't
happen.

So not a big deal.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Kelii]

Andy, Tony:

Thanks for the prompt response. Given your thoughts, I think I can
pass on the whole disconnect thing and simply go with my Open Company
form in dialog mode.

I did think ahead to closing the various forms (for backup purposes as
well). I was quite proud of myself for using an array. I tried for a
few hours to figure out a way to only stop closing forms if they were
"dirty", but this effort ended in complete failure and confusion.

The piece that follows allows the user to check if any forms are open,
except as specified in the fx call.

Thanks again,

Kelii

Public Function kleCheckForOpenForms(ParamArray varFormArray() As
Variant) As String
On Error GoTo Error_Handler
'Sample procedure call kleCheckForOpenForms("frmSwitchboard",
"frmOpenCompany")
'Function output:
'"ExtraFormsOpen"
'"NoExtraFormsOpen"

Dim intFormsCount As Integer
Dim intFormName As Integer
Dim intFormArrayValue As Integer
Dim intI As Integer

'Use UBound fx to determine upper limit of array
'Note array values start at 0 and count up
'Find number of forms open and compare to array size
intFormsCount = Forms.Count - 1
If intFormsCount UBound(varFormArray()) Then
'Too many forms open
kleCheckForOpenForms = "ExtraFormsOpen"
GoTo Exit_Procedure
End If

'Check open forms against name of forms that are ok
'to be open
'Iterates through every open form
For intFormName = intFormsCount To 0 Step -1
'Iterates through every value in the array
'and compare array value with form name
intI = intFormsCount
For intFormArrayValue = UBound(varFormArray()) To 0 Step -1
If Forms(intFormName).Name =
varFormArray(intFormArrayValue) Then
Exit For
Else
intI = intI - 1
End If
If intI = -1 Then
'All forms checked, none match
kleCheckForOpenForms = "ExtraFormsOpen"
GoTo Exit_Procedure
End If
Next
Next

'If here, then no extra forms are open
kleCheckForOpenForms = "NoExtraFormsOpen"

Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function

[Tony Toews [MVP]]

Kelii <ke****@yahoo.comwrote:

>The piece that follows allows the user to check if any forms are open,
except as specified in the fx call.

Sure but I just closed all forms. I have very, very few forms in my apps that aren't
bound to the backend in one form or another.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Kelii]

Tony,

I'm interested in the code you posted above. When you posted it, I
assumed that it was perhaps a reporting application with no data
entry, but based on your last post, perhaps not.

I implemented similar code in my application, but soon abandoned the
effort due to conflicts with my error handlers. For example, if I have
a data entry form open, with an incomplete record in progress, the
form closing code you suggest above would prompt my error handler and
cause an inelegant exit from the form.

Of course, the user has the option to select "Cancel" in the error
handler's message box, which stopped the form from closing. However,
as I tested the application, it seemed to me that the application
became "clunky," and I preferred to have the user close the forms
themselves.

Given the above (sorry if its a bit of a wandering thought pattern), I
wondered if you have any thoughts about how to handle the form closing
gracefully?

Kelii

P.S. I have chosen to setup my backend tables with required properties
set to No, then use VBA to check for completeness and accuracy, with
custom error handlers for most (I hope all) situations.

[lyle fairfield]

Tables are linked. Are back-ends linked? I think not but I'm prepared
to be educated.
To unlink tables we can just Drop them.

Sub temp()
On Error Resume Next
Do
Err = 0
CurrentProject.Connection.Execute "DROP Table " &
CurrentProject.Connection.Execute("SELECT TOP 1 '[' & [NAME] & ']'
From MSysObjects WHERE Type = 6")(0)
Loop Until Err <0
End Sub

I've never had back-end corruption, or front-end either that I recall.
Probably, I am doing something wrong.

On Aug 28, 11:55*pm, Kelii <[e-mail]:

Currently I have a button that allows the user to "Close Company" - at
the moment it doesn't do anything :D I would like the button to
"disconnect" the back end then show my Open Company form.

My question is:
1. Short of closing the application or deleting the linked tables, is
there a way to disconnect the user's front end from the common back
end using VBA?
2. Does disconnecting the user serve any purpose? I've read a lot of
posts about back end corruption, and kicking the user out of the
application, but nothing about disconnecting the back end.

Given the lack of discussion on the topic in this newsgroup (I haven't
found a thing) my assumption is that this is not possible. However,
would be happy if someone could confirm my suspicion.

Thanks to everyone in advance,

Kelii

[Tony Toews [MVP]]

Kelii <ke****@yahoo.comwrote:

>I'm interested in the code you posted above. When you posted it, I
assumed that it was perhaps a reporting application with no data
entry, but based on your last post, perhaps not.

No, not at all. That's a very full featured app with lots of data entry and inquiry
forms.

>Given the above (sorry if its a bit of a wandering thought pattern), I
wondered if you have any thoughts about how to handle the form closing
gracefully?

Yes, that can be a problem but generally the users should have all the other forms
closed when they return to the main menu.

>P.S. I have chosen to setup my backend tables with required properties
set to No, then use VBA to check for completeness and accuracy, with
custom error handlers for most (I hope all) situations.

I completely agree with that. You never know when you need to add some records in a
different fashion with incomplete data such as doing a data import or bulk record
add.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Kelii]

Tony,

Thanks for the responses. This is really my first complete application
and I have no formal training or related work experience; so your
thoughts are helpful and valuable to me.

Lyle,

Thanks for the suggestion. Based on my quick review of VBA help, the
Drop Table query would delete the table from the project, which is not
really the action I'm looking for. I seem to have accomplished the
same thing (at least from the user's standpoint) simply by closing all
the forms and basically opening a Windows Common File Open Save dialog
for the new company to be selected.

Thanks again to you both for you insight.

Kelii