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 OLEDB:Database 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