File Manipulation 1

[Roal]

Hi,

Can anyone help me with the following problem,

I'm trying to link documents to an Access97 table using the following piece of code and I need stop files from being seen if they have already been loaded.

Any suggestions on how I can do this, VBA's not a language that I've used much so don't worry about stating the obvious.

Private Sub cmdLoadOLE_Click()

Dim MyFolder As String
Dim MyExt As String
Dim MyPath As String
Dim MyFile As String
Dim strCriteria As String

MyFolder = "H:\DATA\Correspondence Database\Documents"

MyPath = MyFolder & "\" & "*.doc"

MyFile = Dir(MyPath, vbNormal)
    Do While Len(MyFile) <> 0
        [DocumentPath] = MyFolder & &quot;\&quot; & MyFile
        '[OLEFile].Class = &quot;word.Document&quot;
        [OLEFile].OLETypeAllowed = acOLEEmbedded
        [OLEFile].SourceDoc = [DocumentPath]
        [OLEFile].Action = acOLECreateEmbed
        
    MyFile = Dir
    
DoCmd.RunCommand acCmdRecordsGoToNew


Loop
        


End Sub

Thanks in advance,

Roal
"You need to learn to run before you can walk"

 

[DirectDrive]

I would create a table that holds a list of previously loaded files (tblHist) and a table that will hold the current list (tblFiles). I would make a query qryNewFiles that uses an outer join for these 2 tables. you will want to retun the files that are null in tblHist:

SELECT tblFiles.Files, tblHist.Files
FROM tblFiles LEFT JOIN tblHist ON tblFiles.Files = tblHist.Files
WHERE (((tblHist.Files) Is Null));


First dump the tblFiles and populate it with the contentes of H:\DATA\Correspondence Database\Documents. The open the query that joins hist with current files. This will give you a list of files to process.
As you process each file push it to tblHist, so next time you will not process it.


also..... presuming that "H:\" is a network drive..... you may want to use UNC path, so this will run on any computer in your company without the risk of the mapping being different.

 

[Roal]

Thanks,

This looks like it should get the result that I need without the need for writting to much VB.

I'll look up UNC Path, thanks for the tip.

Look out for my follow up post "Using UNC Path to recognise network diretories"

 

[DirectDrive]

Regarding UNC.
Presuming you are connecting to H:\DATA\Correspondence Database\Documents.....
Look in your windows explorer and it will have something like the following next to H:
ShareName on 'ServerName' (H

so you will want to referance your drive as \\ServerName\ShareName\DATA\Correspondence Database\Documents

The computer uses UNC to look find the network drive. The drive letter is an alias for your convenience. The problem is people can map different drives to different letters, so you couls send your mdb to another user with a different drive mapped to H: and it would not work.

 

[Roal]

Thanks, it probably would have taken me a while to work that out.

Followed your directions and everything is working fine.