CREATE VIEW error

[Edwin Knoppert]

I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]), Year([DatumAanmaak]),
Day([DatumAanmaak]), Month([DatumAanmaak]), Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

It's fine if i could insert this view statement using some alternative
ASP.NET v2 call instead of using execute().
Only need to know how.

MS Access accepts this query fine.
The database is remote and makes it hard to update the db.

 

[David Browne]

It's fine if i could insert this view statement using some alternative
ASP.NET v2 call instead of using execute().
Only need to know how.

MS Access accepts this query fine.
The database is remote and makes it hard to update the db.

What is the remote database?

Why don't you have a local version that you can test stuff like this
against?

David

 

[Guest]

What RDBMS are you using to create such a view?

 

[Jeff Dillon]

You can create simpler views ok??

 

[Edwin Knoppert]

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access through
ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert new
queries remote.
I'm just taking existing querydef's from the access database which work(!)
and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute command.
Liek i said, it works until it's getting complicated according the error
message i get.

 

[Edwin Knoppert]

To clarify, both local as remote accept simplier queries.
I'm trying to understand the other stuff in OleDbSchemaGuid.Tables and so.
Maybe this part offers a better way to insert these views.

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access through
ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert new
queries remote.
I'm just taking existing querydef's from the access database which work(!)
and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute command.
Liek i said, it works until it's getting complicated according the error
message i get.

I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Jeff Dillon]

Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access through
ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert new
queries remote.
I'm just taking existing querydef's from the access database which work(!)
and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute command.
Liek i said, it works until it's getting complicated according the error
message i get.

I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.

Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert new
queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the error
message i get.

I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[David Browne]

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.

Well back in the day, we used to break the .MDB into a "front end" and a
"back end". The front end .mdb would have all your querydefs, forms,
reports and linked tables to the "back end". Then you can deploy a new
version of the front end without having to download all the data.

David

 

[Edwin Knoppert]

Eeeekk!!

Does it mean i have to create queries which apply to the other database?
I'll invest a way to make use of the enumeration stuff, maybe it allows to
write VIEWS as i want.

 

[David Browne]

Eeeekk!!

Does it mean i have to create queries which apply to the other database?
I'll invest a way to make use of the enumeration stuff, maybe it allows to
write VIEWS as i want.

Your queries don't change, they are just going against linked tables instead
of real ones. The worst part is managing the linking, since it uses the
path to the back end database. You would need to have an identical folder
structure for the local and remote setup, or go thorugh a relinking process
after deployment.

I agree it's not very elegant, but it's Access after all.

David

 

[Edwin Knoppert]

Thanks!

I'll first dive into the OLEDB stuff itself before i start to do things like
you suggested, i'll bear those in mind.

 

[Jeff Dillon]

What? You create a simple example that works, and add your linked tables
until it works. Basic troubleshooting. Of course you need everything, but
you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is exactly
what it causing your error (?? obviously?)

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.

Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert new
queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the error
message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

Sorry boss, will send an example tomorrow.
Today i did use a second database but that's not a very nice thing to use.

1) MSAccess hardcodes the path.
2) So i use the IN stuff in a where clause and the engine depends heavily on
it's current directory.
So only the DB filename without path was not good
3) item 2 was still tested on our test webserver and indeed, the folder
issue, so i tested with full path and it worked.
But frankly unusable since each query must now have a fixed folder/filename,
i'm not going to waste my time (the whole day) on this.
4) I was looking at the 'querybuilder' object but i'm not that clever with
that to manage me the SELECT in SELECT
Note that the queries are rather difficult, not an ordinary SELECT, no
select from a group by query and such.
5) Been very busy with the database schem stuff today and looking for a way
to update the scema, i was unlucky.
I can read it fine but not do a persistant update unf.

What? You create a simple example that works, and add your linked tables
until it works. Basic troubleshooting. Of course you need everything, but
you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.

Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

OK Jeff, i worked it out, at the end it was easy to prepare an example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of additional
querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a tranaction
and rollback.

What? You create a simple example that works, and add your linked tables
until it works. Basic troubleshooting. Of course you need everything, but
you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.

Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Jeff Dillon]

I assume you've tried other ORDER BY clauses that work ok? I believe YEAR is
a built-in Access function that you can't use. Did you try ORDER BY Field3?

Jeff

OK Jeff, i worked it out, at the end it was easy to prepare an example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of additional
querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a tranaction
and rollback.

What? You create a simple example that works, and add your linked tables
until it works. Basic troubleshooting. Of course you need everything, but
you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)

I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.


"Jeff Dillon" <[email protected]> schreef in bericht
Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for me
unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

I have not, will try tomorrow (Holland )
I'm using year and such in expressions, these go well though.

I assume you've tried other ORDER BY clauses that work ok? I believe YEAR
is a built-in Access function that you can't use. Did you try ORDER BY
Field3?

Jeff

OK Jeff, i worked it out, at the end it was easy to prepare an example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of additional
querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a tranaction
and rollback.

What? You create a simple example that works, and add your linked tables
until it works. Basic troubleshooting. Of course you need everything,
but you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)


I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.


"Jeff Dillon" <[email protected]> schreef in bericht
Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for
me unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Field3;

Same result (error)

I have not, will try tomorrow (Holland )
I'm using year and such in expressions, these go well though.

I assume you've tried other ORDER BY clauses that work ok? I believe YEAR
is a built-in Access function that you can't use. Did you try ORDER BY
Field3?

Jeff

OK Jeff, i worked it out, at the end it was easy to prepare an example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of additional
querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a
tranaction and rollback.




"Jeff Dillon" <[email protected]> schreef in bericht
What? You create a simple example that works, and add your linked
tables until it works. Basic troubleshooting. Of course you need
everything, but you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)


I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.


"Jeff Dillon" <[email protected]> schreef in bericht
Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for
me unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?

 

[Edwin Knoppert]

You can try my example safely..

I have not, will try tomorrow (Holland )
I'm using year and such in expressions, these go well though.

I assume you've tried other ORDER BY clauses that work ok? I believe YEAR
is a built-in Access function that you can't use. Did you try ORDER BY
Field3?

Jeff

OK Jeff, i worked it out, at the end it was easy to prepare an example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of additional
querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a
tranaction and rollback.




"Jeff Dillon" <[email protected]> schreef in bericht
What? You create a simple example that works, and add your linked
tables until it works. Basic troubleshooting. Of course you need
everything, but you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That is
exactly what it causing your error (?? obviously?)


I already tried that (and forgot) but of course i can not have less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.


"Jeff Dillon" <[email protected]> schreef in bericht
Reduce the complexity until you find where it's breaking

To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have access
through ftp.

Yes, we have the project locally, i'm testing this create view stuff
locally.
The idea is that i need to write me complex queries, to complex for
me unless i prepare multiple querydefs (views) using each other.

To avoid downloading the database for maintanance i wanted to insert
new queries remote.
I'm just taking existing querydef's from the access database which
work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does not.. ???

I simply use an ASPX having a textbox being passed to the execute
command.
Liek i said, it works until it's getting complicated according the
error message i get.




"Edwin Knoppert" <[email protected]> schreef in bericht
I have serious issue by inserting a query using (names are fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS Dag,
Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak]) DESC ,
Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?