Getting date created

[joltremari]

I have been trying to use the code below to get the creation date from a list of files and by matching the name of the file to a record in an access database putting the creation date for the match.

It is not working. I am wondering if instead of using a vb form to do this, if it would be easier/better to do this through code in access.

Any Help?

PLEASE!?!

Thanks,

JO


Current code:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim filesys As FileSystemObject
Dim fol As Folder
Dim fil As File
Dim ssql As String

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
Set filesys = New FileSystemObject

With db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & "\db1.mdb"
.Open
End With

rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
rs.ActiveConnection = db

ssql = "Select * From WOINFO"

rs.Open ssql

Set fol = filesys.GetFolder("d:\DCtest\")

For Each fil In fol.Files
rs.MoveFirst
Do Until rs.EOF
'If fil.Name = rs("WONumber") Then
'rs("ScanDate") = fil.DateCreated
'rs.Update
'End If
List1.AddItem (fil.DateCreated)
rs.MoveNext
Loop
Next fil


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Set filesys = Nothing
Set fil = Nothing
Set fol = Nothing

[Surgeon]

You might try this instead :

Dim db As ADODB.Connection
Dim filesys As FileSystemObject
Dim fol As Folder
Dim fil As File
Dim ssql As String

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
Set filesys = New FileSystemObject

With db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & "\db1.mdb"
.Open
End With

rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
rs.ActiveConnection = db

'you get each file and search its name within the database
' if you find it, then you update the WONumber field's value.
Set fol = filesys.GetFolder("d:\DCtest\")
For Each fil In fol.Files
    db.Execute "UPDATE WOINFO SET ScanDate = '" & fil.DateCreated & "' Where (WONumber = """ & fil.Name & """);"
Next fil

db.Close
Set db = Nothing
Set filesys = Nothing
Set fil = Nothing
Set fol = Nothing

But this would only work if your ScanDate field is a String Type field. If it's datatype is Date, then it won't work. If this is the case, plz give me a shout and I'll give you the right directions.

Regards,
Surgeon

[joltremari]

Thank you!!

I got sent out of town unexpectedly so I won't be able to try it until Tuesday. I certainly do appreciate it and I will let you know!!

Thanks again,

JO