W
Winget
I have a query in Excel 2003 to run a query to Access database. If I manually
run the query it works, but my macro to run it bombs out with Compile Error-
Syntax Error.
It is a complicated query linking several tables, but why would it work to
run manual and not work in VBA?
Here it is in case anyone wants to slug thur it- more after, but it stops
where the double break appears below
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=CustomDietResearch;Description=Custom Diet Research
Database;UID=CustomDietResearch;APP=Microsoft Office 2003;WSID=378PC11;" _
),
Array("DATABASE=Teklad_Custom_Diet_Research;Trusted_Connection=Yes"))
.CommandText = Array( _
"SELECT Diet.TDNumber, Diet.DietName, Diet.UserID,
CustomerInformation.CompanyName, CustomerInformation.ContactName,
CustomerInformation.PhoneNumber, CustomerInformation.EmailAddress,
DietIndexingInfor" _
, _
"mation.Species, DietIndexingInformation.ModificationDiet,
DietIndexingInformation.KeyFeature2, DietIndexingInformation.KeyFeature3,
DietIndexingInformation.KeyFeature4, DietCustomerServiceInfo.Special" _
, _
"Packaging, DietCustomerServiceInfo.FormDescription,
DietCustomerServiceInfo.Irradiated,
DietCustomerServiceInfo.CustomerInfo"&chr(13)&""&chr(10)&"FROM
Teklad_Custom_Diet_Research.CustomDietResearch.CustomerInformation Cu" _
, _
"stomerInformation,
Teklad_Custom_Diet_Research.CustomDietResearch.Diet Diet,
Teklad_Custom_Diet_Research.CustomDietResearch.DietCustomerServiceInfo
DietCustomerServiceInfo, Teklad_Custom_Diet_Research" _
, _
".CustomDietResearch.DietIndexingInformation
DietIndexingInformation"&chr(13)&""&chr(10)&"WHERE
CustomerInformation.TDNumber = Diet.TDNumber AND Diet.TDNumber =
DietCustomerServiceInfo.TDNumber AND DietCustomerServiceInfo" _
,)
run the query it works, but my macro to run it bombs out with Compile Error-
Syntax Error.
It is a complicated query linking several tables, but why would it work to
run manual and not work in VBA?
Here it is in case anyone wants to slug thur it- more after, but it stops
where the double break appears below
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=CustomDietResearch;Description=Custom Diet Research
Database;UID=CustomDietResearch;APP=Microsoft Office 2003;WSID=378PC11;" _
),
Array("DATABASE=Teklad_Custom_Diet_Research;Trusted_Connection=Yes"))
.CommandText = Array( _
"SELECT Diet.TDNumber, Diet.DietName, Diet.UserID,
CustomerInformation.CompanyName, CustomerInformation.ContactName,
CustomerInformation.PhoneNumber, CustomerInformation.EmailAddress,
DietIndexingInfor" _
, _
"mation.Species, DietIndexingInformation.ModificationDiet,
DietIndexingInformation.KeyFeature2, DietIndexingInformation.KeyFeature3,
DietIndexingInformation.KeyFeature4, DietCustomerServiceInfo.Special" _
, _
"Packaging, DietCustomerServiceInfo.FormDescription,
DietCustomerServiceInfo.Irradiated,
DietCustomerServiceInfo.CustomerInfo"&chr(13)&""&chr(10)&"FROM
Teklad_Custom_Diet_Research.CustomDietResearch.CustomerInformation Cu" _
, _
"stomerInformation,
Teklad_Custom_Diet_Research.CustomDietResearch.Diet Diet,
Teklad_Custom_Diet_Research.CustomDietResearch.DietCustomerServiceInfo
DietCustomerServiceInfo, Teklad_Custom_Diet_Research" _
, _
".CustomDietResearch.DietIndexingInformation
DietIndexingInformation"&chr(13)&""&chr(10)&"WHERE
CustomerInformation.TDNumber = Diet.TDNumber AND Diet.TDNumber =
DietCustomerServiceInfo.TDNumber AND DietCustomerServiceInfo" _
,)