By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,575 Members | 1,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,575 IT Pros & Developers. It's quick & easy.

Closing a database without leaving

P: n/a
Hi
Using access 2003

I have a front end and back end. I would like to close the connection to the
backend so that I can compact the back end without leaving the front end.
Currently I must exit the datbase and start the repair form from another mdb
that is not connected to the back end.

I have tried
Dim appAccess As Access.Application
appAccess.CloseCurrentDatabase

this gives me an error.

any suggestions
Thanks Alfred
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You must close any tables that are linked to the backend. That is
sufficient for *you* to disconnect from the backend. Other users of
the backend must be out of the database, too.

I use this function to compact the database (watch for word-wrap), and
watch for application-specific calls to local forms.
Public Sub RunCompact(sFilePathToCompact As String)
' to compact any MDB file

Dim WorkDir As String
Dim CurrentDatabaseName As String
Dim TargetDatabaseName As String
Dim i As Integer
Dim iCount As Integer

' define working directory
i = LastBackslash(sFilePathToCompact)

WorkDir = Left$(sFilePathToCompact, i)
CurrentDatabaseName = Mid$(sFilePathToCompact, i + 1)
TargetDatabaseName = CurrentDatabaseName & ".compact"

' Check if destination database already exists
If Dir(WorkDir & TargetDatabaseName) <> "" Then
If MsgBox("Destination database " & WorkDir &
TargetDatabaseName & " already exists, do you want to delete it?",
vbYesNo, "Confirm") = vbYes Then
Kill WorkDir & TargetDatabaseName
Else
MsgBox "Compact procedure exiting"
Exit Sub
End If
End If
' close the main form so that no tables are open
On Error Resume Next
DoCmd.Close acForm, "@frmMain"

' Compact database
DBEngine.CompactDatabase WorkDir & CurrentDatabaseName, WorkDir &
TargetDatabaseName
If Err = 0 Then
' remove old database
Kill WorkDir & CurrentDatabaseName
Name WorkDir & TargetDatabaseName As WorkDir &
CurrentDatabaseName
Else
MsgBox "Compacting operation failed, couldn't create new
database file " & WorkDir & TargetDatabaseName & vbCrLf & vbCrLf & _
"Nothing has been damaged. Suggest that you exit completely
from LODM then try again. Ensure that nobody else is using the
database." & vbCrLf & vbCrLf & _
"WARNING!! If you have just completed a Restore from
Archive, it is CRITICAL that the backend be " & _
"compacted before adding any new trips. Without compacting,
the TripID sequence will become corrupted.", vbCritical

End If

End Sub

On Fri, 3 Dec 2004 16:10:09 -0000, "ALFRED" <AD*@SPAM.CO.ZA> wrote:
Hi
Using access 2003

I have a front end and back end. I would like to close the connection to the
backend so that I can compact the back end without leaving the front end.
Currently I must exit the datbase and start the repair form from another mdb
that is not connected to the back end.

I have tried
Dim appAccess As Access.Application
appAccess.CloseCurrentDatabase

this gives me an error.

any suggestions
Thanks Alfred

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #2

P: n/a
Thanks
I will try it
Alfred
"Jack MacDonald" <ja**************@telus.net> wrote in message
news:uf********************************@4ax.com...
You must close any tables that are linked to the backend. That is
sufficient for *you* to disconnect from the backend. Other users of
the backend must be out of the database, too.

I use this function to compact the database (watch for word-wrap), and
watch for application-specific calls to local forms.
Public Sub RunCompact(sFilePathToCompact As String)
' to compact any MDB file

Dim WorkDir As String
Dim CurrentDatabaseName As String
Dim TargetDatabaseName As String
Dim i As Integer
Dim iCount As Integer

' define working directory
i = LastBackslash(sFilePathToCompact)

WorkDir = Left$(sFilePathToCompact, i)
CurrentDatabaseName = Mid$(sFilePathToCompact, i + 1)
TargetDatabaseName = CurrentDatabaseName & ".compact"

' Check if destination database already exists
If Dir(WorkDir & TargetDatabaseName) <> "" Then
If MsgBox("Destination database " & WorkDir &
TargetDatabaseName & " already exists, do you want to delete it?",
vbYesNo, "Confirm") = vbYes Then
Kill WorkDir & TargetDatabaseName
Else
MsgBox "Compact procedure exiting"
Exit Sub
End If
End If
' close the main form so that no tables are open
On Error Resume Next
DoCmd.Close acForm, "@frmMain"

' Compact database
DBEngine.CompactDatabase WorkDir & CurrentDatabaseName, WorkDir &
TargetDatabaseName
If Err = 0 Then
' remove old database
Kill WorkDir & CurrentDatabaseName
Name WorkDir & TargetDatabaseName As WorkDir &
CurrentDatabaseName
Else
MsgBox "Compacting operation failed, couldn't create new
database file " & WorkDir & TargetDatabaseName & vbCrLf & vbCrLf & _
"Nothing has been damaged. Suggest that you exit completely
from LODM then try again. Ensure that nobody else is using the
database." & vbCrLf & vbCrLf & _
"WARNING!! If you have just completed a Restore from
Archive, it is CRITICAL that the backend be " & _
"compacted before adding any new trips. Without compacting,
the TripID sequence will become corrupted.", vbCritical

End If

End Sub

On Fri, 3 Dec 2004 16:10:09 -0000, "ALFRED" <AD*@SPAM.CO.ZA> wrote:
Hi
Using access 2003

I have a front end and back end. I would like to close the connection to
the
backend so that I can compact the back end without leaving the front end.
Currently I must exit the datbase and start the repair form from another
mdb
that is not connected to the back end.

I have tried
Dim appAccess As Access.Application
appAccess.CloseCurrentDatabase

this gives me an error.

any suggestions
Thanks Alfred

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.