Re: VBScript Problem with Access Query (Unspecified error)


"Codeblack" <Codeblack@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7DFA172A-7C37-4098-8CE6-CED7E09DD0E1@xxxxxxxxxxxxxxxx
I am facing a strange problem with a access query. I am trying to retrive
some records from the multiple tables using Inner Join. The query is
working
absolutely fine when i execute the query in MS access 2003. But when i use
the below Query in VBscript then i am getting the below Error. Can any one
help me to findout what is that i am missing. Any help will be greatly
appreciated.

Error : (null): Unspecified error.
Error is pointing at :- Set rs = conn.Execute(StrSQL)


Set conn = CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=z:\Project\test.mdb"
conn.Open strConnect

intRow = 1
StrSQL = "SELECT [Webmail Domains].[Root Domain],
Sum([Total_Bytes]/1024/1024) AS [Total MB], Sum(SenderToDom.Recip_Counts)
AS
[Recip Counts]"&_"FROM [DeptCode CorpSvcs] INNER JOIN ((SenderToDom INNER
JOIN AllEmployees ON SenderToDom.Sender = AllEmployees.Email) INNER JOIN
[Webmail Domains] ON SenderToDom.Recip_Domain = [Webmail Domains].Domain)
ON
[DeptCode CorpSvcs].DeptCode = AllEmployees.DeptCode"&_
"GROUP BY [Webmail Domains].[Root Domain] ORDER BY
Sum([Total_Bytes]/1024/1024) DESC"

Set rs = conn.Execute(StrSQL)
do While not rs.EOF
wscript.Echo rs(0)
wscript.Echo rs(1)
rs.MoveNext
Loop
RS.Close
set RS = nothing
conn.close
set conn = nothing


Word wrapping makes it very difficult to read your code, but I believe you
need spaces around your "&" (concatenation) and "_" (line continuation)
characters. The string "&_" will definitely fail. It should be " & _". I
would use something similar to:
==========
StrSQL = "SELECT [Webmail Domains].[Root Domain], " _
& "Sum([Total_Bytes]/1024/1024) AS [Total MB], " _
& "Sum(SenderToDom.Recip_Counts) AS [Recip Counts]" _
& "FROM [DeptCode CorpSvcs] " _
& "INNER JOIN ((SenderToDom " _
& "INNER JOIN AllEmployees " _
& "ON SenderToDom.Sender = AllEmployees.Email) " _
& "INNER JOIN [Webmail Domains] " _
& "ON SenderToDom.Recip_Domain = [Webmail Domains].Domain) "
_
& "ON [DeptCode CorpSvcs].DeptCode = AllEmployees.DeptCode" _
& "GROUP BY [Webmail Domains].[Root Domain] " _
& "ORDER BY Sum([Total_Bytes]/1024/1024) DESC"
========
I wasn't sure how I wanted to indent the nested joins to make it easy to
read. The parentheses are important for indicating which JOIN the ON clauses
apply to. However, I believe just adding the spaces should fix the error.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--


.

Relevant Pages

  • Re: VBScript Problem with Access Query (Unspecified error)
    ... You might it simpler to save the query that works in Access, ... Set conn = CreateObject ... INNER JOIN ((SenderToDom INNER JOIN AllEmployees ... Please reply to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: VBScript Problem with Access Query (Unspecified error)
    ... You might it simpler to save the query that works in Access, ... Set conn = CreateObject ... INNER JOIN ((SenderToDom INNER JOIN AllEmployees ... Please reply to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)