Question VBA SQL statement - Quick Win (probably)

[MOTOWN44]

Afternoon

This is probably a quick win for some of you guys J but im having really trouble getting my SQL statement to work!

Public Sub CheckNewDir()
Dim mysrcdir As String
 
Dim myfilename As String
Dim fso As Object
Dim mydatecreated As Date
Dim mydatemodified As Date
 
mysrcdir = "H:\"
 
Set fs = Application.FileSearch
 
DoCmd.SetWarnings False
 
With fs
 
  .LookIn = mysrcdir
  .SearchSubFolders = False
  .Filename = "*.*"
  .MatchTextExactly = False
 
  If .Execute > 0 Then
 
  Debug.Print .foundfiles.Count
      For i = 1 To .foundfiles.Count
          myfilepath = .foundfiles(i)
          myfilename = Mid(myfilepath, Len(mysrcdir) + 1)
 
          Set fso = CreateObject("Scripting.FileSystemObject")
 
          mypath = fso.getfile(myfilepath).Path
          myname = fso.getfile(myfilepath).Name
          mytype = fso.getfile(myfilepath).Type
          mydatemodified = fso.getfile(myfilepath).Datelastmodified
 
          Debug.Print mypath, myname, mytype, mydatemodified
 
DoCmd.RunSQL ("INSERT INTO Files (FPath, FName, FType, FLastMod) VALUES(mypath, myname, mytype, mydatemodified )")
 
          Set fso = Nothing
 
     Next i
 
  End If
 
End With
 
DoCmd.SetWarnings True
 
End Sub

As you can see im wanting to insert the values of the myname, mypath etc that I defined earlier into the required column in my table called “Files” but there's a problem with the values part of the SQL string

I've tried every combination I can think of comma, double and single quote I can think of but the best result I've got is it actually sticking “myname” and “mytype” in the columns rather than the info they are meant to represent!

Im also not sure if I need to be using a SELECT * somewhere?? But I wasn’t really sure since there isn’t a table/query to select from as im running the code from the immediate window because the module is getting the info for the table from the files in the selected target folder.

Thanks in advance

 

[JANR]

Since you are trying to refrence variables to insert into your table, they must be outside your stringexpression.

Try and replace Docmd.RunSQL line with this:

Currentdb.Execute " Insert Into Files (FPath, FName, FType, FlastMod) " & _
                  " Values ('" & mypath & "','" & myname & "','" & mytype & "','" & mydatemodified & "')", dbFailOnError

I assume all variables are strings, if FlastMod is a datefield then perhaps this:

Currentdb.Execute " Insert Into Files (FPath, FName, FType, FlastMod) " & _
                  " Values ('" & mypath & "','" & myname & "','" & mytype & "','" & Format(mydatemodified,"\#mm\/dd\/yyyy\#) & ")", dbFailOnError

JR

 

[MOTOWN44]

your first suggestion worked perfectly thank you

Matthew