Recent

Author Topic: TODBCConnection with MS Access "Create View" Error  (Read 7701 times)

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
TODBCConnection with MS Access "Create View" Error
« on: July 03, 2015, 09:24:17 am »
Hi Guys,
i can not create a View at runtime in an Access database, connected with TODBCConnection.

This is the test code:
Code: [Select]
Path := IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName));
 Path := Path + 'RWDBase.MDB';

 Conn.Driver := 'Microsoft Access Driver (*.mdb)';
 Conn.Params.Add('DBQ=' + Path);

 Conn.Connected := True;

 Conn.ExecuteDirect('Create VIEW VTest As Select * From TableFeedback');

ExecuteDirect return me this exception:
Could not execute statement. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: 42000; NativeError: 55; Message: [Microsoft][Driver ODBC Microsoft Access]Syntax error or access violation;

Where is the mistake? The code to create the view is correct. It is a matter of privilege? The "Create Table" statement works fine at runtime!

Thank you all in advance!

jc99

  • Hero Member
  • *****
  • Posts: 553
    • My private Site
Re: TODBCConnection with MS Access "Create View" Error
« Reply #1 on: July 03, 2015, 05:39:17 pm »
Isn't there a Semikolon ";" missing at the end of the ExecuteDirect statement ?
OS: Win XP x64, Win 7, Win 7 x64, Win 10, Win 10 x64, Suse Linux 13.2
Laz: 1.4 - 1.8.4, 2.0
https://github.com/joecare99/public
'~|    /''
,_|oe \_,are
If you want to do something for the environment: Twitter: #reduceCO2 or
https://www.betterplace.me/klimawandel-stoppen-co-ueber-preis-reduzieren

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
Re: TODBCConnection with MS Access "Create View" Error
« Reply #2 on: July 03, 2015, 06:07:34 pm »
I try with ";" at the end of statement but not work. Same error!!  :'( :'(

Leledumbo

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: TODBCConnection with MS Access "Create View" Error
« Reply #3 on: July 03, 2015, 11:06:51 pm »
Check "note" and "remarks" on this page:
https://msdn.microsoft.com/en-us/library/bb177895(v=office.12).aspx

ensure you don't violate any of them.

jc99

  • Hero Member
  • *****
  • Posts: 553
    • My private Site
Re: TODBCConnection with MS Access "Create View" Error
« Reply #4 on: July 04, 2015, 01:31:54 pm »
I try with ";" at the end of statement but not work. Same error!!  :'( :'(
... and there is no VTest object somewhere in the DB ?
If the view exists, you Should use 'ALTER' instead.
 
Carefull:
Quote
The Microsoft Access database engine does not support the use of CREATE VIEW, or any of the DDL statements, with non-Microsoft Access database engine databases.

What happens when you create this view in MS Access directly ?
( make sure that you delete it afterwards, or use ALTER as suggested above )
Are you in the right database ? I'm not sure about Access, but in MS-SQL (the bigger Brother) you must either use a fully qualified name or the "use [MyData]" statement
unless your Connection-Element not already selects the right DB.
You can not/should not insert a view when you are in the master/system -DB, or at least your table is not visible, because you are in the wrong scope.
« Last Edit: July 04, 2015, 01:42:39 pm by jc99 »
OS: Win XP x64, Win 7, Win 7 x64, Win 10, Win 10 x64, Suse Linux 13.2
Laz: 1.4 - 1.8.4, 2.0
https://github.com/joecare99/public
'~|    /''
,_|oe \_,are
If you want to do something for the environment: Twitter: #reduceCO2 or
https://www.betterplace.me/klimawandel-stoppen-co-ueber-preis-reduzieren

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
Re: TODBCConnection with MS Access "Create View" Error
« Reply #5 on: July 04, 2015, 08:42:38 pm »
If i type the same statement into Access or MBD Viewer plus work fine. In Delphi Xe5 with adoconnection work fine. Only with TODBCConnection not work... :'(a

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: TODBCConnection with MS Access "Create View" Error
« Reply #6 on: July 06, 2015, 09:39:59 pm »
Can you attach here you project with DB file(s), so we can test it on our side ?

jc99

  • Hero Member
  • *****
  • Posts: 553
    • My private Site
Re: TODBCConnection with MS Access "Create View" Error
« Reply #7 on: July 06, 2015, 09:58:52 pm »
Can you attach here you project with DB file(s), so we can test it on our side ?
If i type the same statement into Access or MBD Viewer plus work fine. In Delphi Xe5 with adoconnection work fine. Only with TODBCConnection not work... :'(a
And since it's working on DXE5, also the Delphi-Proj. if possible
OS: Win XP x64, Win 7, Win 7 x64, Win 10, Win 10 x64, Suse Linux 13.2
Laz: 1.4 - 1.8.4, 2.0
https://github.com/joecare99/public
'~|    /''
,_|oe \_,are
If you want to do something for the environment: Twitter: #reduceCO2 or
https://www.betterplace.me/klimawandel-stoppen-co-ueber-preis-reduzieren

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
Re: TODBCConnection with MS Access "Create View" Error
« Reply #8 on: July 08, 2015, 12:47:32 pm »
Ok, prepare the file and i will send both projects today/tomorrow.

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
Re: TODBCConnection with MS Access "Create View" Error
« Reply #9 on: July 08, 2015, 06:01:24 pm »
Hi guys,
I am attaching my test projects, in Lazarus 1.2.4 and Delhi XE5. Lazarus can't create or drop view, while delphi work fine!

miab3

  • Full Member
  • ***
  • Posts: 145
Re: TODBCConnection with MS Access "Create View" Error
« Reply #10 on: July 08, 2015, 10:19:23 pm »
@ecalogiuri,

With Zeos 7.2 r3636:
http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&start=345#p44589
and Lazarus 1.4-32Win works.
See attachment.

Michal

ecalogiuri

  • Jr. Member
  • **
  • Posts: 74
Re: TODBCConnection with MS Access "Create View" Error
« Reply #11 on: July 09, 2015, 09:09:51 am »
Tanks @miab3, now test Zeoslib...  :D

miab3

  • Full Member
  • ***
  • Posts: 145
Re: TODBCConnection with MS Access "Create View" Error
« Reply #12 on: July 09, 2015, 09:19:30 am »
@ecalogiuri,,

Note that Zeos is compatible with Lazarus and Delphi 7-XE8(and CodeTyphon).

Michal

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: TODBCConnection with MS Access "Create View" Error
« Reply #13 on: July 09, 2015, 10:52:08 am »
Interesting!

Seems, that ODBC driver for Access does not support CREATE VIEW ???
Also seems that other users have similar problem: http://forums.asp.net/t/952788.aspx?CREATE+DROP+ALTER+VIEW+in+Access+Database+using+ODBC

Also seems, that ADO and OLE DB does not suffer from this.

Here is trace:
Prepare success, but execute failed:

project1        fc0-220   ENTER SQLPrepare
      HSTMT               0x001A8378
      UCHAR *             0x018D84D0 [      44] "Create View VTest As Select * From TableTest"
      SDWORD                    44

project1        fc0-220   EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
      HSTMT               0x001A8378
      UCHAR *             0x018D84D0 [      44] "Create View VTest As Select * From TableTest"
      SDWORD                    44

project1        fc0-220   ENTER SQLExecute
      HSTMT               0x001A8378

project1        fc0-220   EXIT  SQLExecute  with return code -1 (SQL_ERROR)
      HSTMT               0x001A8378

      DIAG [42000] [Microsoft][ODBC Microsoft Access Driver]Syntax error or access violation  (55)

 

TinyPortal © 2005-2018