dbf files in Excel with SQL

Every day i need to make a report for salesman, we have 2 dbf files from witch i automatic want to make the report in Excel. Report from 1 dbf file works perfect, but i don't know how to join 2 dbf files in VBA.

I have to following script:

Option Explicit

Sub ReadDBF()

Dim con         As Object
Dim rs          As Object
Dim DBFFolder   As String
Dim FileName    As String
Dim FileName1    As String
Dim sql         As String
Dim myValues()  As String
Dim i           As Integer
Dim j           As Integer

Application.ScreenUpdating = False

DBFFolder = ThisWorkbook.Path & "\"
FileName = "project1.dbf"
FileName1 = "project2.dbf"

On Error Resume Next

Set con = CreateObject("ADODB.connection")
If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection error"
    Exit Sub
End If
On Error GoTo 0

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFFolder & ";Extended Properties=dBASE IV;"

sql = "SELECT project_id, COUNT(*) AS total, salesman, MAX(date) AS max_date, projectname FROM " & FileName & FileName1 & " where DateValue(datumtijd) = Date() and FileName.project_id = FileName1.project_id " & "group by project_id, salesman"


On Error Resume Next
Set rs = CreateObject("ADODB.recordset")
If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection error"
    Exit Sub
End If
On Error GoTo 0

rs.CursorLocation = 3
rs.CursorType = 1

 rs.Open sql, con


ReDim myValues(rs.RecordCount, 20)


i = 1
If Not (rs.EOF And rs.BOF) Then
    'Go to the first record.
    rs.MoveFirst
    Do Until rs.EOF = True
        myValues(i, 1) = rs!project_id
        myValues(i, 2) = rs!salesman
        myValues(i, 3) = rs!Total
        myValues(i, 4) = rs!max_date
        myValues(i, 5) = rs!project
        rs.MoveNext
        i = i + 1
    Loop
Else
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
    Application.ScreenUpdating = True
    MsgBox "There are no records in the recordset!", vbCritical, "No Records"
    Exit Sub
End If

Sheet1.Activate
For i = 1 To UBound(myValues)
    For j = 1 To 4
    Cells(i + 1, j) = myValues(i, j)
    Next j
Next i
rs.Close
con.Close

Set rs = Nothing
Set con = Nothing

Columns("A:D").EntireColumn.AutoFit

Application.ScreenUpdating = True

MsgBox "The values were read from recordset successfully!", vbInformation, "Done"

End Sub

The query doesn't work just by adding the two tables together as the from source. What is it you are trying to actually get? Also, you should never concatenate strings to build a query. They should always be parameterized.

Now, that said, your syntax is simply incorrect. Your variables are referred to as "FileName" and "FileName1", but the underlying TABLES you are querying from are "Project1" and "Project2" respectively. You should learn about aliases to help simplify queries too, and learn proper JOIN syntax.

By listing the tables one after the other with no comma will cause it to fail. Here is a more accurate syntax and formatted for readability. Then I have edited your original query to match the context. You should also always qualify the field names from the respective table so others trying to help know where things are coming from. In the sample below, I have only GUESSED at which table has which columns by using the alias "P1." and "P2." respectively. You will probably need to change them. Also, since "Date" could be interpreted as a reserved word, I have wrapped it in [], but may need to be changed to tick characters (next to number 1) ``

select
      P1.Project_ID,
      COUNT(*) as Total,
      P1.SalesMan,
      MAX( P2.[Date] ) as Max_Date,
      P1.ProjectName
   from
      Project1 P1
         JOIN Project2 P2
            on P1.Project_ID = P2.Project_ID
   where
      DateValue( P2.datumtijd ) = date()
   group by
      P1.Project_ID,
      P1.SalesMan

The JOIN clause identifies the relation BETWEEN the two tables on respective columns. The WHERE clause is additional criteria you are looking for.

sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _
            "MAX(date) AS max_date, projectname " & _
         " FROM " & FileName & " P1 "  & _
             " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _
         " where DateValue(datumtijd) = Date() " & _
         " group by project_id, salesman"