Access 2010 - VBA Command to Close Database if Opened in a Specific Location

ms access vba close current database and open another
access vba close another database
access vba close access
access vba close table
access vba close file
access vba close form docmd
access vba close query
close database access

I am trying to create an event procedure when the form loads that will close the database if the file is accessed from a specific location on a shared drive.

My first attempt looked something like this:

On Error Resume Next
Dim GetPath As String
GetPath = CurrentProject.Path
If GetPath = "C:\Folder1\Folder2" Then
    DoCmd.Quit
End If

However, since this file is located on a shared drive, users may be accessing the file from different drives (but the same server/pathway). So, for example, in my code the C drive wouldn't work for everyone. Some users may be accessing the server from J drive or L drive on their computer.

Is there a way I can get around this, or is there a better method?

Note: One way I could get around this problem would be to, instead, say

If GetPath <> "desired pathway" Then 
DoCmd.Quit

But I want to avoid this if possible.

The below should return you the mapped drive's UNC path (if it is a mapped drive):

Function GetActualPath(sPath) As String
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim drive As Object
    Set drive = fso.GetDrive(fso.GetDriveName(sPath))
    If Len(drive.ShareName) > 0 Then
        'swap out the mapped letter for the share path
        GetActualPath = Replace(sPath, drive.Path, drive.ShareName)
    Else
        'use the path provided
        GetActualPath = sPath
    End If
End Function

You can then test this against your network path.

Application.CloseCurrentDatabase method (Access), If you use the CloseCurrentDatabase method to close the database that is open in the current instance of Microsoft Access, you can then open a different database You can enter this code in a Visual Basic module in any application Dim frm As Form, strDB As String ' Initialize string to database path. DoCmd.CloseDatabase method (Access) 03/06/2019; 2 minutes to read; In this article. Closes the current database. Syntax. expression.CloseDatabase. expression A variable that represents a DoCmd object. Remarks. This method has the same effect as choosing the Microsoft Office button, and then choosing Close Database.

This is almost exactly what you are using. I haven't tried your version, but this has worked for me.

Function CheckIfMasterFile()

    On Error GoTo ErrorHandler

    Dim MasterFileLocation As String
    MasterFileLocation = "C:\Some_Folder\Some_Other_Folder\Folder_File_Resides_In"

    If Application.CurrentProject.Path = MasterFileLocation Then
        'MsgBox ("You have opened the Master file.")
        Application.Quit acQuitPrompt
    End If

ExitFunction:
  Exit Function

ErrorHandler:
  MsgBox (Err.Description)
  Resume ExitFunction:


End Function

DoCmd.Close method (Access), You can use the Close method to close either a specified Microsoft Access window or the active window if none is specified. the RunCommand method to invoke the acCmdSaveRecord command before calling the Close method. Have questions or feedback about Office VBA or this documentation? How to Call One Form From Another Form in Access using the Command Button Wizard 2007 2010 2013 2016 - Duration: 4:45. Tom Fragale - PCWebinars Com 29,993 views 4:45

first of all, I would never hardcode a path or UNC in VBA. If your users have write access for this folder (shared or not), they will be able to create a sub-folder, place a copy of your access file inside and then open the database...

It seems that you want that users can open your database only locally on their workstation, and in this case I would check that differently.

Please let me know...

Automatically close all the open forms and reports in an Access , Before closing a database, it's a good idea to close all the open forms and reports - but you At first, you might think that VBA's For. During the first loop, the code deletes the form at the 0 index position, Employees. Clicking it will close the current database if users have access to the Database window. I'm currently using Application.Quit which leaves a shell of the MS Access Application open.. After processing the calling function against the target application, I am left with every instance of the application remaining open still; even though the specific database instance is closed.

you can check if your file is on a network or local drive.

Dim fsoObj As Object ' File System Object
Dim drvObj As Object ' Drive

Set fsoObj = CreateObject("scripting.filesystemobject")
Set drvObj = fsoObj.GetDrive(fsoObj.GetDriveName(Application.CodeDB.Name))

' DriveType
' 1   Removable (for example a USB Stick)
' 2   Fixed Hard Drive
' 3   Network Drive
' 4   CD-ROM
' 5   RAM Drive

