help with file handling (file path and name)

[boggle-eyed]

hi all, hope someone can help/advise me.
i designing my own db to assist me track referrence material, emails,
reports created at work, i originally had my tables just store these as
attachments but now realise that my db is going to balloon very quickely and
so started my search for a method of saving just there address.
Dev Ashish + Terry Kreft's apllication "explorer"
(http://www.mvps.org/access/downloads/explorer.zip) seems brilliant for what
i want however can some one advise me how
1. i can invoke this from numerous different forms within my db, once
invoked save the selected file path and name to one table. (t_evidence)
2. how if the above is possible how i can reference the "evidence" back to
the form it was creted from,
am very new to access and the code in the application is well over my head.
my thoughts add a save control button to the form on the application, which
will be the record source of my new table. however what exactly am i
saving??? if this is in deed the correct way to go.
thank you for any help advise you can offer in advance.

 

[Hans Up]

Dev Ashish + Terry Kreft's apllication "explorer"
(http://www.mvps.org/access/downloads/explorer.zip) seems brilliant for what
i want however can some one advise me how
1. i can invoke this from numerous different forms within my db, once
invoked save the selected file path and name to one table. (t_evidence)

It's fairly easy to modify the sample form to do what I think you want.
Maybe you can start from there and figure out how to do it for your
other numerous different forms.

2. how if the above is possible how i can reference the "evidence" back to
the form it was creted from,
am very new to access and the code in the application is well over my head.
my thoughts add a save control button to the form on the application, which
will be the record source of my new table. however what exactly am i
saving??? if this is in deed the correct way to go.

Rather than adding a command button, I decided to change the
double-click event code (in the Form_frmExplorer code module) for the
files listbox control.

Private Sub lbxFiles_DblClick(Cancel As Integer)
Dim varRet
Dim stPath As String
Dim strSql
Dim strMsg As String
Dim db As DAO.Database

On Error GoTo ErrorHandler

If mstPath = vbNullString Then
stPath = Left$(Me!lbxFolders, Len(Me!lbxFolders) - 1)
Else
stPath = mstPath & "\" & Me!lbxFolders
End If

strSql = "INSERT INTO t_evidence (" & vbNewLine & _
"doc_file," & vbNewLine & _
"doc_path," & vbNewLine & _
"form_name)" & vbNewLine & _
"VALUES (" & vbNewLine & _
"'" & Replace(Me!lbxFiles, "'", "''") & "'," & vbNewLine & _
"'" & Replace(stPath, "'", "''") & "'," & vbNewLine & _
"'" & Replace(Me.Name, "'", "''") & "');"
' Debug.Print strSql
Set db = CurrentDb
db.Execute strSql, dbFailOnError
strMsg = db.RecordsAffected & " document stored"
MsgBox strMsg
varRet = (db.RecordsAffected = 1)

ExitHere:
Set db = Nothing
On Error GoTo 0
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3022 'already in table
strMsg = "Document was stored previously. " & _
"Not stored again." & vbNewLine & vbNewLine & _
"(" & stPath & Chr(92) & Me!lbxFiles & ")"
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure lbxFiles_DblClick"
End Select

MsgBox strMsg
varRet = True
GoTo ExitHere
End Sub

Here is the code I used to create the t_evidence table. You can save
this code in a new module, position the cursor within the body of the
subroutine, and press the F5 key to run it ... which will create the
table. If you already have a table named t_evidence, rename it before
running the code. Or, if you prefer to create t_evidence manually, the
code above should still work if you have 3 text fields name doc_file,
doc_path, form_name. My version of the table includes a unique index on
doc_path and doc_file to prevent duplicate entries. But the code should
work if you don't include the index. Of couse then you risk storing
duplicates.

Public Sub CreateEvidenceTable()
Const cstrTable As String = "t_evidence"
Dim strSql As String

strSql = "CREATE TABLE t_evidence (" & vbNewLine & _
"doc_id COUNTER CONSTRAINT pkey PRIMARY KEY," & vbNewLine & _
"doc_file TEXT(255) NOT NULL," & vbNewLine & _
"doc_path TEXT(255) NOT NULL," & vbNewLine & _
"form_name TEXT(50)," & vbNewLine & _
"date_added DATETIME DEFAULT Now()," & vbNewLine & _
"CONSTRAINT uniqfullpath UNIQUE(doc_path, doc_file)" & vbNewLine & _
");"
Debug.Print strSql
CurrentProject.Connection.Execute strSql
End Sub

 

[boggle-eyed]

thank you for your help, speedy responce and useful reply it works better
than i imagined.

 

[Hans Up]

thank you for your help, speedy responce and useful reply it works better
than i imagined.

That's good news. Thanks for your gracious reply.

I coded that fast. Looking at it today, I made some changes.

In lbxFiles_DblClick, I changed Dim strSql to Dim strSql As String.

Also I didn't see what purpose the varRet variable served in that sub,
so I commented out the 3 lines which referred to it, and the thing seems
to work fine.

In the CreateEvidenceTable procedure, I changed the beginning of the
strSql assignment to:

strSql = "CREATE TABLE " & cstrTable & " (" & vbNewLine & _

My intention had been to use the constant to hold the table name and
build the CREATE TABLE statement using the constant. But then I forgot.

I think there could be a bigger issue, but I don't know whether it will
be a concern in your case.

Storing the folder in a table is brittle. If you ever relocate your
documents collection (or a subset of the collection), you will have to
update your table with the new doc_path values. However, avoiding that
brittleness would require more extensive changes to what you have
working now. And I don't know if it would be worth the effort for you.

Good luck,
Hans

 

[boggle-eyed]

Thanks once again,

I will change the code in the two places that you have highlighted, however
as for the VarRet 3 lines i will leave them in place, dont want to risk
deleting the wrong lines.

yes i thought about what would happen if i changed the documents location,
because no one else will be using my db and whilst turning out to be a bigger
project than imagined doubt that it will get big enough to involve changing
my file structure. That said i might require a back-up on dvd/cd disk any
ideas?? if this is a complecated issue, like you mentioned might not be worth
the effort, will be able to back them up from within their present locations.

once again thank you for your support, hard work and help so far.

 

[Hans Up]

Thanks once again,

I will change the code in the two places that you have highlighted, however
as for the VarRet 3 lines i will leave them in place, dont want to risk
deleting the wrong lines.

I think we're talking about two different things. Say you have a line of
code like this:

MsgBox "Hello World"

You can disable that line of code by placing a single quote before it.

'MsgBox "Hello World"

So the line could still remain in your code, but just not do anything.
Basically VBA treats anything on the same line after a single quote as a
comment, and ignores it. Unless the single quote is inside a double
quoted sting such as this one:

Debug.Print "five o'clock"

I hope you won't feel I'm talking down to you, but you said you were
very new to Access and I got the impression you didn't understand what I
meant by "comment out".

yes i thought about what would happen if i changed the documents location,
because no one else will be using my db and whilst turning out to be a bigger
project than imagined doubt that it will get big enough to involve changing
my file structure.

OK, if it's only you, you got less to worry about.

That said i might require a back-up on dvd/cd disk any
ideas?? if this is a complecated issue, like you mentioned might not be worth
the effort, will be able to back them up from within their present locations.

I think as long as your restore to the same location you should be fine.
If you're instead asking for tips on the best techniques to back up
to optical disks, I don't know because I don't use them. I just back up
to a file share on another computer.

once again thank you for your support, hard work and help so far.

You're welcome. Best wishes for success with your project.

Hans

 

[boggle-eyed]

once again i am indebted to you.

no i did not feel like you were talking down to me, you were right i total
misunderstood that you meant by commenting out, thankfully i didnt delete the
lines...few.

when i said new to access it has only been this year that i have been
tinkering with it, for the last three months i have been developing my
project and it is turning into a labour of love that i hope will enable me to
process my work grenerated paperwork faster allowing me to concentrate more
upon the actual work.

had an idea, wish i had had it sooner and asked originally but hope you will
be able to assist me still without changing the code you have already
suppyied for my project.

i have three forms at present (f_knowledge, f_council, f_developement) that
i am going to launch the explorer application from. if i add a new column to
there relevant tables say "knowledgeevedence, councilevedence and
developementevidence."

1. is it possible that i could progmatically load the index number of the
relevant document in t_evidence (the one we created). into the number columns
of the table whos form generated the document.

2. could this stored referrence index number be used as an on click or
double click to load the original document.

3. one evidential document might be used to prove statement in two or all of
the tables could, provided i opened the document three times from "explorer"
once from each form, the three tables hold identicle links to the same
document.

realise that storing data more than once is not ideal.

hope this doesnot cause to much of a head ache.

thanks in advance.