B
bobm
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
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