Prev: Flag a value in a group...
Next: Query result shows all fields
From: PieterLinden via AccessMonster.com on 16 May 2010 18:46
FWIW,
here's Tony's explanation of how to use Temp Tables in Access....
http://www.granite.ab.ca/access/temptables.htm

not quite the same as SQL Server, but the best you're going to do in Access...


--
Message posted via http://www.accessmonster.com

From: Edwinah63 on 17 May 2010 04:58
>>You can of course hard-code
>>some unioned queries to force the missing records to be included.

D'oh! I completely forgot about creating a dynamic union query! Thank
you for reminding me!

Maybe something like this?

public sub CreateAQuery(mthsBetween as integer)

dim i as integer
dim sql as string

for i = 0 to mthsBetween

sql = sql & "select " & i & " as Mth union "

next i

<------Can I do this next bit??--->

Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
'MyUnionQuery' DROP QUERY MyUnionQuery;"
CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql


end sub

The code above isn't quite right but you get the idea.

Had a hunt around the internet for a "Create Query" statement. Is
there one? Would prefer to stick to SQL statements wherever possible
but will use querydefs etc otherwise.

A big thank you to everyone who responded :-)
From: Bob Barrows on 17 May 2010 07:38
Edwinah63 wrote:
>>> You can of course hard-code
>>> some unioned queries to force the missing records to be included.
>
> D'oh! I completely forgot about creating a dynamic union query! Thank
> you for reminding me!
>
> Maybe something like this?
>
> public sub CreateAQuery(mthsBetween as integer)
>
> dim i as integer
> dim sql as string
>
> for i = 0 to mthsBetween
>
> sql = sql & "select " & i & " as Mth union "
>
> next i
>
> <------Can I do this next bit??--->
>
> Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
> 'MyUnionQuery' DROP QUERY MyUnionQuery;"
> CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql
>
>
> end sub
>
> The code above isn't quite right but you get the idea.
>
> Had a hunt around the internet for a "Create Query" statement. Is
> there one? Would prefer to stick to SQL statements wherever possible
> but will use querydefs etc otherwise.
>
> A big thank you to everyone who responded :-)

Aside from the test for existence: absolutely. In this case, it would be

CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

If you parameterize it or create an action query, it would be

CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

Online help has a well-hidden section on JetSQL, but I did manage to find it
:-)
Just hit F1, find the again well-hidden link on the help screen to get to
the table of contents, and find the node called "Microsoft Jet SQL
Reference" - the last bit isn't so hard :-)

You would have to look at the querydefs collection (if using DAO) or the
ADOX Views or Procedures collection (if using ADO) to find out if the query
exists. But you know ... since you're already into the Querydefs collection,
you might as well simply use a querydef object ... it will truly be
temporary if you omit the qryname argument from the CreateQuerydef
statement. Since you are using VBA, you're code won't be portable anyways,
so you might as well do it the easy way.

Oh! And don't forget: unlike Transact-SQL, Jet SQL requires a FROM clause
with table expression. You don't have to actually retrieve any data from the
table, but you do have to name one.

Transact-SQl:
Select 'a' as col1, 2 as col2
union
Select 'b', 4

Jet SQL:
Select TOP 1 'a' as col1, 2 as col2 FROM existingtable

union
Select TOP 1 'b', 4 FROM existingtable

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"


From: Edwinah63 on 17 May 2010 21:45
Thanks for your reply. I am having trouble now with the Create View
Statement.

To check I had the right syntax I created a dummy table with 1 field,
1 record. I then created the sample statement in the SQL view of both
an ordinary and so called "Data Definition" query

create view MyView as select dummyid from tblDummy

Both come back with the error "Syntax error in Create Table statement"

What am I doing wrong?

Had a google around and it seems that Access 2007 will not recognize
create view statement for this database unless the ANSI92
compatibility is turned on and the ANSI92 option under Access Options/
Object Designers/Query Design/Sql Server Compatible Syntax is greyed
out.

Database is an Access 2007 database looking at Access 2007 tables
within the same database. I really want to avoid the whole querydefs
thing if I can.

Are you sure that JetSQL supports the DDL statements Create View/
Procedure? If so may I have the full steps please?
From: Bob Barrows on 17 May 2010 22:47
Edwinah63 wrote:
> Thanks for your reply. I am having trouble now with the Create View
> Statement.
>
> To check I had the right syntax I created a dummy table with 1 field,
> 1 record. I then created the sample statement in the SQL view of both
> an ordinary and so called "Data Definition" query
>
> create view MyView as select dummyid from tblDummy
>
> Both come back with the error "Syntax error in Create Table statement"
>
> What am I doing wrong?
>
> Had a google around and it seems that Access 2007 will not recognize
> create view statement for this database unless the ANSI92
> compatibility is turned on and the ANSI92 option under Access Options/
> Object Designers/Query Design/Sql Server Compatible Syntax is greyed
> out.
>
> Database is an Access 2007 database looking at Access 2007 tables
> within the same database. I really want to avoid the whole querydefs
> thing if I can.
>
> Are you sure that JetSQL supports the DDL statements Create View/
> Procedure? If so may I have the full steps please?

I can't give you any more than what is in the online help .. sorry.
In A2003, I did have to check the ANSI92 option for the database I was
testing with in order to make the CREATE VIEW statement work in the SQL View
of a query builder window. Without that option turned on, I got the same
error you did.

This snippet of code also failed until I turned on ANSI92:

Sub testcreateview()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "CREATE VIEW testqry AS select * from table1", dbFailOnError
End Sub

If you cannot turn on that option, it appears you will have to resort to the
querydef (or ADOX Views) method. I'm baffled as to why you are so desperate
to avoid it. Is it because of the need to create a Reference to DAO? If so,
you can do that in code by using the Application object's References
collection, which has two methods for creating references: AddFromFile and
AddFromGuid. Here is an example of the latter:

Dim ref As Reference, DAOfound As Boolean
DAOfound = False
For Each ref In Application.References
If ref.Name = "DAO" Then
DAOfound = True
exit for
End If
Next ref
If Not DAOfound Then
Application.References.AddFromGuid
"{00025E01-0000-0000-C000-000000000046}", 3, 6
End If


They are very simple to use. You don't even have to drop the querydef if
you've already created it - just set the .SQL property to the new sql
statement, like this:

sub createqry()
dim db as database,qdf as querydef,strsql as string
'build your string
set db=currentdb
on error resume next
set qdf=db.querydefs("qryname")
if err <> 0 then set qdf=db.createquerydef("qryname")
qdf.sql=strsql
end sub

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Flag a value in a group...
Next: Query result shows all fields