docmdTransferText and adding links to the document

[ziglex]

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'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 the table 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've tried adding a self-referencing hyperlink to a form in the word document and then reading that in - but that doesn't seem to work. 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

 

[spikepl]

Store full path as text - forget the hyperlink data type.

To link to the file, in a double-click event for the control showing the field with the path, call

FollowHyperlink Me.txtMyPath

Google Access FollowHyperlink

 

[ziglex]

Store full path as text - forget the hyperlink data type.
To link to the file, in a double-click event for the control showing the field with the path, call
FollowHyperlink Me.txtMyPath
Google Access FollowHyperlink

Thanks for that - it's good to know not to bother with the hyperlink data type, but could you tell me how to store the full path as text?

I can't work out how to import the path data into the record automatically.

 

[ziglex]

I've had a work on it and finally managed to get 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!

 

[Gasman]

PMFJI,

By 'Top' import, do you mean just the first record in the table with the last file name in the folder?

 

[spikepl]

google

Access loop recordset

and the cure will become obvious to you

 

[ziglex]

PMFJI,

By 'Top' import, do you mean just the first record in the table with the last file name in the folder?

Sorry that wasn't clear - I meant that when I run the import it only adds the filepath to the first record in the import set and leaves the others blank.

Spikepl - whilst I appreciate your help and appreciate that you're trying to help me learn, I'm afraid I'm still not sure what to do. I've tried the .MoveNext command but that doesn't seem to work either. If you do know the answer it'd be really helpful to hear it!

 

[Gasman]

I'd look at the first link brought up by Spikepl's phrase.

However I know what it is to try and learn and still be unsure on how everything works.

You appear to be looping through the all the files in the folder, but are opening the database and closing it each time.?

What record do you land on when you open a database?
Are you actually moving to another record in the database.?
Should you be adding or editing? That is only something you can know depending on your application.

Have a look at the code in the link I posted.

From your code I'd expect you to have the last file path in the folder stored in the first record of the table.

When this happens to me, I would use the debug option and walk through the code step by step, trying to see where I have gone wrong. That generally shows me my error.

 

[CJ_London]

cross posted here

http://www.accessforums.net/import-export-data/docmdtransfertext-linking-document-51332.html