Method 'Command' of object 'view' failed

[OzNet]

Hi

Can anyone tell me why I am getting this error message when applying the sql to a query please?

Here is my code:
[code=vb]
Private Sub cmdJobs_Click()
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim varItem As Variant
Dim strArea As String
Dim strZone As String
Dim strStatus As String
Dim strSQL As String
Dim blnTest As Boolean

'sql statement
strSQL = "SELECT tblJobs.ClientN ame, tblJobs.ClientA ddress, tblJobs.ClientS uburb, Postcodes.Local ity, Postcodes.State , Postcodes.Pcode , tblJobs.ClientP hone, tblJobs.ClientM obile, tblJobs.Referre r, tblJobs.okayed, tblJobs.Notes, tblJobs.TeamAll ocated, tblJobs.Area, tblJobs.Zone, tblJobs.Rubbish Removal, tblJobs.Mower, tblJobs.UteTrai lor, tblJobs.TeamFoc us, tblJobs.Assista nt, tblJobs.Status, tblYWCDate.[YWC Date], tblJobs.JobsID, tbAreas.Colour, tblZones.ZColou r" & _
" FROM tblYWCDate, tbAreas INNER JOIN (tblZones INNER JOIN (tblJobs INNER JOIN Postcodes ON tblJobs.ClientS uburb = Postcodes.Local ity) ON tblZones.ZoneNa me = tblJobs.Zone) ON tbAreas.AreaNam e = tblJobs.Area;"


' Apply the SQL statement to the stored query
cat.ActiveConne ction = CurrentProject. Connection
Set cmd = cat.Views("qryJ obSheets").Comm and
cmd.CommandText = strSQL
Set cat.Views("qryJ obSheets").Comm and = cmd
Set cat = Nothing
[/code]

This is the line where the problem is showing up:
Set cat.Views("qryJ obSheets").Comm and = cmd

The sql was copied from the query itself and when I get past this problem I will be adding variables to change the sql via forms fields

I have used this code (not the sql obviously) in another database without problems and I have checked the references which are all the same except for Calendar which I doubt would be the issue.

Greg

[Stewart Ross]

This is the line where the problem is showing up:
Set cat.Views("qryJ obSheets").Comm and = cmd
The sql was copied from the query itself and when I get past this problem I will be adding variables to change the sql via forms fields
Greg

Hi Greg. Looks like there could be a problem with the SQL statement, not the code. I tested by substituting SQL and table references for one of my test tables without any difficulties - in line with what you say about having used this code before yourself.

Your SQL statement seems fine as far as can be seen without the table defs to refer to, and anyway you copied it directly from a query. Is the Areas table correctly prefixed tbAreas (and not tblAreas) throughout?

-Stewart

[OzNet]

Thanks Stewart for your reply.

I have found a work around without actually resolving the problem head on.

Google searches seem to indicate a problem of compatibility with the ADOX dll's I tried following some of the instructions but didn't really know what I was doing.

There seems to be issues with Vista and Access 2003 but it was not working on my laptop with XP either.

Eventually (today) I changed from ADOX to DAO and it works fine.

Thanks again Stewart for taking the time to find a resolution.