CREATE VIEW

W

witek

Hi.

Does CREATE VIEW work in MS Access ?

I have to modify query using DDL SQL.

Thanks for any help.
 
A

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.
 
W

witek

Allen said:
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.
Click to expand...

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.
 
W

witek

witek said:
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.
Click to expand...


Yes. It works. Great! Thanks.
 
A

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.
 
W

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.
 
Top