Vista - VBScript Problem with Access Query (Unspecified error)

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

Codeblack wrote:It is very hard to help you without knowing the string that this
expression evaluates to.
Echo the contents of StrSQL or write them to a file so you can show us
what the result looks like.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

The Echo of StrSQL just returned this

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

Z:\Project\test.vbs(25, 1) (null): Unspecified error



"Bob Barrows [MVP]" wrote:

And you have verified that this matches the sql of the query that you
successfully ran in Access?

You might it simpler to save the query that works in Access, giving it
a name you can use in vbscript:

Set conn = CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=z:\Project\test.mdb"
conn.Open strConnect
set rs=createobject("adodb.recordset")
conn.MySavedQuery rs
do While not rs.EOF
wscript.Echo rs(0)
wscript.Echo rs(1)
rs.MoveNext
Loop


Codeblack wrote:--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

--
Jeff C
Live Well .. Be Happy In All You Do


"Codeblack" wrote:
I use Access extensively and whenever I want to process data from outside
the database I use a different method. I build the process to work
completely within an Access macro, whether running multiple queries in
succession and/or running code from a module - place it all in a macro. Then
I use Access command line functionality - "full path to Access.exe" "full
path to the database I am using" /x "name of macro". I place the command
line in a batch file and run it using the task scheduler. Maybe this method
could help?

Thank you very much for your suggestion. The problem got solved.

"Bob Barrows [MVP]" wrote: