Automate Access table actions based on Filesystem resources?

[minofifa]

Hi again everyone. I really appreciate the help i've received in this forum.
As I've mentioned, I'm new to Microsoft development, but I am interested in
using Access (an the other MS Office tools) to create a small business
application.

My situation is this: The company has a list of clients, who in turn have
various resources associated with them (word docs, excel docs, pdf's etc).
The file system on the fileserver looks liked this:

- Busniesses
- Foo Inc
- someDoc.doc
- someTable.xsl
- someOtherFile.pdf
- Bar Inc
.... etc more files

Employees for my company will be creating these documents for their clients
and adding them to the file system as appropriate. I have an Access database
that has information about all of the company's clients, but I would like the
database to present these various documents as attachments. I would like to
automate the process of adding these documents to the database, based on
their presence (and location) within the filesystem I've laid out above.

For example if an employee creates a new document (thisYearsDoc.doc) for Foo
Inc, I would like a script that periodically checks this directory and adds
any new files it finds to Foo Inc's record in the database. I suppose, for
robustness, that the script should check for changes and deletions too.

Is this type of automation possible / practical? I've been looking into
automation tools for Windows / Office and it seems like my choices are VBA
Macros in Access, VBScript, JScript or PowerShell. My current understanding
is that PowerShell is the latest and greatest, and I'm thinking what I'm
trying to do couldn't be done with an in-app VBA Macro for Access (i could
very well be wrong). Out of all this technology I'm most familiar with
JScript.

Again I really appreciate the help I've received here, and any advice would
be great.

 

[Steve]

Hello,

What you describe here can be done. It will take a correctly designed set of
tables in the database and a fair amount of VBA programming (not macros). I
can do it for you. I provide help with Access, Excel and Word applications
for a very reasonable fee. If you would like my help, contact me.

Steve
(e-mail address removed)

 

[Philip Herlihy]

Hello,

What you describe here can be done. It will take a correctly designed set of
tables in the database and a fair amount of VBA programming (not macros). I
can do it for you. I provide help with Access, Excel and Word applications
for a very reasonable fee. If you would like my help, contact me.

Steve
(e-mail address removed)

[snip]

Careful:

http://home.tiscali.nl/arracom/whoissteve.html

Phil, London

 

[Gina Whipp]

Minofifa,

What you can be done in Access BUT (and there's always a but)... you will
need to use VBA coding. As to whether it is practical or not, well that is
entirely up to you. You COULD look at the Document Mangement systems
available for sale, since it sounds like that is what you want and see if
that gives you what you want. Or you COULD take this project on yourself
anf build it in Access. OR you could hire someone, PLEASE check references.
OR you could go to your local universities and look for available services
there.

If you decide to take this project on yourself, we are here to assist... for
FREE, well 99.9% of us. And if you are going to take this on, note it's not
an up and running databse in weeks...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

Hello,

What you describe here can be done. It will take a correctly designed set
of tables in the database and a fair amount of VBA programming (not
macros). I can do it for you. I provide help with Access, Excel and Word
applications for a very reasonable fee. If you would like my help, contact
me.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009

you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

Hello David,

Open your report in design view and select txtOrderID. Open properties and
go to the Data tab. Put the following expression in the Control Source
property:

=IIF([chkActive],"CW259","(CW259)")

Steve

John... Visio MVP

 

[Steve]

Minofifa,

Did you notice that none of the other responders provided any help to you?
They are only good at making defamatory and libelous statements against me.
I can help you if you give me a chance. Contact me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

Minofifa,

Did you notice that none of the other responders provided any help to you?
They are only good at making defamatory and libelous statements against
me. I can help you if you give me a chance. Contact me.

Steve
(e-mail address removed)

Considering that you are the primary cause of diverting people's attention
from the prime purpose of these newsgroups, free help, it does put your use
of logic in question.

To be libelous, the statments have to be false. Is there any specific claim
you want to refute?


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009

you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

Hello David,

Open your report in design view and select txtOrderID. Open properties and
go to the Data tab. Put the following expression in the Control Source
property:

=IIF([chkActive],"CW259","(CW259)")

Steve

John... Visio MVP

 

[KenSheridan via AccessMonster.com]

To give you an idea of how it can be done here's some VBA code I use to add
CD tracks to a database of .mp3 files burnt to folders (1 per CD) and show
them in a subform. It works by using the Dir function to step through the
files in the selected folder and then inserts rows into the Tracks table. By
virtue of a unique index on the CD_ID and Track columns the same row cannot
be inserted more than once, so the code will only add new files. To handle
deleted files or those whose names have been changed, the simplest method
would be to first delete all tracks for the CD_ID in question from the table
by executing a DELETE query, in which case all files in the folder will be
inserted again, thus incorporating any deletions or changes to the file name.

In this code the .mp3 extension is stripped off the track name when its
stored in the table, but in your case you'd want to keep the extensions.

The GetBrowse function which the code calls to select a folder can be
obtained from:

http://www.developerfusion.co.uk/show/2127


Private Sub cmdAddTracks_Click()

On Error GoTo Err_Handler

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strFolder As String
Dim strTrack As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strFolder = GetBrowse()
strFolder = Left(strFolder, Len(strFolder) - 1) & "\*.*"
strTrack = Dir(strFolder)

Me.Dirty = False

Do While Len(strTrack) > 0
strTrack = Left(strTrack, Len(strTrack) - 4)
strSQL = "INSERT INTO Tracks(CD_ID,Track) " & _
"VALUES(" & Me.CD_ID & ",""" & strTrack & """)"
strTrack = Dir()

cmd.CommandText = strSQL
cmd.Execute
Loop

Me.sfrTracks.Requery

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here

End Sub

The files are not stored as attachments, just as the paths in a text column
(the database is in Access 2002), but I'm sure the code could be adapted
easily to do so, or the files could be opened in their associated application
by means of the ShellToFile function in the following module, passing the
path to the file and the Hwnd value for the current window into the function:

Option Compare Database
Option Explicit

Declare Function ShellExecute& Lib "shell32.dll" Alias "ShellExecuteA" (ByVal
_
hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal _
lpParameters As String, ByVal lpDirectory As String, ByVal nshowcm As Long)

Sub ShellToFile(strPath As String, ByVal lngHwnd As Long)

Dim lngRetVal As Long

lngRetVal = ShellExecute(lngHwnd, "open", strPath, _
vbNullString, CurDir, 1)

If lngRetVal < 32 Then
MsgBox "Unable to open file " & strPath, vbInformation, "Warning"
End If

End Sub

Ken Sheridan
Stafford, England

 

[Keith Wilby]

Did you notice that none of the other responders provided any help to you?
They are only good at making defamatory and libelous statements against
me.

I can assure $tevie boy that *no* libellous remarks about you have been
published here by anyone. We don't need to damage your "reputation" since
you do such a bang-up job of that yourself. However, the second of your
sentences there could be deemed to contain an element of libel since all of
the "responders" (sic) to whom you refer do not fit your description one
iota. Provide FREE help or bugger off, its really that simple.

 

[Dorian]

Access can look at a file system with the FileSystemObject so there is no
need for any job to update the database. The code would just look for
whatever files you specify and could then list them or even open them for the
user.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".