Error on connection to recordset

[oscar.c.marin]

Office 2003
I'm trying to obtain information from a query in Access from a Sub in
Excel-VBA. The code follows. I receive an error message indicating that the
recordset cannot be opened.

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 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 "tblEmployees, cn"
rngEmployees.CopyFromRecordset rs

Thank you in advance.

Oscar

 

[Mauro Gamberini]

Try:

Dim sQuery As String

sQuery = "SELECT * FROM tblEmployees"

'.....................

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

'..............................

 

[oscar.c.marin]

Try:

Dim sQuery As String

sQuery = "SELECT * FROM tblEmployees"

'.....................

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

'..............................

Thank you for taking the time. I corrected the code as per your suggestions
as follows:

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.