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

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 ASSELECT    MFP.FollowUpPlan_Id,    MFP.FollowUpPlan_Name AS PlanName,    DFP.Sequence_No AS SequenceNo,    MFS.FollowUpSchedule_Name AS ScheduleNameFROM    (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_IdWHERE    MFP.is_Deleted=False AND DFP.is_Deleted=FalseORDER BY    MFP.FollowUpPlan_Id,    DFP.Sequence_No;

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.

Declare variable olevarCatalog ,cmd as OleVariant in Delphi, Uses ComObj

olevarCatalog := CreateOleObject('ADOX.Catalog');olevarCatalog.create(YourConnectionString); //This Will create MDB file.// Using ADO Query(CREATE TABLE TABLEName....) add the required Tables.// To Insert View Definition on MDB file.cmd := CreateOleObject('ADODB.Command');cmd.CommandType := cmdText;cmd.CommandText := 'ANY Kind of SELECT Query(JOIN, OrderBy is also allowed)';olevarCatalog.Views.Append('Name of View',cmd);cmd := null;

This is a best way to Create MS ACCESS File(.MDB) and VIEWs using Delphi.