Open of object recordset failed

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

how u have declared the rs6 ?

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?

Show the code surounding that statement...

The SQl expression is

VB Code:

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

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...

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

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

Did you test the SQL statement in native Access?

Quote:

---

Originally Posted by engnouna

The SQl expression is

VB Code:

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.

Well i tried this

VB Code:

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

Shouldn't it be

VB Code:

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

And is BID a numeric field?

Quote:

---

Originally Posted by Shuja Ali

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.

Quote:

---

Originally Posted by Randem

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:

Code:

---

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

---