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:

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.ClientName, tblJobs.ClientAddress, tblJobs.ClientSuburb, Postcodes.Locality, Postcodes.State, Postcodes.Pcode, tblJobs.ClientPhone, tblJobs.ClientMobile, tblJobs.Referrer, tblJobs.okayed, tblJobs.Notes, tblJobs.TeamAllocated, tblJobs.Area, tblJobs.Zone, tblJobs.RubbishRemoval, tblJobs.Mower, tblJobs.UteTrailor, tblJobs.TeamFocus, tblJobs.Assistant, tblJobs.Status, tblYWCDate.[YWC Date], tblJobs.JobsID, tbAreas.Colour, tblZones.ZColour" & _
" FROM tblYWCDate, tbAreas INNER JOIN (tblZones INNER JOIN (tblJobs INNER JOIN Postcodes ON tblJobs.ClientSuburb = Postcodes.Locality) ON tblZones.ZoneName = tblJobs.Zone) ON tbAreas.AreaName = tblJobs.Area;"
 
 
 ' Apply the SQL statement to the stored query
        cat.ActiveConnection = CurrentProject.Connection
        Set cmd = cat.Views("qryJobSheets").Command
        cmd.CommandText = strSQL
        Set cat.Views("qryJobSheets").Command = cmd
        Set cat = Nothing
 

This is the line where the problem is showing up:
Set cat.Views("qryJobSheets").Command = 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("qryJobSheets").Command = 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.