DAO.Database open to improve FE & BE efficiency

[perryc]

All, why the following path in the code says to me the error message: Trouble opening database: \\bsrfs\shared.... error: could not find the "\\bsrfs\shared\...."


Does this DAO only work with a certain version of ACCESS? Mine is 2010.

Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)
' Source : Total Visual SourceBook


    Dim x As Integer
    Dim strName As String
    Dim strMsg As String

    ' Maximum number of back end databases to link
    Const cintMaxDatabases As Integer = 2


    ' List of databases kept in a static array so we can close them later
    Static dbsOpen() As DAO.Database

    If pfInit Then
        ReDim dbsOpen(1 To cintMaxDatabases)
        For x = 1 To cintMaxDatabases
            ' Specify your back end databases
            Select Case x
            Case 1:
                strName = "\\bsrfs\shared\Dept_Intake\BSHH_IntakeReferral_Da tabase_be.accdb"
                'Case 2:
                ' strName = "H:\folder\Backend2.mdb"
            End Select
            strMsg = ""


            On Error Resume Next
            Set dbsOpen(x) = OpenDatabase(strName)
            If Err.Number > 0 Then
                strMsg = "Trouble opening database: " & strName & vbCrLf & _
                         "Make sure the drive is available." & vbCrLf & _
                         "Error: " & Err.Description & " (" & Err.Number & ")"
            End If


            On Error GoTo 0
            If strMsg <> "" Then
                MsgBox strMsg
                Exit For
            End If
        Next x
    Else
        On Error Resume Next
        For x = 1 To cintMaxDatabases
            dbsOpen(x).Close
        Next x
    End If
End Sub

[CJ_London]

recommend you surround your code with the code tags to preserve indentation and make it more readable in the future - makes it more likely you will get a response. But to answer your question you can use unc paths in access so it is likely there is an issue with the path or database - e.g. is it a .accdb or .mdb (since your second file is a mdb)

[perryc]

recommend you surround your code with the code tags to preserve indentation and make it more readable in the future - makes it more likely you will get a response. But to answer your question you can use unc paths in access so it is likely there is an issue with the path or database - e.g. is it a .accdb or .mdb (since your second file is a mdb)

TY for the tip! I will keep that in mind.

As far as UNC path, the first line was my database, since I don't have the second one, I commented it out. I copied the codes from another forum.

strName = "\\bsrfs\shared\Dept_Intake\BSHH_IntakeReferra l_Da tabase_be.accdb" (pointed to my backend database)

I changed the path to: "R:\Homecare - All - Scans\Dept_Intake\BSHH_IntakeReferral_Database_be. accdb" but the same result.

[orange]

As Ajax suggested, did you make sure the file actually exists independent of Access (such as File Explorer)?

[perryc]

As Ajax suggested, did you make sure the file actually exists independent of Access (such as File Explorer)?

YES. How I got the path, was I went to that folder, copy and paste the path and the file name over... and paste onto the code instead of typing.

Well... I guess if I looked carefully what the error message may help!! so sorry. So, apparently, it is not the path that was wrong, it was because I put a password onto the Backend. So, what code do I need to add to remedy that? So sorry!

[CJ_London]

google 'vba opendatabase with password' or similar to find the parameters you require. Plenty of examples out there. here is an example

https://answers.microsoft.com/en-us/...c-68b599b31bf5

[perryc]

google 'vba opendatabase with password' or similar to find the parameters you require. Plenty of examples out there. here is an example

https://answers.microsoft.com/en-us/...c-68b599b31bf5

yep... that works! Thank you. I am not sure if this really improve my connectivity to the Backend... I guess I will try.

[Micron]

I am not sure if this really improve my connectivity to the Backend

If you link the tables with a password, then you don't need to supply it in code or anywhere else. However, be advised that the password can be read by those who know how IF they can view the objects in the nav pane. This can be either a good or bad thing. In an accde, you can't read code where the password value for the BE is located. If for some reason your 'master' FE db is toast, so are you. However, the linking password can be discovered if you know how, which means you could at least open the BE.

[perryc]

If you link the tables with a password, then you don't need to supply it in code or anywhere else. However, be advised that the password can be read by those who know how IF they can view the objects in the nav pane. This can be either a good or bad thing. In an accde, you can't read code where the password value for the BE is located. If for some reason your 'master' FE db is toast, so are you. However, the linking password can be discovered if you know how, which means you could at least open the BE.

Ty. I think I solved it by the previous suggestion. I have mine published in an accde so, it should be ok. I keep a copy of the accdb as well.