'FolderExists' alternative? (1 Viewer)

C

Chrisopia

Registered User.
Local time
Today, 14:41
Joined
Jul 18, 2008
Messages
279
I am using the "folderexists" function to determine the location of the backend.
This is followed by tableDef functions to relink the database with the backend.

the folderExists function is a custom function I found on google, and it works but makes start up incredibly delayed.

Code: 
If FolderExists("\\Bath\####") Then
Branch = "Bath"
'ElseIf FolderExists("\\CHIPP-NAS\####") Then
Branch = "Chippenham"
ElseIf FolderExists("\\NAS\####") Then
Branch = "Trowbridge"
Else
    MsgBox "Your backend directory could not be found. Please restart the system. If this message continues to appear, please call for assistance", vbExclamation, "Directory Error"
End If

If Branch = "Bath" Then
ImgPath = "####\images\"
BePath = "####\BackEnd\"
InvoiceRef = "DPBA"
BranchhID = 1

If Branch = "Chippenham" Then ...

I was wandering if there was a quicker way to find where the backend resides but to also set the branch location and hence set the relevant variables

I was looking into error handling, but it seems to only handle a 2 case scenario, I would need 3 cases (one for each branch) and a final error message.

Code: 
Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function
 
C

Chrisopia

Registered User.
Local time
Today, 14:41
Joined
Jul 18, 2008
Messages
279
At the moment it's in 3 basic steps,

- Establish which branch the database is
- Determine what the path is for the back end
- reconnect to backend

the method I am doing at the moment is using the different paths for the backend location to determine where the database is.
I could just check what the network drive is called, e.g. 'Bath' , 'Chippenham' or 'NAS' to determine the BePath variable.

The only way I could think of was using "FolderExists", but using this slows down the system, as does the block of "If" functions.

I would assume error handling would be quicker at processing it, but I have no idea how to implement it.

e.g.

OnError:
"Bath"
Then
OnError:
"Chippenham"
Then
"Trowbridge"
End Error
End Error

Code: 
'FolderExists function in full
Public Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    Dim lngAttributes As Long
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function
Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

'Function ran on Startup to determine branch variables
Public Function StartUp()
   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String

'What branch is it
If FolderExists("\\BATH\####") Then
    Branch = "Bath"
ElseIf FolderExists("\\CHIPPENHAM\####") Then
    Branch = "Chippenham"
ElseIf FolderExists("\\NAS\####") Then
    Branch = "Trowbridge"
Else
    MsgBox "Your backend directory could not be found. Please restart the system. If this message continues to appear, please call for assistance", vbExclamation, "Directory Error"
End If

'Determine paths and variables
If Branch = "Bath" Then
    ImgPath = "\\BATH\####\images"
    BePath = "\\BATH\####\backend"
    InvoiceRef = "DPBA"
    BranchhID = 1
ElseIf Branch = "Chippenham" Then
    ImgPath = "\\CHIPPENHAM\####\Invoices\"
    BePath = "\\CHIPPENHAM\####\System\"
    InvoiceRef = "DPCH"
    BranchhID = 2
ElseIf Branch = "Trowbridge" Then
    ImgPath = "\\NAS\####\Invoice Scans\"
    BePath = "\\NAS\####\Database\"
    InvoiceRef = "DPTR"
    BranchhID = 3
End If

'Finds backend information and creates links determines by the BePath variables
   Set dbsTemp = CurrentDb
         ConnectOutput dbsTemp, _
            "tblAddress", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblAddress"
        ConnectOutput dbsTemp, _
            "tblContact", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblContact"
        ConnectOutput dbsTemp, _
            "tblCustomers", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblCustomers"
        ConnectOutput dbsTemp, _
            "tblDetails", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblDetails"
         ConnectOutput dbsTemp, _
            "tblInvoice", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblInvoiceQuote"
         ConnectOutput dbsTemp, _
            "tblOrder", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblOrder"
         ConnectOutput dbsTemp, _
            "tblZ1", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblZ1"
         ConnectOutput dbsTemp, _
            "tblZ2", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblZ2"
Starting
End Function

Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)

   Dim tdfLinked As TableDef
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)
   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable

'Decides whether to create the link or refresh it
On Error GoTo Err
   dbsTemp.TableDefs.Append tdfLinked 'creates the link
Exit Sub
Err:
    Select Case Err.Number
    Case 3012
     dbsTemp.TableDefs.Refresh 'refreshes link
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error in Startup()"
  End Select
End Sub
 
Solo712

Solo712

