Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on connection to recordset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on connection to recordset
Try:
Dim sQuery As String sQuery = "SELECT * FROM tblEmployees" '..................... rs.CursorLocation = adUseClient rs.Open sQuery, _ cn, adOpenKeyset, _ adLockOptimistic, _ adCmdText '.............................. -- --------------------------- Mauro Gamberini http://www.riolab.org/ "oscar.c.marin" ha scritto nel messaggio ... 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;" Thank you in advance. Oscar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on connection to recordset
"Mauro Gamberini" wrote:
Try: Dim sQuery As String sQuery = "SELECT * FROM tblEmployees" '..................... rs.CursorLocation = adUseClient rs.Open sQuery, _ cn, adOpenKeyset, _ adLockOptimistic, _ adCmdText '.............................. -- --------------------------- Mauro Gamberini http://www.riolab.org/ "oscar.c.marin" ha scritto nel messaggio ... 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;" Thank you in advance. Oscar 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTable connection using ADO Recordset | Excel Programming | |||
ADO Connection vs Recordset objects. | Excel Programming | |||
Disconencted recordset for DSN less connection | Excel Programming | |||
Connection Recordset Loop Problem with VBA - Help | Excel Programming | |||
VBA excel - problem with having clause in sql with adodb.connection/recordset | Excel Programming |