I could not get my head round filesystemobjec t syntax and more importantly how to add this to db.
Can you explain in filesystemobjec texample posted, how do i define only to search for particular file types, eg .jpg files?
As u may gather, your coding level is just a wee bit above my novice level, but i grasp the principles of what, and more importantly how you are doing what you are doing. Just syntax issue now defining this particular point. Then I feel comfortable I can tweak the db format to my own uses and continue from there.
4 7863 NeoPa 32,568
Recognized Expert Moderator MVP
This question pertains to Table to include folders in a directory - Multilevel.
Hi GingerNob.
Welcome to Bytes!
I should explain, as you're new, that we like to keep separate questions in their own threads. It makes dealing with the various questions more manageable. You may want to browse the FAQ section, available at the top of every page to familiarise yourself with some of our specific rules. They are largely in line with general forum etiquette elsewhere too, so should prove useful right across the web.
Another issue you will find mentioned there is the text-type abbreviations, which are also frowned upon (outlawed), as they make communication more fragile. Communication being so critically important with such technical questions.
Right. Admin hat off & Expert hat back on now.
Give me a quick chance to check this out then I'll post something that should help you on your way.
NeoPa 32,568
Recognized Expert Moderator MVP
Let's start by including the code in the function DoFolder(), so that anyone reading this can follow. It's a little long, but doesn't all need to be read in all circumstances. - Private Function DoFolder(folParent As Folder, rs As DAO.Recordset) As String
-
Dim fol As Folder
-
Dim fil As File
-
Dim lngSize As Long, lngUnit As Long, lngID As Long
-
Dim lngFileSize As Long, lngFileUnit As Long
-
Dim strSize As String
-
-
lngUnit = 0
-
With folParent
-
Call rs.AddNew
-
rs!RootFolder = Me.txtRoot
-
rs!FullName = Replace(.ParentFolder & ("\" + .Name), ":\\", ":\")
-
rs!Drive = .Drive
-
rs!Parent = .ParentFolder
-
rs!Name = .Name
-
If .IsRootFolder Then
-
rs!Name = .Drive.VolumeName
-
Else
-
rs!Creation = .DateCreated
-
rs!Modified = .DateLastModified
-
rs!Accessed = .DateLastAccessed
-
End If
-
rs!FileType = .Type
-
lngID = rs!fileID
-
Call rs.Update
-
For Each fol In .SubFolders
-
strSize = DoFolder(folParent:=fol, rs:=rs)
-
Call AddSize(lngSize:=lngSize, _
-
lngUnit:=lngUnit, _
-
lngAdd:=Split(strSize, ",")(0), _
-
lngAddUnit:=Split(strSize, ",")(1))
-
Next fol
-
For Each fil In .Files
-
With fil
-
lngFileUnit = IIf(.Size > 2147483647, 1, 0)
-
lngFileSize = .Size / (1024 ^ lngFileUnit)
-
Call rs.AddNew
-
rs!RootFolder = Me.txtRoot
-
rs!FullName = Left(Replace(.ParentFolder & ("\" + .Name), _
-
":\\", ":\"), 255)
-
rs!Drive = .Drive
-
rs!Parent = .ParentFolder
-
rs!Name = .Name
-
rs!Creation = .DateCreated
-
rs!Modified = .DateLastModified
-
rs!Accessed = .DateLastAccessed
-
rs!FileType = Left(.Type, 50)
-
rs!Size = lngFileSize
-
rs!Unit = varUnits(lngFileUnit)
-
Call rs.Update
-
Call AddSize(lngSize, lngUnit, lngFileSize, lngFileUnit)
-
End With
-
Next fil
-
End With
-
With rs
-
Call rs.Seek(Comparison:="=", Key1:=lngID)
-
Call .Edit
-
!Size = lngSize
-
!Unit = varUnits(lngUnit)
-
Call .Update
-
End With
-
DoFolder = lngSize & "," & lngUnit
-
End Function
The lines we're mainly interested are #33 to #53, where each file that is found is processed. What we need to do, as the .Files collection of the folder object contains all available files by definition, is to add code in there so that only files that match your required format are processed. IE. All the files we don't want will be checked but ignored. To do this we add some lines just after the With fil line at #35 : - Select Case UCase(Mid(.Name, InStrRev(.Name, ".")))
-
Case ".JPG"
This could easily have been done with an If statement instead, but extending the .JPG to include multiple options is better done with Select Case.
As well as indenting the code inbetween, we also need to add the following before the End With line (originally #52) :
That sequence of code would now look like : - For Each fil In .Files
-
With fil
-
Select Case UCase(Mid(.Name, InStrRev(.Name, ".")))
-
Case ".JPG"
-
lngFileUnit = IIf(.Size > 2147483647, 1, 0)
-
lngFileSize = .Size / (1024 ^ lngFileUnit)
-
Call rs.AddNew
-
rs!RootFolder = Me.txtRoot
-
rs!FullName = Left(Replace(.ParentFolder & ("\" + .Name), _
-
":\\", ":\"), 255)
-
rs!Drive = .Drive
-
rs!Parent = .ParentFolder
-
rs!Name = .Name
-
rs!Creation = .DateCreated
-
rs!Modified = .DateLastModified
-
rs!Accessed = .DateLastAccessed
-
rs!FileType = Left(.Type, 50)
-
rs!Size = lngFileSize
-
rs!Unit = varUnits(lngFileUnit)
-
Call rs.Update
-
Call AddSize(lngSize, lngUnit, lngFileSize, lngFileUnit)
-
End Select
-
End With
-
Next fil
Hi, thanks so much.
Actually, knowing that people like yourself prefer people like me to learn rather than expect other people to provide solutions (quite right too), I did 'play' and did roughly what you suggested but with an IF. Please also note I used a slightly different variation on your test, checking for fil.type...
your code; how would it deal with a file who's name contains more than 1 '.' ?, but as you say CASE is more tidy and efficient so I will change.
If I may follow on with this thread (as it provides a perfect example) I then migrated the Database to SQL server 2008. Sadly it then failed to work.
I have spent all yesterday trying to now understand ADO and convert your example to ADO as I read I have to use ADO to connect to a SQL backend.
Is this correct? and if so, any pointers. I really have tried. I can connect to the database (confirming using the cn.state command) but I am then trying to use your existing code but compare existing records to see if the record exists for the file found.
I think it will be better if I add the complete altered code for your review; - Option Compare Database
-
Option Explicit
-
-
'Handle lack of access to certain folders
-
'Handle rounded integer division by 1024
-
-
Private Const conClear As String = _
-
"DELETE " & _
-
"FROM [%T] " & _
-
"WHERE [RootFolder]='%D'"
-
Private Const conUnits As String = _
-
"Bytes," & _
-
"KiloBytes," & _
-
"MegaBytes," & _
-
"GigaBytes," & _
-
"TeraBytes," & _
-
"PetaBytes," & _
-
"ExaBytes," & _
-
"ZettaBytes," & _
-
"YottaBytes"
-
-
Private varUnits As Variant 'Array
-
Public NumAddFiles As Integer
-
Public TotalFilesProcessed As Integer
-
Public TotalJPEGSProcessed As Integer
-
-
Sub StatusBar(Optional Msg As Variant)
-
Dim Temp As Variant
-
-
' if the Msg variable is omitted or is empty, return the control of the status bar to Access
-
-
If Not IsMissing(Msg) Then
-
If Msg <> "" Then
-
Temp = SysCmd(acSysCmdSetStatus, Msg)
-
Else
-
Temp = SysCmd(acSysCmdClearStatus)
-
End If
-
Else
-
Temp = SysCmd(acSysCmdClearStatus)
-
End If
-
End Sub
-
-
Private Sub cmdTrawl_Click()
-
Dim strTable As String, strSQL As String
-
Dim lngErrNo As Long
-
Dim fso As FileSystemObject
-
Dim fol As Folder
-
Dim cn As New ADODB.Connection
-
Dim rs As New ADODB.Recordset
-
Dim SQL As String
-
SQL = "select * from dbo.tblFileStructure"
-
-
cn.ConnectionTimeout = 300
-
' DSN connection
-
cn.Open "DSN=DHANCOCK;Trusted_Connection=yes;"
-
-
If cn.State = adStateOpen Then
-
MsgBox "Welcome to Images DB!"
-
Else
-
MsgBox "Sorry. Error in connection."
-
End If
-
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
-
-
On Error Resume Next
-
NumAddFiles = 0
-
TotalFilesProcessed = 0
-
TotalJPEGSProcessed = 0
-
-
StatusBar "Trawling: " & Me.txtRoot
-
Set fso = CreateObject("Scripting.FileSystemObject")
-
If Err > 0 Then
-
Call MsgBox("Unable to access Scripting.FileSystemObject")
-
Exit Sub
-
End If
-
-
Set fol = fso.GetFolder(FolderPath:=Me.txtRoot)
-
If Err > 0 Then
-
Call MsgBox("Invalid path - """ & Me.txtRoot & """.")
-
Exit Sub
-
End If
-
-
strTable = "tblFileStructure"
-
-
If Err > 0 Then
-
Call MsgBox("Unable to access """ & strTable & """.")
-
Exit Sub
-
End If
-
varUnits = Split(conUnits, ",")
-
On Error GoTo 0
-
Call DoFolder(folParent:=fol, rs:=rs)
-
Call rs.Close
-
Debug.Print "Added: "; NumAddFiles; " Files to DB"
-
StatusBar "Jpegs Processed: " & TotalJPEGSProcessed
-
End Sub
-
-
Private Function DoFolder(folParent As Folder, rs As ADODB.Recordset) As String
-
Dim fol As Folder
-
Dim fil As File
-
Dim lngSize As Long, lngUnit As Long, lngID As Long
-
Dim lngFileSize As Long, lngFileUnit As Long
-
Dim strSize As String
-
Dim strCriteria As String
-
Dim FoundFiles As Integer
-
-
lngUnit = 0
-
With folParent
-
For Each fol In .SubFolders
-
strSize = DoFolder(folParent:=fol, rs:=rs)
-
Call AddSize(lngSize:=lngSize, _
-
lngUnit:=lngUnit, _
-
lngAdd:=Split(strSize, ",")(0), _
-
lngAddUnit:=Split(strSize, ",")(1))
-
Next fol
-
For Each fil In .Files
-
TotalFilesProcessed = TotalFilesProcessed + 1
-
StatusBar "Trawling: " & folParent & " - " & TotalFilesProcessed & " Files Processed"
-
-
With fil
-
lngFileUnit = IIf(.Size > 2147483647, 1, 0)
-
lngFileSize = .Size / (1024 ^ lngFileUnit)
-
-
If fil.Type <> "JPEG image" Then
-
GoTo nextrec
-
Else
-
'test to see if record exisits 1st
-
TotalJPEGSProcessed = TotalJPEGSProcessed + 1
-
strCriteria = "[FullName] = '" & fil.ParentFolder + "\" + fil.Name & "'"
-
' **** THIS IS WHERE I GET STUCK, SEARCH COMES BACK WITH RESULT '-1'..commented out rs.find as it simply did not work
-
rs.MoveFirst
-
'rs.Find strCriteria, 0
-
MsgBox (rs.RecordCount)
-
MsgBox (rs!FullName)
-
If rs.RecordCount = 1 Then
-
'record exisits, dont creat a new one.
-
'Debug.Print rs.AbsolutePosition; "RECORD EXISTS"; strCriteria
-
Else
-
'record does not exist and process ADDNEW
-
Debug.Print "ADD " & strCriteria
-
Call rs.AddNew
-
rs!FullName = Left(Replace(.ParentFolder & ("\" + .Name), _
-
":\\", ":\"), 255)
-
rs!Drive = .Drive
-
rs!Parent = .ParentFolder
-
rs!Name = .Name
-
rs!Creation = .DateCreated
-
rs!Modified = .DateLastModified
-
rs!Accessed = .DateLastAccessed
-
rs!FileType = Left(.Type, 50)
-
rs!Size = lngFileSize
-
rs!Unit = varUnits(lngFileUnit)
-
Call rs.Update
-
NumAddFiles = NumAddFiles + 1
-
Call AddSize(lngSize, lngUnit, lngFileSize, lngFileUnit)
-
End If 'end of if record exists
-
End If 'end of if jpeg file
-
End With
-
nextrec:
-
Next fil
-
End With
-
With rs
-
End With
-
DoFolder = lngSize & "," & lngUnit
-
End Function
-
-
Private Sub AddSize(ByRef lngSize As Long, _
-
ByRef lngUnit As Long, _
-
ByVal lngAdd As Long, _
-
ByVal lngAddUnit As Long)
-
Dim lngWrk As Long, lngSmall As Long
-
-
If lngUnit > lngAddUnit Then
-
lngWrk = 1024 ^ (lngUnit - lngAddUnit)
-
lngSmall = lngAdd \ lngWrk
-
lngAdd = lngSmall + _
-
IIf(lngAdd - (lngSmall * lngWrk) >= (lngWrk / 2), 1, 0)
-
ElseIf lngUnit < lngAddUnit Then
-
lngWrk = 1024 ^ (lngAddUnit - lngUnit)
-
lngUnit = lngAddUnit
-
lngSmall = lngSize \ lngWrk
-
lngSize = lngSmall + _
-
IIf(lngSize - (lngSmall * lngWrk) >= (lngWrk / 2), 1, 0)
-
End If
-
On Error GoTo ErrAS
-
lngSize = lngSize + lngAdd
-
Exit Sub
-
-
ErrAS:
-
If Err = 6 Then
-
lngUnit = lngUnit + 1
-
lngSmall = lngAdd \ 1024
-
lngAdd = lngSmall + IIf(lngAdd - (lngSmall * 1024) >= 512, 1, 0)
-
lngSmall = lngSize \ 1024
-
lngSize = lngSmall + IIf(lngSize - (lngSmall * 1024) >= 512, 1, 0)
-
Resume
-
End If
-
End Sub
-
-
Private Sub cmdExit_Click()
-
Call DoCmd.Close
-
End Sub
-
-
Private Sub Form_Load()
-
txtRoot.Value = "M:\Data\My Pictures\Holiday Pics"
-
End Sub
-
-
-
To summarize intended solution; using your existing functionality, but so it is re-usable in the sense that the database does not get 'trashed' but it is a store of existing files, so if you add files to the file system, in my case drive M: testing using my holiday pictures, you re-run the script and it tests for existing records, ignores them if they exist, adds them if they do not. Reason: later I intend to add a field for 'Pic_Info' essentially picture descriptions etc, which I would not want to loose.
Please also note the functionality ALL worked fine until I ported to SQL 2008.
If I am going outside the scope of this thread and you would prefer I created a new one, please let me know.
Kindest Regards
David
NeoPa 32,568
Recognized Expert Moderator MVP
your code; how would it deal with a file who's name contains more than 1 '.' ?
It does that already by using InStrRev() instead of InStr().
If I may follow on with this thread (as it provides a perfect example) I then migrated the Database to SQL server 2008. Sadly it then failed to work.
I have spent all yesterday trying to now understand ADO and convert your example to ADO as I read I have to use ADO to connect to a SQL backend.
Is this correct? and if so, any pointers.
No. That is not correct. You can attach SQL Server tables and views to an Access database and treat them as local tables.
If this question is now resolved, you need to post your new one in a separate thread. I suggest you take these couple of short answers and work on your database first to a point where you can put a question that reflects your current understanding. The new question, in its own thread, can certainly contain a link through to this one to allow anyone reading it to understand the background.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: JeffFinnan |
last post by:
Is there a way Netscape 4.x can be forced to select a particular file extension
other than the default .html?
===============================
<form name=form1>
<input type=file name="fileN">
<input type=button onClick="setContent(1)" value="Open File">
</form>
|
by: Nigil LaVey |
last post by:
hi guys...
I am just wondering if there's any method to retrieve the File Type in c#?
say like.. in the tradditonal ASP way.. by using FileSystemObject... there's
a property called File.Type and the following will return.. for .asp file..
it returns string "Active Server Pages." in c#???
thanks
pls advise,
|
by: CLEAR-RCIC |
last post by:
In the old days, you could get a file type in ASP by doing:
dim fs, fld, f
set fs = server.createobject("Scripting.FileSystemObject")
set fld = fs.GetFolder(server.mappath(sCurrentDir))
for each f in fld.subfolders
f.type
next
|
by: Jason Lewis |
last post by:
Hi,
I'd like to find a way of narrowing a search, as you type in a field. Kind of
like when you have a big list, as you type, it narrows down the items in the
list to things that match what you have typed. Can anyone point me in the
right direction or give me a link that has an example of how to do it?
Thanks,
Jason
|
by: Julie |
last post by:
What is the *fastest* way in .NET to search large on-disk text files (100+ MB)
for a given string.
The files are unindexed and unsorted, and for the purposes of my immediate
requirements, can't be indexed/sorted.
I don't want to load the entire file into physical memory, memory-mapped files
are ok (and preferred). Speed/performance is a requirement -- the target is to
locate the string in 10 seconds or less for a 100 MB file. The...
| |
by: mike |
last post by:
Hi,
I have been playing with VB.NET/C# for getting some general properties of a
fileinfo object. However, FileInfo object does not seem to expose some of the
basic properties like TYPE that used to be available in "FileSystemObject" of
ScriptingLibrary. For example the "TYPE" property should return "Text
Document" or "Microsoft Office Document" or "Visual Studio Code File" and so
on. similar to the properties displayed by Properties...
|
by: Blade |
last post by:
Hello Friends
well this is my first post on this site, hope i get the solution for my problem
During the development of one project i am facing this problem
i use a SHGetFileInfo structure to get icons for files and folders, it work fine but in same program i use SHGetFileInfo again to get a file type as it is display in windows explorer. this time i make a vb class file and call it whenever i req. to display a file type, but it shows some...
|
by: lildiapaz |
last post by:
Hi, everyone
I'm developing a c# windows application that allows the user to encrypt any file type. i would like to encrypt the file using a powerful encrypting algorithm. I've tried to use the rinjael, triple des, but it doesn't seem to encrypt certain files at all. Can you please tell me what the problem is. I don't understand why this function doesn't work when I call it and encrypt the file. when I search for the file it still pulls up...
|
by: princymg |
last post by:
I want to search a file from server and want to copy it to the local disk.
how it is done? This is working if the file is in my hard disk itself.But not when it comes to server. If i map the server i can search.like y:\\serverfolde\\folder am tring to make an exe. different people will map to different drive. so i cant give like that.should give like @\\server\\serverfolde\\folder\\ but it is not working.
My code is<code>...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |