Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Trying to Create a Query/View in MSAccess DB using the ADOCommand Component


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 0
Mark Moss

Posts: 7
Registered: 9/20/99
Trying to Create a Query/View in MSAccess DB using the ADOCommand Component  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 8, 2014 12:18 PM
HELP

I am trying to create a Query/View in a Microsoft Access Database using the TADOCommand component.

I think I have the options set correctly, but it is giving me a "Syntax error in JOIN operation" error message.

However when I take the SQL and place it into TOAD it runs fine. Any Ideas?

ADOCommand.Type := cmdText

ADOCommand.Connection := Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\_Combined_DoDMERB_2000_Files\YearEndProcessing_Access\_Tuesday_Wednesday.mdb;';

ADOCommand.CommantTest := CREATE VIEW TEST_mem AS
SELECT ERWIN.APPLICANT.APPLICANT_ID,
ERWIN.APPLICANT.APPLICANT_SSN,
ERWIN.APPLICANT.LAST_NAME,
ERWIN.APPLICANT.FIRST_NAME,
ERWIN.APPLICANT.MIDDLE_INITIAL,
ERWIN.APPLICANT.NAME_SUFFIX,
ERWIN.APPLICATION.AGENCY_CD,
ERWIN.DIAGNOSTIC_CODES.DIAG_CD,
ERWIN.APPLICATION.CYCLE_YEAR,
ERWIN.DIAGNOSTIC_CODES.DIAG_NAME,
ERWIN.MEDICAL_DIAGNOSIS.REMOVED_BY_REMEDIATION,
ERWIN.APPLICATION_RULES_OUTPUT.RULES_SEQ_ID,
ERWIN.APPLICATION_RULES_OUTPUT.RULES_EXEC_DT
FROM ( ( ( ERWIN.APPLICANT
INNER JOIN
ERWIN.APPLICATION
ON ERWIN.APPLICANT.APPLICANT_ID =
ERWIN.APPLICATION.APPLICANT_ID)
INNER JOIN
ERWIN.MEDICAL_DIAGNOSIS
ON ERWIN.APPLICANT.APPLICANT_ID =
ERWIN.MEDICAL_DIAGNOSIS.APPLICANT_ID)
INNER JOIN
ERWIN.DIAGNOSTIC_CODES
ON ERWIN.MEDICAL_DIAGNOSIS.DIAG_CD =
ERWIN.DIAGNOSTIC_CODES.DIAG_CD)
INNER JOIN
ERWIN.APPLICATION_RULES_OUTPUT
ON ERWIN.APPLICATION.APPLICATION_ID =
ERWIN.APPLICATION_RULES_OUTPUT.APPLICATION_ID
WHERE ( (ERWIN.APPLICATION.AGENCY_CD) = 'CA')
AND ( (ERWIN.DIAGNOSTIC_CODES.DIAG_CD) in ( 'MA005', 'MA006' )
AND ( (ERWIN.APPLICATION.CYCLE_YEAR) = '2013')
AND ( (ERWIN.MEDICAL_DIAGNOSIS.REMOVED_BY_REMEDIATION) = 'F')
AND ( (ERWIN.APPLICATION_RULES_OUTPUT.RULES_SEQ_ID) =
(SELECT MIN (rules_seq_id)
FROM ERWIN.application_rules_output
WHERE ERWIN.application_rules_output.application_id =
ERWIN.application.application_id))
AND ( (ERWIN.APPLICATION_RULES_OUTPUT.RULES_EXEC_DT) >=
TO_DATE( '04/15/ 2003', 'mm/dd/yyyy' ) ) )

Any help that you can give would be greatly appreciated

Mark Moss

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02