Access View

I ran into trouble trying to get something to work in Access.

The first issue was, although in Help, it states you can use CREATE VIEW, it
returns an error if you try to create a query in SQL View. It says there is
a syntax error in my CREATE TABLE syntax. Of course, thinking I would get a
different result by performing the same action, I ran it umpteen more times
to be sure. I guess I was in denial.

I finally found a conversation where someone said, Access uses DAO, not ADO
when you're in the GUI. Ok, that now made sense since the Help file said
"Note The Microsoft Jet database engine does not support the use of CREATE
VIEW, or any of the DDL statements, with non-Microsoft Jet database engine
databases."

I found I could create a function, run that to create the view and then it
would show up in the query section. Is that a workaround or what? Too bad
it's not in the Help file. However, once I tried that, I ran into another
issue. You can only use simple selects in a view. (translation: grrr)

Here's my simple function:

Function CreateViewAdo()
Dim conn As ADODB.Connection
Dim strSQL As String
strSQL = "CREATE VIEW view1 AS SELECT * FROM tablename;"
Set conn = CurrentProject.Connection
conn.Execute strSQL
Set conn = Nothing
End Function

Does anyone know if the ORDER BY can be used if I did this all via dynamic
SQL or will I run into the same issue of a view only allowing a simple
select?

Ex.
sql = "CREATE VIEW view1 AS SELECT * FROM tablename ORDER BY id;"
sql2 = "SELECT name, id FROM view1 WHERE name = 'Peyton Manning';"
sql3 = "DROP VIEW view1"

I'm thinking it's going to fail which is why I'm here. Is there a
workaround? id really won't be the field as it is an autonumber field but
rather a field that can have duplicate values. This is to create a ranking
system where if 10 users had a value of 1, the next rank would be 11th.

TIA...
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Access View

Roland Hall wrote:
> I ran into trouble trying to get something to work in Access.
>
> The first issue was, although in Help, it states you can use CREATE
> VIEW, it returns an error if you try to create a query in SQL View.
> It says there is a syntax error in my CREATE TABLE syntax. Of
> course, thinking I would get a different result by performing the
> same action, I ran it umpteen more times to be sure. I guess I was
> in denial.
>
> I finally found a conversation where someone said, Access uses DAO,
> not ADO when you're in the GUI. Ok, that now made sense since the
> Help file said "Note The Microsoft Jet database engine does not
> support the use of CREATE VIEW, or any of the DDL statements, with
> non-Microsoft Jet database engine databases."

Hmm - that has nothing to do with the issue. DAO works with Jet databases
.... directly, unlike ADO. So, DAO should* be able to allow a CREATE VIEW
statement to be executed. The "non-Microsoft Jet" caveat applies to
situations where one is using non-Jet databases, such as dbase, Excel, or
ODBC databases. The issue seems to be that DAO is not able to support the
new features in Jet 4.0, which makes sense since DAO has been deprecated
since before Jet 4.0 was released.

>
> I found I could create a function, run that to create the view and
> then it would show up in the query section. Is that a workaround or
> what? Too bad it's not in the Help file. However, once I tried
> that, I ran into another issue. You can only use simple selects in a
> view. (translation: grrr)
>
> Here's my simple function:
>
> Function CreateViewAdo()
> Dim conn As ADODB.Connection
> Dim strSQL As String
> strSQL = "CREATE VIEW view1 AS SELECT * FROM tablename;"
> Set conn = CurrentProject.Connection
> conn.Execute strSQL
> Set conn = Nothing
> End Function
>
> Does anyone know if the ORDER BY can be used if I did this all via
> dynamic SQL or will I run into the same issue of a view only allowing
> a simple select?

You're using dynamic sql in the above function. I don't understand what you
think the difference is.

>
> Ex.
> sql = "CREATE VIEW view1 AS SELECT * FROM tablename ORDER BY id;"
> sql2 = "SELECT name, id FROM view1 WHERE name = 'Peyton Manning';"
> sql3 = "DROP VIEW view1"
>
> I'm thinking it's going to fail which is why I'm here. Is there a
> workaround? id really won't be the field as it is an autonumber
> field but rather a field that can have duplicate values. This is to
> create a ranking system where if 10 users had a value of 1, the next
> rank would be 11th.
>
> TIA...

