How to create VIEW in MS Access Database using Delphi Application without installing MSAccess on PC?

Question

I want to create VIEW definitions on MS Access. I have used following CREATE VIEW Statement:

SELECT 
   MFP.FollowUpPlan_Id, 
   MFP.FollowUpPlan_Name AS PlanName, 
   DFP.Sequence_No AS SequenceNo, 
   MFS.FollowUpSchedule_Name AS ScheduleName    
FROM 
   MAS_FollowUp_Plan AS MFP, 
   DET_FollowUp_Plan AS DFP, 
   MAS_FollowUp_Schedule AS MFS
WHERE 
   (((MFP.FollowUpPlan_Id)=DFP.FollowUpPlan_Id) AND
   ((DFP.FollowUpSchedule_Id)=MFS.FollowUpSchedule_Id)) AND
   MFP.is_Deleted = FALSE AND
   DFP.is_Deleted = false     
ORDER BY 
   MFP.FollowUpPlan_Id, DFP.Sequence_No;       

but it throw an error:

Only Simple Select Queries are allowed in view.

Please Help, Thanks in Advance.

Answer 1

The issue here, as Jeroen explained, is a limitation of Access' CREATE VIEW statement. For this case, you can use CREATE PROCEDURE instead. It will create a new member of the db's QueryDefs collection --- so from the Access user interface will appear as a new named query.

The following statement worked for me using ADO from VBScript. From previous Delphi questions on here, my understanding is that Delphi can also use ADO, so I believe this should work for you, too.

CREATE PROCEDURE ViewSubstitute AS
SELECT
    MFP.FollowUpPlan_Id,
    MFP.FollowUpPlan_Name AS PlanName,
    DFP.Sequence_No AS SequenceNo,
    MFS.FollowUpSchedule_Name AS ScheduleName
FROM
    (MAS_FollowUp_Plan AS MFP
    INNER JOIN DET_FollowUp_Plan AS DFP
    ON MFP.FollowUpPlan_Id = DFP.FollowUpPlan_Id)
    INNER JOIN MAS_FollowUp_Schedule AS MFS
    ON DFP.FollowUpSchedule_Id = MFS.FollowUpSchedule_Id
WHERE
    MFP.is_Deleted=False AND DFP.is_Deleted=False
ORDER BY
    MFP.FollowUpPlan_Id,
    DFP.Sequence_No;

Answer 2

You cannot mix ORDER BY with JOIN when creating views in Access. It will get you the error "Only simple SELECT queries are allowed in VIEWS." (note the plural VIEWS)

Having multiple tables in the FROM is a kind of to JOIN.

• either remove the ORDER BY,
• or have only one table in the FROM and no JOINs.

I remember from the past (when I did more Access stuff than now) seeing this for a large query with a single table select with an ORDER BY as well.

The consensus is that you should not have ORDER BY in views anyway, so that is your best thing to do.

Another reason that you can get the same error message is if you add parameters or sub selects. Access does not like those in views either, but that is not the case in your view.