Use Access to search for files in a network location

[dawnpgeo]

Hi Folks.



I'm very new to Access. I've created some databases including queries, macros, forms, etc. Very basic stuff.

Here is what I'd like to do:

I have a database containing a table with part numbers in one of the fields. I'd like for Access to search a network location of drawing files for each of the part numbers listed in the table and tell me if the drawing file exists.

Is this possible?

Thanks so much,
Dawn

[Robeen]

Hi,
It is possible to have Access tell you if a file exists.
You can use code like this:

If FileExists(MyFileName) Then
    Msg Box MyFileName & " exists."
End If

I use something like the above to find out if files exist so I can delete them first before writing a new version of the file in the same location.
I have a function called KillMyFile.
To use it I have to pass it the name of a file to 'Kill'.
This is how I do it:
1. In a Macro I have a 'RunCode' Action that has this in it:
KillMyFile("\\NetworkDriveName\FolderName\FileName.xls")
What that's saying is - run the function named 'KillMyFile' and pass it the file name in the parentheses.

2. My function looks like this:

Function KillMyFile(file_name)
Dim MyFileName As String

MyFileName = file_name

If FileExists(MyFileName) Then
    Kill MyFileName
End If

End Function

This may not be the most elegant way of doing what I need - but it works for me.
Perhaps you can use this to do what you need.

Hope this helps!

P.S. Please go to Page 2 of this thread to see where I later posted the 'FileExists()' function - without which the above code sample will not work.

[dawnpgeo]

Thanks for the reply, Robeen.

Gosh!... that looks so "Greek" to me. *sigh* I guess I have lots and lots to learn.

I knew Access could handle that with someone who knows how to use it. :-)

Can anyone suggest some learning materials that will get me kick started in writing code?

So, here is a scenario a bit more to what I really need

I have a table with a field called "Part number" and a checkbox field called "drawing exists". Access searches the directory with the first 7 or 8 characters of the part number field to see if the drawing exists. If it does exist it checks the box in the "drawing exists" field.

Please keep it on a 3rd grade level. :-)

Thanks,
Dawn

[Robeen]

Ok . . . 3rd Grade it is!!

First let me just say - by way of encouragement - that what you're trying to do is pretty basic. It only looks complex to you because you've never done it before. With a little hands-on experience, you will get more comfortable working in Access.
There are free Tutorials on line on YouTube that are very helpful to get you started. You can follow along and you'll find that you will soon get comfortable working in the environment and you'll start getting adventurous!! Fear not!

Ok . . . enough blather . . .

Here's what you need to do - in Summary:
1. If you don't already have a Form based on your Table, you will have to create a Form.
If your Table does not have a field to record if the PartNumber file exists - you should add a new field to the Form & call it, for example, 'HasFile' - and give it a Yes/No data type.
2. When a New record is displayed on your Form, you want to tell Access to:
3. Read the value [text] that is in the text box that is displaying your PartNumber.
4. Execute some code that will search your network or pc directory [folder] for a file that bears the same name as your PartNumber.
5. If it finds such a file in that folder - turn the CheckBox on your Form 'on' [set it to checked . . or 'True'].

Now - assuming you have the Form set up and the 'HasFile' field in your Table - I'll start at Step 2.:
Step 2.
a. When a new record is displayed on your Form an 'Event' named 'On Current' happens behind the scenes.
b. Open your Form, go to the Property Sheet for the Form, click the Event Tab, and click in the 'On Current' row.
c. Click the [...] button to the right of the row & select 'Code Builder'.
You are now IN the 'On Current' Event Code page and you can 'tell' Access what to do when it executes that event.
You will see something like this:

Private Sub Form_Current()

End Sub

Steps 3, 4, 5. Read the value in the PartNumber field on the Form and all the rest:
a. In the space between the two lines of Code in the Code Editor paste in this VBA Code:

Dim strPartNumber As String, strPath As String, strFile As String, strPathFile As String

strPath = \\Type\In\Your\Path\Here\

Me.PartNumber.SetFocus
strPartNumber = Me.PartNumber.Text        'This assumes your Part Number field is named 'PartNumber'.
strFile = strPartNumber & ".txt"          'Put the correct file extension in where I put ".txt"
strPathFile = strPath & strFile

If FileExists(strPathFile) Then
Me.HasFile.SetFocus                       'This assumes that your check box on the Form is named 'HasFile'
    Me.HasFile.Value = True
End If

. . . and that is pretty much it.
Substitute the correct values for the Path, and the file extension [I used .txt] and run the Form.
I'm not sure exactly what your file names look like.
If they match your PartNumbers exactly - then nothing further to do. Otherwise, give me more detail and we'll get that part taken care of as well.
If your file names don't EXACTLY match your PartNumbers then it might be a good idea to create a test file that is named for an exact PartNumber.
Maybe put it in a separate directory while testing . . . and modify the strPath value in your code.
Then - when that part works fine - we can tackle how to approach partial matches.

I hope this helps!!

[dawnpgeo]

Robeen,

I am kneeled on the floor in front of you raising and lowering my hands. THANK YOU SO MUCH for taking the time to help me. You have no idea how much I appreciate it. We have 430 part numbers to verify and it will take FOREVER to do manually.

I'm going to start wading through this project now. I might be posting back from time to time with "snags".

THANK YOU AGAIN!
Dawn

[dawnpgeo]

Hi Robeen.

I got everything pasted in and modified as necessary. My path is in red which makes me think there is a problem with it. I get an error:
Compile error:
Expected: expression

Another dumb question: How do I run it? When I click run it asks for a Macro name. ?

My file names might not match exactly.

Should I send you a copy of my database and a screenshot of my file directory?

Thanks,
Dawn

[Robeen]

First send me your code.
Then, if I can't see what the problem is, I might have to have the database.
Or, if you want - just post the database - I'll look at it.

[dawnpgeo]

My database exceeds the limit.

Here is the code:

Private Sub Form_Current()


Dim strPartNumber As String, strPath As String, strFile As String, strPathFile As String


strPath = pdf \ Drawings \ Collins


Me.PartNumber.SetFocus
strPartNumber = Me.PartNumber.Text 'This assumes your Part Number field is named 'PartNumber'.
strFile = strPartNumber & ".pdf" 'Put the correct file extension in where I put ".txt"
strPathFile = strPath & strFile


If FileExists(strPathFile) Then
Me.HasFile.SetFocus 'This assumes that your check box on the Form is named 'HasFile'
Me.HasFile.Value = True
End If




End Sub

[Robeen]

My mistake:
Change this:

strPath = pdf \ Drawings \ Collins

To:

strPath = "\\pdf\Drawings\Collins\"

Apart from the double quotes that I didn't have in my example code, you have spaces in your path and you also didn't have the "\\" at the beginning of the path string. Make sure you remove the spaces and add the "\\" at the start of the path string.

Let me know how it goes!!

[dawnpgeo]

I still have an error.

Me.PartNumber.SetFocus I has the bold text to the left highlighted when it gives the error.

Dawn

[Robeen]

Could it be that the 'Name' of your Text Box that is displaying your Part Numbers is not 'PartNumber'?
I had a comment in that line to tell you to make sure you use the correct name of the Text Box on your Form.

[dawnpgeo]

I thought that, too.

It IS named PartNumber.

Can I send you the database via email? It is too large to send here.

[dawnpgeo]

I thought that, too.

It IS named PartNumber.

Can I send you the database via email? It is too large to send here.

[dawnpgeo]

FileExists Method


This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010.
Description
Returns True if a specified file exists; False if it does not.
Syntax
object.FileExists(filespec)
The FileExists method syntax has these parts:

Part	Description
object	Required. Always the name of a FileSystemObject.
filespec	Required. The name of the file whose existence is to be determined. A complete path specification (either absolute or relative) must be provided if the file isn't expected to exist in the current folder.

So, what is the "object" in this case?????

[Robeen]

Can you zip your db and then post it?
If it is still too large after zipping, can you try going to Database Tools -> Compact & Repair Database? Make a backup copy of the Database first - just in case.
THEN zip it again.
Failing that, can you strip the db down to bare essentials. Just leae a few [30?] records in the Table . . . and then send it.
If that doesn't work - let me know.
I'm not sure why the code isn't working - but I'll try & run it here and see what happens.