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

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
0
Mark
7/8/2014 7:18:21 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

0 Replies
400 Views

Similar Articles

[PageSpeed] 3

Reply:

Similar Artilces:

Error trying to use a datawindow created using CREATE
Should it be possible to do the following in 5.0.04: u_dw ldw integer li_Result ldw = create u_dw ldw.dataobject = "dw_test" li_Result = ldw.of_SetTransObject( SQLCA ) li_Result = ldw.retrieve() destroy ldw The of_SetTransobject fails with a -1. The following also fails: datawindow ldw integer li_Result ldw = create datawindow ldw.dataobject = "dw_test" li_Result = ldw.SetTransObject( SQLCA ) li_Result = ldw.retrieve() destroy ldw The following works: n_dw lds integer li_Result lds= create n_dw lds.dataobject = "dw_test" li_...

Creating a view using two Db and odbc to connect.
I'm need help to create a view using information that is stored in two databases (SSA 5.5.04 Build #1867 & a pervasive db ). I'm going to use ODBC to connect. I don't see anything in the help file that will help me accomplish this task. I also have ASA 7.0 . Can this be done? Thanks in advance for your help. Views are database specific so you can only create one across databases if one of your dbs supports that feature. I don't believe ASA has that capability though ASE and Oracle provide that feature. That's a question for the ASA news group. For P...

Can I use a components package created in C++ inside a Delphi form?
Hello, I recently created a component package, containing many controls, that I'm commonly using for my forms and projects. This package was written in C++, using RAD Studio XE7 C++ Builder. All the controls inside this package inherit from some VCL components, written in Delphi, and cohabitation has never been a problem. Until now, I always used these components in C++ projects, and in this context, I never experimented problems with that. But now, I need to write a Delphi form. There is no alternative, I MUST write a Delphi form. So, I created a new Delphi form project, and I notic...

Making SQL queries using OLE DB instead of ADO
Hi, I usualy make my SQL queries using the traditional ADO statements, just like this:Dim sqlCon as New SqlConnectionDim sqlCmd as New SqlCommand sqlCon.ConnectionString="Server=.;uid=sa;pwd=sasa;database=mydatabase" sqlCmd.Connection = sqlConsqlCmd.CommandText = "Select id, user from table_users"sqlCmd.Parameters.Add(New SqlParameter("@userName",userNameBox.Text) trysqlCon.Open()slqCmd.ExecuteNonQuery()Catch ex as ExceptionEnd TrysqlCon.Close()And it seems that, altought this method is correct and prevents SQL Injection (as it uses parametrization), it is slower than other co...

How do I create views that uses other views in Data Architect ?
How do I create views that are based on other views within Data Architect? Thanks, Buddy Mcalister Arthur D. Little, Inc mcalister@scra.org Well, I can't get it to list the views in the dialog boxes the way I would expect it to work, so I think you have a bug (or at least an enhancement). What you can do for now is use the SQL button on the View properties dialog box. In there, you can enter any SQL statements. Join views and tables or whatever you want to do. Let me know if this doesn't work. I'll submit the issue about adding "Views" to the lists of ...

Composite component, using frame or create component?
I need to create a custom 'edit control'. It has a custom bitmapped background. It will have two labels, one for a caption, and one for the editable content (a number). When clicking on the editable content (the label), another form will popup to allow the user to enter the actual number from a number pad (its a touch screen application). It seems that I could create this using a frame? Or can one create a custom vcl component to do this? Any suggestions on what what makes sense design wise? tk Hello, totte karlsson wrote: > Any suggestions on what what...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit and .Post), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuery...

C# method using ado.net to create an html table with just the db table name
 I was wondering whether there was a simple way using ADO.NET in C# to have a method that has the following header: public static String viewTable(String tablename) that outputs the data either in: <table> <tr> <th>Headings</th> // Repeat for each column in table </tr> <tr> <td>Values</td> // Repeat for each record (and for each column) in table </tr> </table> OR <table> <tr> // Repeat these 4 lines for each column & display values for the first record <td>Heading...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server [Edit]
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit, .Post etc), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuer...

How do I create a link in a list view to query the row for guid and use the guid to concatenate to a link so when you click it opens it a page
I want hyper link in a list view that queries the row for guid and uses it link to open the page   Hi Devinthedude, You can bind guid into NavigateUrl property in HyperLink control in ListView: <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "nextpage.aspx?guid=" + Eval("guid") %>' Text="Link"></asp:HyperLink> Thanks,Qin Dian TangMicrosoft Online Community SupportPlease remember to mark the replies as answers if they help and unmark them if they provide no help. Thanks for the reply yeah t...

"activex component can t create object" error when trying to use .net dll in VB6
Hi everyone!I've writen a small .net class library to communicate with my web service to fetch some data from the database, etc,I have selected  "register for com interop" option, I added the reference to the vb6 project and everything works fine,but, when I deploy my whole project in another pc, I get  "activex component can t create object" error!I use a custom setup (using Inno setup) and register the type library file (tlb),which is copied to the vb6 exe's directory,I searched the registry and found the same registration entries in both pcs,so, what&...

Using parameters in querying an Access DB and using Datasets
Once a user has logged into my application, they are directed to their admin page.  On there, it will display if they have any new messages.  I can assign the login name to the variable "yourname". The messages are stored in the unimaginatively titled 'messages' table.  The msgto and msgfrom fields are linked to the 'logins' table where the names are stored.  So far, I have created a tableadapter and have the query that will list the required fields for all the messages that are unread: SELECT     m...

ASA7 ADO & Delphi ADO ?? Anyone is using it?
Any comments? Is it working? Any Problems? Thanks Hi, PR <noemail@no.com> schrieb in im Newsbeitrag: BoTThJaq$GA.89@forums.sybase.com... > Any comments? Is it working? Any Problems? I tried to connect to ASA7 via the new OLE DB - Provider and ADOExpress from Delphi 5.01. No chance. Only the error message 'Invalid Enum Value'. About the new provider I haven't heard anything good until now... Regards Frank Frank, I've experienced the same problem with the ASA 7 OLEDB Provider. I'm not sure if this is a bug in the AsaProvider or Delp...

Database Query and other components inside complex Delphi component
Hi! I am trying to develop Delphi DB component for displaying and editing customer data. Many tables (like, carts) in my database references customer using foreign key (like carts.fk_customer) and users want to see a lot of data about customer in diferent forms. So - I am planning to make the component (apparently - derived from TPanel) with the usual properties - DataSource, DataField (for FK_CUSTOMER) and this component should process the changes of its data field (usually fk_customer) by reading addional d ata from the query (component has DataSource property, so, this is possible to fi...

Web resources about - Trying to Create a Query/View in MSAccess DB using the ADOCommand Component - embarcadero.delphi.ado

Resources last updated: 12/25/2015 6:03:42 AM