I think* you'll have better success using ADOX to create a stored procedure
(which is actually a saved query in disguise). A saved query can contain an
ORDER BY clause. Come to think of it, you can probably use CREATE PROCEDURE
instead of CREATE VIEW to achieve your goal.


Bob Barrows

*Translation: it's been years since I seriously used Jet, so
a) my recollection may be fuzzy
b) my information may be outdated
c) You would probably have better luck getting an answer from an Access
newsgroup
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access View

"Bob Barrows [MVP]" wrote in message
news:OCKZoxurFHA.1252@TK2MSFTNGP09.phx.gbl...
: Roland Hall wrote:
: Hmm - that has nothing to do with the issue. DAO works with Jet databases
: ... directly, unlike ADO. So, DAO should* be able to allow a CREATE VIEW
: statement to be executed. The "non-Microsoft Jet" caveat applies to
: situations where one is using non-Jet databases, such as dbase, Excel, or
: ODBC databases. The issue seems to be that DAO is not able to support the
: new features in Jet 4.0, which makes sense since DAO has been deprecated
: since before Jet 4.0 was released.

Sounds good.

: > Here's my simple function:
: >
: > Function CreateViewAdo()
: > Dim conn As ADODB.Connection
: > Dim strSQL As String
: > strSQL = "CREATE VIEW view1 AS SELECT * FROM tablename;"
: > Set conn = CurrentProject.Connection
: > conn.Execute strSQL
: > Set conn = Nothing
: > End Function
: >
: > Does anyone know if the ORDER BY can be used if I did this all via
: > dynamic SQL or will I run into the same issue of a view only allowing
: > a simple select?
:
: You're using dynamic sql in the above function. I don't understand what
you
: think the difference is.

I was referring to using it from ADO in ASP, not as a function in Access.

: > Ex.
: > sql = "CREATE VIEW view1 AS SELECT * FROM tablename ORDER BY id;"
: > sql2 = "SELECT name, id FROM view1 WHERE name = 'Peyton Manning';"
: > sql3 = "DROP VIEW view1"
: >
: > I'm thinking it's going to fail which is why I'm here. Is there a
: > workaround? id really won't be the field as it is an autonumber
: > field but rather a field that can have duplicate values. This is to
: > create a ranking system where if 10 users had a value of 1, the next
: > rank would be 11th.
:
: I think* you'll have better success using ADOX to create a stored
procedure
: (which is actually a saved query in disguise). A saved query can contain
an
: ORDER BY clause. Come to think of it, you can probably use CREATE
PROCEDURE
: instead of CREATE VIEW to achieve your goal.
:
: *Translation: it's been years since I seriously used Jet, so
: a) my recollection may be fuzzy
: b) my information may be outdated
: c) You would probably have better luck getting an answer from an Access
: newsgroup

Can't use CREATE PROCEDURE in Access for the same reason unless you do it in
a function.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Access View

Roland Hall wrote:
>
> Can't use CREATE PROCEDURE in Access for the same reason unless you
> do it in a function.

True, but running it via ADO in a VBA module is exactly the same as running
it via ADO in an ASP page ...
Procedures can also be created via ADOX if you care to research that.

My point is: it sounds like you need a "procedure", not a "view".

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access View

"Bob Barrows [MVP]" wrote in message
news:%23Px1UD7rFHA.4076@TK2MSFTNGP10.phx.gbl...
: Roland Hall wrote:
: >
: > Can't use CREATE PROCEDURE in Access for the same reason unless you
: > do it in a function.
:
: True, but running it via ADO in a VBA module is exactly the same as
running
: it via ADO in an ASP page ...
: Procedures can also be created via ADOX if you care to research that.
:
: My point is: it sounds like you need a "procedure", not a "view".

And my point is a need a brain. Thanks Bob.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp