VB Forum / Database Access / October 2004

Public Sub AddFiles(FilePath As String)

Dim arFileList() As DFiles
Dim I As Integer, x As Integer
Dim Cnn As Connection
Dim Rst As Recordset
Dim f As Integer, d As Integer
Dim arTypes() As String
Dim strSQL As String

f = 0
d = 0

Set Cnn = CreateConnection(ADO_MSAccess, App.Path & "\webfiles.mdb")
Cnn.Open
Set Rst = New ADODB.Recordset
Rst.LockType = adLockOptimistic
Rst.CursorType = adOpenKeyset
Rst.CursorLocation = adUseClient
arTypes = Split(FileTypes, "|")
For I = 0 To UBound(arTypes)
   ReDim arFileList(0)
   FindFilesAPI FilePath, "*" & arTypes(I), f, d, arFileList
   For x = 0 To UBound(arFileList)
       If arFileList(x).Path <> "" Then
       Rst.Open "select * from tblfiles where path='" & arFileList(x).Path & Replace(arFileList(x).Name, "'", "''") & "'", Cnn, adOpenDynamic, adLockBatchOptimistic
       If Rst.EOF Then
           With arFileList(x)
               With arFileList(x)
                   Cnn.Execute "insert into tblfiles (filename,path,type,size,created,modified) values('" _
                   & .Name & "','" & .Path & "\" & Replace(.Name, "'", "''") & "','" & .FileType & "'," & .Size & ",#" & .CreationDate & "#,#" & .ModDate & "#)"
               End With
           End With
       Else
           If arFileList(x).Size <> Rst!Size Then
               Cnn.Execute "update tblfiles set size=" & CLng(arFileList(x).Size) & " where songid=" & Rst!songid
           ElseIf arFileList.ModDate <> Rst!modified Then
               Cnn.Execute "update tblfiles set modified=#" & arFileList(x).ModDate & "# where fileid=" & Rst!fileid
           End If
       End If
       Rst.Close
       End If
   Next x
Next I

On Error Resume Next
Rst.Close
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
Set Ctl = Nothing

End Sub

Table Def

FileName        Text
Path               Text
Type               Text
Size               Long
Created               Date
Modified        Date

Failing Query Statement

update tblfiles set size=6590508 where fileid=222