get rid of the quotes on this line
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"



"oscar.c.marin" wrote:

I rewrote my original code to include one the members suggestions as follows,
but the "cannot open the recordset" error continues to nag me. I would
appreciate any help.

Sub testAccessConn()
Application.ScreenUpdating = False
Call ObjectVarDeclare

Dim rngEmployees As Range
Set rngEmployees = vacations.Range("vacations_names").Offset(1, 0)
Dim rngEmpLastName As Range
Set rngEmpLastName =
vacations.Range("vacations_names").Offset(1, 1)
Dim intColIndex As Integer
Dim sQuery As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source" & _
"=C:\Documents and Settings\oscar\Mis Documentos\aaa-oacm\" & _
"aaa-tech_projects\planillas_project\nomina.mdb;"
'rs.Open "qryVacations, cn"
'rngEmployees.CopyFromRecordset rs



sQuery = "SELECT tblEmployees.name AS nombre, tblEmployees.lastname_1 AS
apellido1," & _
"tblEmployees.lastname_2 AS apellido2, tblJobDescrp.entryDate AS [fecha
ingreso]," & _
"Int((Now()-tblJobDescrp.entryDate)/7) AS totalWeeks," & _
"Int(((Now()-tblJobDescrp.entryDate)/7)/50) AS vacPeriods," & _
"tblVacations.init_date AS [fecha inicio], tblVacations.end_date AS [fecha
final]," & _
"tblVacations.end_date-tblVacations.init_date AS [# days]" & _
"FROM tblEmployees, tblVacations, tblJobDescrp" & _
"WHERE tblEmployees.ID1 = tblVacations.id_employee And tblEmployees.ID1 =" &
_
"tblJobDescrp.id" & _
"ORDER BY tblEmployees.lastname_1, tblEmployees.lastname_2,
tblVacations.init_date;"


rs.CursorLocation = adUseClient
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"

Nevertheless, I receive the same error that connection cannot be made
because the recordset is closed.

Thanks in advance.

Oscar