Run-time error Method 'Open of Object' _Recordset' failed

[Guest]

Hello,

I get the subject error message when I run this query from Excel VBE. I am
trying to populate a combo box in Excel by connecting to an Access database.

The connection string and sql for this as follows. Appreciate all advice
given.

Sql = "SELECT DISTINCT tblUserData.[Team Leader] " & _
"FROM tblUserData " & _
"WHERE (((tblUserData.[Team Leader]) Is Not Null) AND
((tblUserData.Position) In (SELECT tblUserData.Position " & _
"FROM tblUserData " & _
"WHERE (((tblUserData.Name)=""" & strName & """))) AND
((tblUserData.Team) In (SELECT tblUserData.Team " & _
"FROM tblUserData " & _
"WHERE (((tblUserData.Name)=""" & strName & """)))) " & _
"ORDER BY tblUserData.[Team Leader];"

Debug.Print Sql

Set cn1 = CreateObject("ADODB.Connection")
cn1.Provider = "Microsoft.Jet.OLEDB.4.0"
cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
AdminFilePath & " ;Jet OLEDBatabase Password=" & AdminPwd & ";"
Set rs1 = CreateObject("ADODB.recordset")
rs1.Open Sql, cn1

Do Until rs1.EOF
Me.ComboBox5.AddItem rs1.Fields("Team Leader")
rs1.MoveNext
Loop
rs1.Close
cn1.Close
Set cn1 = Nothing
Set rrs1 = Nothing

The debug.print of the the Sql is...
SELECT DISTINCT tblUserData.[Team Leader] FROM tblUserData WHERE
(((tblUserData.[Team Leader]) Is Not Null) AND ((tblUserData.Position) In
(SELECT tblUserData.Position FROM tblUserData WHERE
(((tblUserData.Name)="John"))) AND ((tblUserData.Team) In (SELECT
tblUserData.Team FROM tblUserData WHERE (((tblUserData.Name)="John"))))
ORDER BY tblUserData.[Team Leader];

and this works ok when I run from the SQL window in Access.

Bob

 

[onedaywhen]

The debug.print of the the Sql is...
SELECT DISTINCT tblUserData.[Team Leader] FROM tblUserData WHERE
(((tblUserData.[Team Leader]) Is Not Null) AND ((tblUserData.Position) In
(SELECT tblUserData.Position FROM tblUserData WHERE
(((tblUserData.Name)="John"))) AND ((tblUserData.Team) In (SELECT
tblUserData.Team FROM tblUserData WHERE (((tblUserData.Name)="John"))))
ORDER BY tblUserData.[Team Leader];

and this works ok when I run from the SQL window in Access.

Try removing the semicolons from the body of the SQL statement e.g.

replace

)="John")))

with

)="John"))))

Jamie.

--

 

[Guest]

thanks for the response but same error when I removed the semicolon from the
body of the sql.

SELECT DISTINCT tblUserData.[Team Leader]
FROM tblUserData
WHERE (((tblUserData.[Team Leader]) Is Not Null)
AND ((tblUserData.Position) In (SELECT tblUserData.Position FROM tblUserData
WHERE (((tblUserData.Name)="John Brown"))))
AND ((tblUserData.Team) In (SELECT tblUserData.Team FROM tblUserData
WHERE (((tblUserData.Name)="John Brown")))))
ORDER BY tblUserData.[Team Leader];

If I remove the nested sql it runs ok.

 

[Stefan Hoffmann]

hi Bob,

thanks for the response but same error when I removed the semicolon from the
body of the sql.
If I remove the nested sql it runs ok.

Have you tested it as a query in Access? Your query may be too complex
for Jet.

Why don't you create a query in Access?


mfG
--> stefan <--

 

[Guest]

Have you tested it as a query in Access? Your query may be too complex
for Jet.

Why don't you create a query in Access?

thanks for the reply. Yes the query does run in access if i copy and paste
the debug.print sql to the access sql window. ok so perhaps nested queries
are too complex for jet or is there a better way to write the sql that will
be supported by jet?

i have not defined the query in access because i need to populate a combo
box depending on who the user is (strName in the where statement). is there a
way a can do this from access instead - pass the strName variable to an
access query?

regards, bob

 

[Stefan Hoffmann]

hi Bob,

thanks for the reply. Yes the query does run in access if i copy and paste
the debug.print sql to the access sql window. ok so perhaps nested queries
are too complex for jet or is there a better way to write the sql that will
be supported by jet?

Yes, try an INNER JOIN instead of your sub-queries, e.g.

SELECT DISTINCT U.[Team Leader]
FROM tblUserData U
INNER JOIN tblUserData P ON P.Position = U.Position
INNER JOIN tblUserData T ON T.Team = U.Team
WHERE NOT IsNull(U.[Team Leader])
AND U.Name =""
AND T.Name =""

A also use [Name] as it is a reserved word.


mfG
--> stefan <--

 

[Guest]

stefan, thanks for your help ...appreciate it.

got the same error with your sql. after individually writing tbe queries
found that position is a reserved word so had to square bracket it. now all
queries work.

cheers, bob