Import data from query in Access
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 |
Import data from query in Access
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 |
Import data from query in Access
Let me know if you are still having problems. I'm a litle confused at the
moment. I not a real expert with Access but I think you are still going to have problems. From what I know the Jet Engine method was developed for Excel 97, and Excel 2000. And is used with DAO objects. Ado object came out with excel 2003. I don't know if you can declare Dim cn As New ADODB.Connection using ADO and then cn.Open "Provider=Microsoft.Jet.OLEDB.4.0 (use Jet engine with a variable declared as ADODB). Let me know if it works, I'm curious. "dmoney" wrote: 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 |
Import data from query in Access
I get a Run Time Error refering to a sintax error in the FROM clause. Weird
ahhh? "dmoney" wrote: 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 |
Import data from query in Access
Still having problems. Got a Run Time Error refering to a sintax error in the
FROM clause. Weird ahhhh? "Joel" wrote: Let me know if you are still having problems. I'm a litle confused at the moment. I not a real expert with Access but I think you are still going to have problems. From what I know the Jet Engine method was developed for Excel 97, and Excel 2000. And is used with DAO objects. Ado object came out with excel 2003. I don't know if you can declare Dim cn As New ADODB.Connection using ADO and then cn.Open "Provider=Microsoft.Jet.OLEDB.4.0 (use Jet engine with a variable declared as ADODB). Let me know if it works, I'm curious. "dmoney" wrote: 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 |
All times are GMT +1. The time now is 08:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com