SELECT Query 1

[Dodge]

Hello all, need some help...

In Access 2K I'm trying to run mulitple SELECT queries, for instance I want to run the following:

select count(*) as OOB11
from [OOB Station1] where [Line]='1' and [Shift]='1'
go
select count(*) as OOB2
from [OOB Staion2] where [Line='2' and [Shift]='1'

It works in SQL Server 7, so whats the deal, does Access not support keyword 'go' or am I missing something?

Thanks for the help
Dodge

 

[nickjar2]

I have never come across the keyword 'Go' in access. Are you familiar with VBA?

Nick

 

[Dodge]

Well I did some reacearch and it turns out that 'go' is a resrved keyword recognized by the Query Analyzer in SQL Server. I am familiar with VBA.

 

[mac318]

Dodge, just because 'go' is a keyword for SQL 7 does not necessarily follow that it is also a keyword for Jet 4.0 which is the first line interpreter for Access 2000. If you are going communicate directly with SLQ 7, thus by-passing Jet 4.0, you must use ODBCdirect.

mac

 

[nickjar2]

Whenever i need to run multiple queries i just run some code to execute the queries.

Nick

 

[Dodge]

Mac & Nic,

Thanks for the replies. The 'go' keyword is not a transact-sql statment. It is simply a utility/command used by osql, isql and the query analyzer. So in other words doesnt even apply to what it is i'm trying todo.

If anyone knows how to create a mulitple SELECT query in access please help.

Thanks
Dodge

 

[mac318]

As do I, Nick; however, I believe Dodge is confusing Jet language with SQL 7 language. I don't believe Jet supports the keyword 'go' which appears to be the problem.

mac

 

[nickjar2]

Here's similar code to what i would use. Create a knew module:

function whatever()
dim db as database
dim rs as dao.recordset
dim sSelect as string

sSelect = "select count(*) as OOB11 "
sselect = sselect & " from [OOB Station1] where [Line] ='1' " sselect = sselect & "and [Shift]='1';"

set rs = db.openrecordset(sSelect)

msgbox rs.fields(0)

sSelect = "select count(*) as OB2 "
sselect = sselect & " from [OOB Station2] where [Line]='2' " sselect = sselect & "and [Shift]='1';"

set rs = db.openrecordset(sSelect)

msgbox rs.fields(0)

end function

I think thats about right. Sorry about the formatting. I can't seem to get that right.

Nick

 

[Dodge]

Thanks Nic, It seems I'm going to have to create a Modual after all, just would have been much simpler to use a query.

Mac, 'go' has nothing to do with what im trying to accomplish. As I stated above its a utiltiy used by some sql analyzers. So, it's not reconized by Jet or SQL Server only the query analyzer. It was my mistake for putting in the statment in the first place. Needless to say, I still can't get access to execute 2 select statments.