If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
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. |
#3
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
Steve wrote:
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 [snip] Careful: http://home.tiscali.nl/arracom/whoissteve.html Phil, London |
#4
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
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 "minofifa" wrote in message ... 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. |
#5
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
"Steve" schreef in bericht m... 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 -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 10.000 pageloads... it's a shame !!) Arno R |
#6
|
|||
|
|||
Automate Access table actions based on Filesystem resources? - stevie the pimp is back
"Steve" wrote in message
m... 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 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) Steve wrote: 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 "Steve" wrote in message 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. "Steve" wrote: 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 |
#7
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
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 "minofifa" wrote in message ... 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. |
#8
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
"Steve" wrote in message
m... 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 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) Steve wrote: 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 "Steve" wrote in message 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. "Steve" wrote: 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 |
#9
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
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_he 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 minofifa wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200910/1 |
#10
|
|||
|
|||
Automate Access table actions based on Filesystem resources?
"Steve" wrote in message
m... 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. |
|
Thread Tools | |
Display Modes | |
|
|