CREATE VIEW

[witek]

Hi.

Does CREATE VIEW work in MS Access ?

I have to modify query using DDL SQL.

Thanks for any help.

[Allen Browne]

CREATE VIEW works if you execute the query under ADO, e.g.:
Dim strSql As String
strSql = "CREATE VIEW qry1 as SELECT tblInvoice.* from tblInvoice;"
CurrentProject.Connection.Execute strSql

It may not work if you try to execute it from the interface, because Access
natively uses DAO.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"witek" <wite...@spam.gazeta.pl.invalid> wrote in message
news:egh7mq$iuo$2...@inews.gazeta.pl...

[witek]

Allen Browne wrote:
> CREATE VIEW works if you execute the query under ADO, e.g.:
> Dim strSql As String
> strSql = "CREATE VIEW qry1 as SELECT tblInvoice.* from tblInvoice;"
> CurrentProject.Connection.Execute strSql
>
> It may not work if you try to execute it from the interface, because Access
> natively uses DAO.
>

Thanks a lot.
I will try. Microsoft in unpredictable.
I've tried to build and test query under MSAccess before I moved it to
VBA and it of course failed.

[witek]

Yes. It works. Great! Thanks.

[Allen Browne]

If the query failed when executed in code, it probably contained a reference
like:
[Forms].[Form1].[Text1]

That kind of thing requires the Expression Service, which is not available
if the OpenRecordset or Execute the query in code. Instead, concatenate the
value from the form into the SQL string you wish to execute, e.g.:
strSql = "SELECT * FROM Table1 WHERE Surname = """ &
[Forms].[Form1].[Text1] & """;"

That issue is much more common than the ADO-specific one in your post.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"witek" <wite...@spam.gazeta.pl.invalid> wrote in message

news:egj4su$paj$1...@inews.gazeta.pl...

[witek]

Allen Browne wrote:

Thanks. But I don't use MS Access forms.
Query is called from Excel.
Everything works fine from VBA but when I tried to do the same in MS
Access it failed.
I started building query in MS Access which failed. It was surprise for
me because I was almost sure that query is build correctly.

[Jamie Collins]

Allen Browne wrote:
> If the query failed when executed in code, it probably contained a reference
> like:
> [Forms].[Form1].[Text1]
>
> That kind of thing requires the Expression Service, which is not available
> if the OpenRecordset or Execute the query in code.

In absence of the 'expression builder' it works like a PROCEDURE
('parameter query') i.e. it can be executed if you passed a value for
[Forms].[Form1].[Text1] as a parameter e.g.

Set rs = CurrentProject.Connection.Execute "EXECUTE ViewNameHere 'param
value here';"

Alternatively, use a Command object to create a parameter of known type
etc.

Jamie.

--

[Jamie Collins]

Allen Browne wrote:
> CREATE VIEW works if you execute the query under ADO
>

> It may not work if you try to execute it from the interface, because Access
> natively uses DAO.

Can be done with in the Access UI (Access2002 onwards) while in ANSI-92
Query Mode. See:

http://office.microsoft.com/en-us/assistance/HP030704831033.aspx

Jamie.

--