CREATE VIEW

  • Thread starter Thread starter witek
  • Start date Start date
W

witek

Hi.

Does CREATE VIEW work in MS Access ?

I have to modify query using DDL SQL.

Thanks for any help.
 
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 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.
 
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.
 
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 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.
 
Allen said:
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.
Click to expand...

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.

--
 
Back
Top