Registered User.
Local time
Today, 17:41
Joined
Oct 19, 2012
Messages
828
Chrisopia said:
At the moment it's in 3 basic steps,

- Establish which branch the database is
- Determine what the path is for the back end
- reconnect to backend

the method I am doing at the moment is using the different paths for the backend location to determine where the database is.
I could just check what the network drive is called, e.g. 'Bath' , 'Chippenham' or 'NAS' to determine the BePath variable.

The only way I could think of was using "FolderExists", but using this slows down the system, as does the block of "If" functions.

I would assume error handling would be quicker at processing it, but I have no idea how to implement it.

e.g.

OnError:
"Bath"
Then
OnError:
"Chippenham"
Then
"Trowbridge"
End Error
End Error

Code: 
'FolderExists function in full
Public Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    Dim lngAttributes As Long
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
 
    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function
Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function
 
Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function
 
'Function ran on Startup to determine branch variables
Public Function StartUp()
   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String
 
'What branch is it
If FolderExists("\\BATH\####") Then
    Branch = "Bath"
ElseIf FolderExists("\\CHIPPENHAM\####") Then
    Branch = "Chippenham"
ElseIf FolderExists("\\NAS\####") Then
    Branch = "Trowbridge"
Else
    MsgBox "Your backend directory could not be found. Please restart the system. If this message continues to appear, please call for assistance", vbExclamation, "Directory Error"
End If
 
'Determine paths and variables
If Branch = "Bath" Then
    ImgPath = "\\BATH\####\images"
    BePath = "\\BATH\####\backend"
    InvoiceRef = "DPBA"
    BranchhID = 1
ElseIf Branch = "Chippenham" Then
    ImgPath = "\\CHIPPENHAM\####\Invoices\"
    BePath = "\\CHIPPENHAM\####\System\"
    InvoiceRef = "DPCH"
    BranchhID = 2
ElseIf Branch = "Trowbridge" Then
    ImgPath = "\\NAS\####\Invoice Scans\"
    BePath = "\\NAS\####\Database\"
    InvoiceRef = "DPTR"
    BranchhID = 3
End If
 
'Finds backend information and creates links determines by the BePath variables
   Set dbsTemp = CurrentDb
         ConnectOutput dbsTemp, _
            "tblAddress", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblAddress"
        ConnectOutput dbsTemp, _
            "tblContact", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblContact"
        ConnectOutput dbsTemp, _
            "tblCustomers", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblCustomers"
        ConnectOutput dbsTemp, _
            "tblDetails", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblDetails"
         ConnectOutput dbsTemp, _
            "tblInvoice", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblInvoiceQuote"
         ConnectOutput dbsTemp, _
            "tblOrder", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblOrder"
         ConnectOutput dbsTemp, _
            "tblZ1", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblZ1"
         ConnectOutput dbsTemp, _
            "tblZ2", _
            ";DATABASE=" & BePath & "Central_be v6.accdb", _
            "tblZ2"
Starting
End Function
 
Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)
 
   Dim tdfLinked As TableDef
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)
   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
 
'Decides whether to create the link or refresh it
On Error GoTo Err
   dbsTemp.TableDefs.Append tdfLinked 'creates the link
Exit Sub
Err:
    Select Case Err.Number
    Case 3012
     dbsTemp.TableDefs.Refresh 'refreshes link
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error in Startup()"
  End Select
End Sub
Click to expand...

You can find out quickly the backend for your current database without having to do directory searches.

See if this helps : http://www.access-programmers.co.uk/forums/showpost.php?p=247847&postcount=2

Best,
J.
 
C

Chrisopia

Registered User.
Local time
Today, 14:41
Joined
Jul 18, 2008
Messages
279
CurrentDB is useful, but only when a path is already established.

I need it to find what network is available on the system, and that will tell it what branch we are in.
If I used it on the front end, I would have to name every computer on the network... I can't point to the backend because that is what I'm trying to define.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 23:41
Joined
Aug 11, 2003
Messages
11,695
You must log in or register to reply here.

Similar threads

M
Solved VBA to exchange any "/" with "-" to create folder name
Replies
3
Views
671
Morphies
M
C
How to gracefully handle errors generated when opening a form with linked data?
Replies
6
Views
699
cricketbird
C
C
Sendkeys not working anymore
Replies
7
Views
810
NauticalGent
NauticalGent
A
Run-time error 52 Bad file name or number
Replies
11
Views
992
Pat Hartman
P
M
Solved Error making directory
Replies
5
Views
658
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom