Help with Recordset Query

  • Thread starter Thread starter Ken Snell
  • Start date Start date
I get an error "Run-time error '2147467259 (80004005)':

Syntax error in union query."

Code is below... can anyone shed some light on this problem??????
Thanks in advance.... Bob M.
+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_


Dim cnn1 As ADODB.Connection
Dim rstEmployee As ADODB.Recordset
Dim strcnn As String
Dim cmd As String

' Open a connection without using a Data Source Name (DSN).
' with this connection... multiple recordsets can be opened
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode," & _
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
 
Ken..... See replied comment on other group. Thanks... Bob



Ken Snell said:
see reply posted in other newsgroup where you posted this question.

--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
I get an error "Run-time error '2147467259 (80004005)':

Syntax error in union query."

Code is below... can anyone shed some light on this problem??????
Thanks in advance.... Bob M.
+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_


Dim cnn1 As ADODB.Connection
Dim rstEmployee As ADODB.Recordset
Dim strcnn As String
Dim cmd As String

' Open a connection without using a Data Source Name (DSN).
' with this connection... multiple recordsets can be opened
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode," & _
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
 
Back
Top