Solved

Access - Import data from .txt in a path relative to the .accdb

Posted on 2014-09-22
18
280 Views
Last Modified: 2014-09-24
I have an Access 2013 database (I didn't write it, but given the task of updating) that uses a macro to import data from txt files in another directory.  The macro is hard coded with the drive letter so I converted it to VB, and would like to change it to use a relative path to make it more portable and usable for others that may already have the drive letter in use (like me).  Is this possible?  I've done some searching on the net, but haven't really found anything that tells me one way or the other.
It's an Access 2013 front end (.accdb) linked to a SQL 2012 db.

The paths it's currently configured to use are:
Database path:  Y:\dept\db\FrontEnd\PCName\DB.accdb
Import file location:  Y:\Dept\db\FileTransfer\Data\InputFile.txt

I was hoping to use a ..\.. shortcut statement in the path to the import file, but nothing I've tried seems to be working.  Admittedly, I'm very new to DB design and VB scripting.

Thanks,
~RW
0
Comment
Question by:dcgimo
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40337922
I generally give  users a place to store common paths.  In multi-user applications I may need to store a path for each user or I may need to store a path for all users but different paths for different things.  Once you analyze your situation, create a table.  Then create a form that will populate the table.  To make it user friendly, add a builder button to let the user navigate to a folder rather than making them type it in.

I've included a few code snippets.  cmdBrowse_Click() is the click event of the ... button.  It opens the common file dialog.  At the end is code I use to get the saved path.  In order to keep me from writing into production directories, the code checks for my ID and writes to a testing directory otherwise, it gets the appropriate folder from the table.

Private Sub cmdBrowse_Click()
    Me.txtCHIPAAReportsFolder = fChooseDirectory()
End Sub

Public Function fChooseDirectory()

    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function


...... use the saved path
    If Environ("username") = "Remote Foxpro" Then
        sPath = "S:\Pat\DSS_Exports\ExportFiles\"
    Else
        'Me.txtFolder = "P:\afox\scc\dss\"
        sPath = DLookup("CHIPAAReportsFolder", "tblAgencySetUp", "AgencyID = 1")
    End If
    
    If Right(sPath, 1) = "\" Then
    Else
        sPath = sPath & "\"
    End If

Open in new window

Browse button
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40338565
You can use this function:
Public Function GetCurrentDatabaseDir( _
  Optional ByVal booTrailingBackslash As Boolean = False) _
  As String

  Dim strDatabasePath As String
  Dim strDatabaseName As String
  
  On Error Resume Next
  
  strDatabasePath = LCase(DBEngine(0)(0).Name)
  
  strDatabaseName = Dir(strDatabasePath, vbNormal)
  strDatabasePath = Mid(strDatabasePath, 1, _
                    Len(strDatabasePath) - _
                    Len(strDatabaseName) - Abs(Not booTrailingBackslash))

  GetCurrentDatabaseDir = strDatabasePath

End Function

Open in new window

/gustav
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40339780
here is a much shorter way of getting the current database path:

Application.CurrentProject.Path

Save that to a variable, and combine it with the rest of the path.
0
 

Author Comment

by:dcgimo
ID: 40340088
@Pat Hartman - The users for this database are not tech savvy in any way; having them enter this in will cause more issues than leaving it the way it is now.  I'll play with it and see if I can get it to work in this case and create an admin-type of screen where it can be changed at deployment.

@ Gustav - What can I use for my FileCopy statements?  
Here's the command it's using now (converted to vbscript from a macro):
DoCmd.TransferText acImportDelim, "TblBurnsFire Import Specification", "dbo_tblBurnsFire", "Y:\FileTransfer\DataFromTablets\tblBurnsFire.txt", False, ""

@ Helen Feddema - That gives me the path, I don't have a problem getting that, I'm looking for a way to move two directories up the filesystem from the DB path as part of the import function.

Thank you for your assistance,
~RW
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40340091
The problem with using the current path is it will be local and different for each user.  If you want a shared network folder, you need to use some other method.  I like my method because it lets the users choose but you could use the folder of the BE if you want a shared folder although I suggest that you use subdirectories rather than the folder that holds the BE.

You can get the folder of the BE out of the MSysObjects table database column.

PS, I've never had tech savvy users.  I wouldn't know what to do.  However, if they can be trusted to save an excel document and find it again, they can use a form such as the one I showed.  They only have to use it once to set things up.  It's not like they have to use it to export every file.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40340122
In that case a FolderPicker dialog is probably the best way to go.  I often place path selectors on a main menu, so they can be selected once and used throughout the database.  They don't have to be changed unless the paths themselves are changed.  Here is a screen shot of a main menu with path selectors:
New Style Main Menu
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40340156
However, this might work:

Public Function GetGrandparentFolder() As String

   Dim strCurrentPath As String
   Dim fso As New FileSystemObject
   Dim fld As Scripting.Folder
   Dim fldParent As Scripting.Folder
   Dim fldGrandparent As Scripting.Folder
   
   strCurrentPath = Application.CurrentProject.Path
   Set fld = fso.GetFolder(strCurrentPath)
   Set fldParent = fld.ParentFolder
   Set fldGrandparent = fldParent.ParentFolder
   GetGrandparentFolder = fldGrandparent.Name

End Function

Open in new window

0
 

Author Comment

by:dcgimo
ID: 40340169
@Pat - The end-users for this db are all using the 'Y' drive.  I am trying to avoid defining a drive letter within the database, if possible, should something occur and using 'Y' is not an option.  That's why I would like to use a path relative to the database's location
            - Agreed; I have some users that could probably handle making this change themselves, but I don't want to give them the option of messing it up.
            - If I have to maintain defining the drive letter, I'll probably do something similar to your original code sample on an administrative page that only I can access.

@Helen - regarding the FolderPicker menu - see above message to Pat.
               - I'll check out that code sample and see if that will work.

Thanks again,
~RW
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40340282
I never rely on mapped drives if I can avoid it.  My BE is always linked using the UNC so the path to the BE is:
\\newfiscal\Afox\AFox\AccessApplications\Database\AOAClientManagement_DataVer140827.accdb

That way it doesn't matter how the users have the drives mapped.  Some of my clients can't come close to an available drive letter for new shares so this is the only way.

As far as other folders go, if you keep the paths for each person, it doesn't matter if they have different drive letters.  If you keep them for the app, then they must be consistent so UNC is a better choice.  Again, common mappings are generally done once by an administrator and then left alone unless the folder is moved to a different share.

Helen's code to get the grandparent folder will work but just keep in mind that your starting point is the folder containing the FE and so it will point to something on the user's PC rather than a shared folder on a server.  If it is a place to hold exported files for the given user, it will be fine.  If it is for a place to hold common/shared files, it won't work.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40340884
Here's the command it's using now (converted to vbscript from a macro):
 DoCmd.TransferText acImportDelim, "TblBurnsFire Import Specification", "dbo_tblBurnsFire", "Y:\FileTransfer\DataFromTablets\tblBurnsFire.txt", False, ""
That would be:
DoCmd.TransferText acImportDelim, "TblBurnsFire Import Specification", "dbo_tblBurnsFire", GetCurrentDatabaseDir(True) & "tblBurnsFire.txt", False, ""

Open in new window

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40341481
You can even use a one-liner:
Dim strPath As String
strPath = StrReverse(Split(StrReverse(CurrentDb.Name, "\", 2)(1)) & "\"
DoCmd.TransferText acImportDelim, "TblBurnsFire Import Specification", "dbo_tblBurnsFire", strPath & "tblBurnsFire.txt", False, ""

Open in new window

/gustav
0
 

Author Comment

by:dcgimo
ID: 40341992
@Pat - That still doesn't really accomplish my goal; Using the server name in a UNC, to me, is worse than a drive letter; I can change the mapped drive easily database gets updated or moved.  The users are launching the database from a network drive mapped to their account as 'Y'.  For me, right now at least, it's in my 'J' drive.

@Gustav - I tried the one-liner code and got a "Compile error: Expected: list separator or )"; I added a ) after the "\".  Should it go there, or after the (1))?  In either location, I'm getting this compile error, Compile Error: Wrong number of arguments or invalid property assignment.
Does 'StrReverse' need to be declared?  If so, as what?

Thanks,
~RW
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40342026
Sorry, it should read:

strPath = StrReverse(Split(StrReverse(CurrentDb.Name), "\", 2)(1)) & "\"

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40342038
The users are launching the database from a network drive mapped to their account as 'Y'
Please don't tell us that all the users are opening the very same copy of the FE.

Every user should have his own personal copy of the FE, preferably loaded on his C drive to minimize network traffic.  I use a very simplistic approach.  I create a .bat file on a server drive.  The user's shortcut opens the .bat file.  The .bat file downloads the current version of the database and then opens it on their C: drive.  There are more sophisticated tools available that compare versions and only download a new FE when the versions change.

Using the UNC for BE mapping is pretty standard practice.  It means that the users don't even have to have a drive mapped to the BE which heightens security.  When you use a drive letter, either everyone has to relink his own FE (which I don't recommend) or everyone has to have the same drive letter available to map to the BE's server (which is not always possible, especially in large companies).
0
 

Author Comment

by:dcgimo
ID: 40342140
@Pat - My initial question stated where the front end was located (Database path:  Y:\dept\db\FrontEnd\PCName\DB.accdb) and that it' using SQL 2012 as it's BE.  The PCName folder is unique for each user so nobody is sharing a FE and since it's a SQL BE, there's no need to map it via UNC.

@Gustav - That works...sort of.  It's getting to the right drive letter, but isn't changing directories.
The folder for the front end is J:\dept\db\frontend\pcname\
The folder with the text files is J:\dept\db\filetransfer\dataimport\

I changed
"tblBurnsFire.txt", False, ""
to "FileTransfer\DataImport\tblBurnsFire.txt", False, "" and am getting a runtime error '3044': J:\Dept\DB\FronEnd\PCNAME\FileTransfer\DataImport\ is not a valid path.

Thanks,
~RW
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40342153
Forgot that.
Here is what to do - using DOS syntax for moving up and down the path:
Dim strPath As String
strPath = StrReverse(Split(StrReverse(CurrentDb.Name), "\", 2)(1)) & "\..\..\filetransfer\dataimport\"
DoCmd.TransferText acImportDelim, "TblBurnsFire Import Specification", "dbo_tblBurnsFire", strPath & "tblBurnsFire.txt", False, ""

Open in new window

/gustav
0
 

Author Closing Comment

by:dcgimo
ID: 40342275
I'd like to thank all of you for your contributions.
Gustav - Your solution accomplishes exactly what I need.
Have a great week!
~RW
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40342324
Thanks!

/gustav
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now