try this
Sub testAccessConn()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Application.ScreenUpdating = False
'Call ObjectVarDeclare


Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)


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
[fechaingreso]," & _
"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
[fechafinal]," & _
"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

End Sub

"oscar.c.marin" wrote:

Norman Jones need not answer directing to erlandsen page.

I need to obtain the resulting data from a query and paste into Excel. The
following procedure results in an error saying something like: The connection
cannot be used to make this operation. It is closed or is not valid in this
context.

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"

TIA.