TODBCConnection with MS Access "Create View" Error
lazarus / 16-Databases

----------------------------------------------------------------------
ecalogiuri · 2015-07-03 07:24

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 · 2015-07-03 15:39

Isn't there a Semikolon ";" missing at the end of the ExecuteDirect
statement ?

----------------------------------------------------------------------
ecalogiuri · 2015-07-03 16:07

I try with ";" at the end of statement but not work. Same error!!

----------------------------------------------------------------------
Leledumbo · 2015-07-03 21:06

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 · 2015-07-04 11:31

Quote from: ecalogiuri on July 03, 2015, 06:07:34 pmI 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:
QuoteThe 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.

----------------------------------------------------------------------
ecalogiuri · 2015-07-04 18:42

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 · 2015-07-06 19:39

Can you attach here you project with DB file(s), so we can test it on
our side ?

----------------------------------------------------------------------
jc99 · 2015-07-06 19:58

Quote from: LacaK on July 06, 2015, 09:39:59 pmCan you attach here you
project with DB file(s), so we can test it on our side ?
Quote from: ecalogiuri on July 04, 2015, 08:42:38 pmIf 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

----------------------------------------------------------------------
ecalogiuri · 2015-07-08 10:47

Ok, prepare the file and i will send both projects today/tomorrow.

----------------------------------------------------------------------
ecalogiuri · 2015-07-08 16:01

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 · 2015-07-08 20:19

@ecalogiuri,

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

Michal

----------------------------------------------------------------------
ecalogiuri · 2015-07-09 07:09

Tanks @miab3, now test Zeoslib...

----------------------------------------------------------------------
miab3 · 2015-07-09 07:19

@ecalogiuri,,

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

Michal

----------------------------------------------------------------------
LacaK · 2015-07-09 08:52

Interesting!

Seems, that ODBC driver for Access does not support CREATE VIEW ???
Also seems that other users have similar problem: http://forums.asp.ne
t/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)