So in your case:

If DrvObj <> 2 then Docmd.Quit

Hope this helps

VBA, Open Access Database From Excel, If you wish to only import the data into excel you could use the topic covered in the article below: is run on a computer with a different version of Access installed. Note: Although this code was written in VBA for Excel, it is applicable to The codes below will open the access database located in the path� MS Access VBA Programming 1 Comment If all you wish is to determine the path of the current database, simply use the following line of code: Application.CurrentProject.Path

To begin, a disclaimer. I've never worked with newer access.

But, I'm pretty sure the following logic is correct.

  1. You don't need to declare any fso object.
  2. I assume CurrentProject.Path is similar to old CurrentDb.Name

All Paths for a desktop app will resolve to either

"x:\Path\" or

"\\Path"

Therefore, the chr @ P2 will be ":" or "\"

So,

sDB = CurrentProject.Path

Select case Mid, (sDB, 2, 1)
  Case ":"
  ' May be local, or -- networked if map network enabled
  ' We can use WMI, FSO, API to discover but that can be slow 
  ' (up to 750ms just to invoke)
  '
  ' Since we can easily inspect if root just by checking C:\

    If left(sdb,1) <> "C" then 
     ' Definit4ely networked
     '... do things close
    End If  

  Case "\"
    ' Definit4ely networked
    '... do things close
Emd Select

Edit:

It just occurred to me...

Why would users have different drive paths?

This should be a Front-End Back-End solution.

Such that all users access the front end via their local "C:\" drive

all tables should be linked to a back-end share as a dedicated network resource.. either "x:\maindb" or "\maindb".

That's assuming you want custom params or sec pr user.

In which case, all users will report "c:\" as CurrentProject.Path

Under such a scenario, no user would report a non C drive on close.

So, a question. Have you split this db into front end-back end?

Closing a Database and Exiting Access : MS Access, The Access program and database object window Close buttons. covered in this lesson are so simple an open database and exiting the Access (The Close button located in the far, upper right-hand corner of the screen� Use an Access database in a trusted location. When you place an Access database in a trusted location, all VBA code, macros, and safe expressions run when you open the database. You do not have to make trust decisions while the database opens. The process of using an Access database in a trusted location follows these broad steps:

Microsoft Access Performance Increase the Speed of Your Access , Microsoft Access Performance Tips to Speed up Your Access Databases This allows your database application to quickly locate specific records. You can significantly increase the performance of opening tables, forms, and reports that If it is not compiled, VBA compiles it by loading the code into memory, performing a� You may have these lines in separate subroutine if you like to separately open and close specific connections. So yeah guys, this how you establish a connection to the ACCESS database using ADO. There are other methods too, but this is the easiest way to connect to a data source of access through VBA. I have explained it in as detail as I can.

Disable the Microsoft Access Close Button to Prevent Users from , Accidentally Closing the Access Database Application The result is users accidentally closing Access when they shouldn't. This form is opened when the application starts and when it's closed code you can use, plus lots of other VBA /VB6 code; Our Total Access Startup program to Avoid Program Files Folder. I need to create a module that will open a CSV file from a specific location Then save it as a xls file over wirtting file in there Then running a Macro within excel to format the xls file. Then save it. Can this be done? I need the code for it. Thanks!

Vba check if folder exists, The next step is to check if the specified access rights exist in the DACL that was efficient solutions, would be to iterate through all the tables in the database. If VBA Code: An easy way to avoid errors is to check if a folder exists before open the linked file in VBA, and check to see if sheet Batch exists, then close the file.

Comments
  • Thanks user588, any thoughts on alternate methods?
  • never seen a PC with a drive named D:\ or X:\ ?
  • Hi Rockmo. Yes, I have split the DB and our teams are split across multiple offices/locations. So while at my office we may be under an H drive, for the other offices they may be under a D or X (while still connecting to the same shared/network drive) Hope that makes sense.
  • Dee, if I understand, you have unc paths within the front-end that specify the back-end tables but, you allow users to install the FE to different network shares mapped as drive paths with different letters. Why?