Create a link to a table using vba

Question

If it possible to create a link between an Access 2010 database and a .CSV table using VBA?  I'd like to automate the External Data/Text File link process.  I have tried the OLE1 example in the MS Access Help file, but can't get it to work.  At first I was getting an "Object Required" error.  I then added both Dim and Set statements, setting the object to "Nothing" (because I didn't know what to set it to), and it still won't run.  This may not even be the way to approach the task, I don"t know.

Here is my modified code:

Private Sub CreateFileLink()

   Dim Test As Object

   Set Test = Nothing

' Set class name - I removed this because it didn't seem necessary for creating a link.

' Test.Class = "Excel.Sheet"

 ' Specify type of object.

    Test.OLETypeAllowed = acOLELinked

 ' Specify source file.

    Test.SourceDoc = "C:\Users\Walt\Documents\Downloads\MyTestFile.csv"

 ' Specify data to create link to.  I also removed this because I want to link the entire file

 ' Test.SourceItem = "R1C1:R5C5"

 ' Create linked object.

    Test.Action = acOLECreateLink

End Sub

Answer 1

Amend the code like this:

    With Application.FileDialog(1)

        .InitialFileName = "C:\Users\Walt\Downloads"

        If .Show Then

where C:\Users\Walt\Downloads\ is the path to your Downloads folder.

Answer 2

That is absolutely PERFECT, Ken.  Thank you so much. 

Every time you, or one of the others who provide us amateurs with so much help, I am simply amazed at the solutions.  Then I ask myself, "Why couldn't I find that in the Microsoft help files?"  Better yet, why don't any of the many manuals I have in front of me cover things like this.  I guess that is like asking why do our hearts beat.  No viable answer.

In trying to understand the code you provided, I tried changing the "(1)" to "(2)" in both .FileDialog and .SelectedItems lines, and learned that .FileDialog(2) resaves the selected file, and .SelectedItems(2) returns an error.  The rest of the code is pretty self explanatory, although I did try to get more information about the TransferText portion of the DoCmd line.  That research directed me to Office Online, which apparently covers everything except Access - - Groan.

One question I was trying to answer is, "what determines which Library window is opened"?  When I run the code the first time,  the Documents window opened.  I navigated to the "Downloads" window, which is where I need to go to find my file.  After I made the selection and ran the code additional times,  the "Downloads" window opens.  However, if I go to the "Documents" window and select a file, additional runs defaults to that window.  This leads me to believe that Windows saves the last location from which a file was selected and the code goes to that last location.

Is there a way to direct the action to the "Downloads" window regardless of what file list window was last used?

Answer 3

You could do something like this:

    Dim strPath As String, strFile As String

    With Application.FileDialog(1)

        If .Show Then

            strPath = .SelectedItems(1)

        Else

            MsgBox "No file selected", vbInformation

            Exit Sub

        End If

    End With

    strFile = Dir(strPath)

    strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_lnk"

    DoCmd.TransferText acLinkDelim, "", strFile, strPath, True, ""

This will allow the user to browse to the file to be imported and then created a linked table with the file's name (less the extension) followed by '_lnk'.  I've assumed in the above that the text file will have column names

Answer 4

Daniel,

I apologize for not explaining my goal better.  Yes, I want to automatically create a linked table to a csv file.

I currently have a link to a csv file in my Access database.  But I would like to set it up so that another user on another database copy could input a file name in an Input Box, and then have my vba code create a permanent link. 

The user won't have access to the Access menus, so won't be able to set it up manually by clicking on the External Data & Text File tabs and then completing the process via the Get External Data - Text File window.

Answer 5

I'm not sure if I understand.  Could you explain a little more and I'm sure we can get it functional for you.

You want to use a csv as an attachment in a table?

You want to automatically create a linked table to a csv file?

Something else altogether?

Thank you for the clarifications, it will help us help you.