Open of object recordset failed

[engnouna]

Hi

When I execute this query:
rs6.Open SQL, cn, adOpenKeyset, adLockPessimistic

the following message occurs:
"Open of object recordset Failed"

I am using Access database

What's wrong?

Thanks

[noshaba]

how u have declared the rs6 ?

[Shuja Ali]

Most probably you are using a reserved keyword in your query, like DATE, NAME, etc. Try to wrap the Column names in Square Brackets.

If that doesn't help then please show us what is in SQL variable?

[randem]

Show the code surounding that statement...

[engnouna]

The SQl expression is

SQL = "Select * from test inner Join Client on test.ClientID=Client.ClientID"
SQL = SQL & " inner Join Contact on test.ContactID=Contact.ContactID"
SQL = SQL & " inner Join Event on test.EventID=Event.EventID"
SQL = SQL & " inner Join Interval on test.IntID=Interval.IntID"
SQL = SQL & " inner Join Satellite on test.SatelliteID=Satellite.SatelliteID"
SQL = SQL & " inner Join Origin on test.OriginID=Origin.OriginID"
SQL = SQL & " inner Join Destination on test.DestinationID=Destination.DestinationID"
SQL = SQL & " inner Join Guest on test.GuestID=Guest.GuestID where BID=12"

Please help me

[randem]

You need to show more that just the SQL statement. More code...

What is rs6 defined as? Did you open the database? etc...

We cannot see anything from one or two statements...

[engnouna]

Well I used Rs6 to open another SQl and it worked,so i am pretty sure that the error is from the SQL string

What's wrong in my SQL string?
thanks

[randem]

If you are go tell us what is wrong, then why ask the question...

Did you test the SQL statement in native Access?

[Shuja Ali]

The SQl expression is

SQL = "Select * from test inner Join Client on test.ClientID=Client.ClientID"
SQL = SQL & " inner Join Contact on test.ContactID=Contact.ContactID"
SQL = SQL & " inner Join Event on test.EventID=Event.EventID"
SQL = SQL & " inner Join Interval on test.IntID=Interval.IntID"
SQL = SQL & " inner Join Satellite on test.SatelliteID=Satellite.SatelliteID"
SQL = SQL & " inner Join Origin on test.OriginID=Origin.OriginID"
SQL = SQL & " inner Join Destination on test.DestinationID=Destination.DestinationID"
SQL = SQL & " inner Join Guest on test.GuestID=Guest.GuestID where BID=12"

Please help me

Try wrapping up your columns/Table names in square brackets [] in your SQL and the problem will be resolved. I guess it is Event/Test /Interval that is creating this problem.

[engnouna]

Well i tried this

SQL = "Select * from [test]"
SQL = SQL & " inner Join [Client] on [test].[ClientID]=[Client].[ClientID]"
SQL = SQL & " inner Join [Contact] on [test].[ContactID]=[Contact].[ContactID]"
SQL = SQL & " inner Join [Event] on [test].[EventID]=[Event].[EventID]"
SQL = SQL & " inner Join [Interval] on [test].[IntID]=[Interval].[IntID]"
SQL = SQL & " inner Join [Satellite] on [test].[SatelliteID]=[Satellite].[SatelliteID]"
SQL = SQL & " inner Join [Origin] on [test].[OriginID]=[Origin].[OriginID]"
SQL = SQL & " inner Join [Destination] on [test].[DestinationID]=[Destination].[DestinationID]"
SQL = SQL & " inner Join [Guest] on [test].[GuestID]=[Guest].[GuestID] where [test].[BID]=12"

But it returned "Syntax error missing operator"

I am desperate,what's wrong with my SQL statement?

Thanks

[Shuja Ali]

Shouldn't it be

SQL = "Select [TEST].* from [test]"

And is BID a numeric field?

[si_the_geek]

I guess it is Event/Test /Interval that is creating this problem.

I would agree that those names are dubious, and should be enclosed.

I'm not sure about the others (Contact/Origin/..), so it's safest to do them anyway.

Did you test the SQL statement in native Access?

I would assume not, as Access tends to not like joins without its own special format of brackets, which are something like this:

FROM ((t1 
       INNER JOIN t2 ON ...)
       INNER JOIN t3 ON ... )
       INNER JOIN t4 ON ...