Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Data from Access query | Excel Discussion (Misc queries) | |||
Import Data from Access/Query Criteria Not Enforced in Output | Excel Programming | |||
Import Query from Access - Bug | Excel Programming | |||
vba code to import access query data into excel spreadsheet | Excel Programming | |||
import data from an Access parameter query | Excel Discussion (Misc queries) |