a

[ranshe]

To the best of my knowledge, my co-worker and I have our PC's and VB environments set up the same.
The following Sub runs fine on my PC but when I send it to my co-worker and he loads it onto his PC in VB it will not run but gets the error message:

" Syntax error in the FROM clause"

and the debug highlights the query statement.

dbImportDB.Execute "INSERT INTO " & strCampName & _
"(" & strFieldListTO & ") " & _
"SELECT " & strFieldListFROM & _
"FROM CustomerMaster " & _
"IN " & strFromPath & _
"WHERE " & strSelectCriteria


Niether one of us know how to solve this problem.
We rebuilt the ODBC connections to no avail.
The References have been set on both PC's. Though, I am not completely sure if they are the same but I know he is able to write complex query statements on his PC without any problems.

I would appreciate any new ideas!
Thanks!

Private Sub cmdCreate_Click()
    'This sub reads from database "CampDump"
    'table "CustomerMaster" and writes to
    'the database "TestDB" table "tblCourtesyCall_0705".
    'Both DB's are via ODBC
    
    Dim dbImportDB As New ADODB.Connection
    Dim dbExportDB As New ADODB.Connection
    Dim strSelectCriteria As String
    Dim strFromPath As String
    Dim strCampName As String
    Dim strstrFromPath As String
    Dim strFieldListTO As String
    Dim strFieldListFROM As String
                 
            [COLOR=green]'Open databases[/Color]
    dbImportDB.Open "TestDB"
    dbExportDB.Open "CampDump"

           [COLOR=green]'Load String vairiables[/Color]
    strCampName = "tblCourtesyCall_" & Format(Now, "mmyy ")
    
    strFieldListTO = "X, Y, CMAddress2, CMCity, CMState, " & _
              "CMZipCode, CMPhone, CMAccountStartDate "
              
    strFieldListFROM = "CMOurAcctID, CMCustomerName,  " & _
                       "CMAddress2, CMCity, CMState, " & _
                       "CMZipCode, CMPhone,  " & _
                       "CMAccountStartDate "
    strFromPath = " '" & dbExportDB.DefaultDatabase & ".mdb' "
    
    strSelectCriteria = "CMAccountTermDate Is Null " & _
                        "AND (DatePart('m',Now())- " & _
                        "DatePart('m',CMAccountStartDate) " & _
                        "=1) AND (DatePart('yyyy',Now()) " & _
                        "=DatePart('yyyy',CMAccountStartDate))"
              
    dbImportDB.Execute "INSERT INTO " & strCampName & _
                                    "(" & strFieldListTO & ") " & _
                       "SELECT " & strFieldListFROM & _
                       "FROM CustomerMaster " & _
                       "IN " & strFromPath & _
                       "WHERE " & strSelectCriteria
            
    MsgBox ("Campaign Database has been created")
    
    [COLOR=green]'close database(s)[/Color]
    
    dbImportDB.Close
    dbExportDB.Close
    
End Sub

 

[gmmastros]

Wow, that would be very frustrating. I don't notice anything that would be wrong. my suggestion would be to check the sql string you are trying to execute.

Debug.print "INSERT INTO " & strCampName & _
                                    "(" & strFieldListTO & ") " & _
                       "SELECT " & strFieldListFROM & _
                       "FROM CustomerMaster " & _
                       "IN " & strFromPath & _
                       "WHERE " & strSelectCriteria

You may also want to look at the connection strings.

After you set the ConnectionString property and open the Connection object, the provider may alter the contents of the property, for example, by mapping the ADO-defined argument names to their provider equivalents.

so, change the code to add the following lines.

    dbImportDB.Open "TestDB"
    dbExportDB.Open "CampDump"
    [red]Debug.Print dbImportDB.ConnectionString[/red]
    [red]Debug.Print dbExportDB.ConnectionString[/red]

Run the code on both computers and look for any differences.

Unfortunately, that's about the only advice I can give.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom