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!
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!
Code:
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