docmdTransferText and linking to a document

[zigex]

Hello all,
I'm new here and not very experienced with access, so I hope you'll forgive me for posting a question without first posting advice (and also for my wonky use of terminology!). I've tried posting this to other forums but haven't had much luck - a colleague of mine pointed me here as somewhere to go with questions.
I'm currently working on a way to import data from many word documents into a table - I've got this working fine using the following code:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err


Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String


strFolderPath = "C:\Users\zigex\Documents\Super Project\testfiles\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files


For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, "Monthly Import Specification", "monthlyimport", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "C:\Users\zigex\Documents\Super Project\testfiles" & objF1.Name 'Move the files to the archive folder
End If
Next


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing


bImportFiles_Click_Exit:
Exit Sub


bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit


End Sub

However, I'm wanting to add a link to those files to each record so that the user can click on the record (or select one and then use a button) to open each document in it's native environment (MS Word). I know how to do the opening bit, but am unable to make the docmdTransferText automatically generate a link to the document and store it. As these files may move around (and there are a lot of them) I need to make sure it's done automatically so can't really get the user to write it in manually.


Any help would be immensely appreciated!


All the best,
Ziggy

[pbaldy]

You're importing into the same table? Presuming it has a field for the path, you can run an update query inside your loop that updates all records with Null in that field to the path. That would obviously require all existing records have a path so only the newly imported record(s) were updated.

[zigex]

Thanks for your help Paul.
Yes, I just want to import all of those files into one table (which has a column for path) and have the path added for each record. My difficulty is how to get the file path for each document and how to add it to the record - basically what the update query should be.
I've looked for commands to get the path of objects but I've not come up with much luck.

[pbaldy]

Don't you already have the path?

strFolderPath & objF1.Name

[zigex]

Thanks again for the response, I've had a work on it and got it to add the correct path for the document, but unfortunately it does this only adds this data for the top import. I'm sure it's something very simple, but I'm not quite sure what it is:

Private Sub bImportFiles_Click()On Error GoTo bImportFiles_Click_Err


Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim rs As dao.Recordset
Dim db As dao.Database


strFolderPath = "C:\Users\zigex\Documents\Super Project\testfiles\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files


For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
    DoCmd.TransferText acImportDelim, "Monthly Import Specification", "monthlyimport", strFolderPath & objF1.Name, False
    Set db = CurrentDb
    Set rs = db.OpenRecordset("monthlyimport", dbOpenDynaset)
    rs.Edit
    rs.Fields(8) = strFolderPath & objF1.Name
    rs.Update
    rs.Close
    db.Close
    ''Name strFolderPath & objF1.Name As "C:\Users\zigex\Documents\Super Project\archive" & objF1.Name 'Move the files to the archive folder
End If
Next


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing


Me.Refresh
bImportFiles_Click_Exit:
Exit Sub

As always, any help would be appreciated!

[pbaldy]

You'd have to loop the recordset to update more than one record. I was thinking more like:

db.Execute "UPDATE monthlyimport SET FieldName = '" & strFolderPath & objF1.Name & "' WHERE FieldName Is Null"

which should update all records where the field is blank. That should be the newly imported records.

[zigex]

You'd have to loop the recordset to update more than one record. I was thinking more like:

db.Execute "UPDATE monthlyimport SET FieldName = '" & strFolderPath & objF1.Name & "' WHERE FieldName Is Null"

which should update all records where the field is blank. That should be the newly imported records.

Thank you very much! This was exactly what I needed! I think I was getting a bit confused with my loops. You've made a busy man a happy man!

[pbaldy]

Happy to help!