Import data from query in Access

  • Thread starter Thread starter oscar.c.marin
  • Start date Start date
O

oscar.c.marin

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
 
get rid of the quotes on this line
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"
 
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 said:
get rid of the quotes on this line
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"



oscar.c.marin said:
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
Click to expand...
Click to expand...
 
I get a Run Time Error refering to a sintax error in the FROM clause. Weird
ahhh?

dmoney said:
get rid of the quotes on this line
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"



oscar.c.marin said:
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
Click to expand...
Click to expand...
 
Still having problems. Got a Run Time Error refering to a sintax error in the
FROM clause. Weird ahhhh?

Joel said:
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 said:
get rid of the quotes on this line
rs.Open "sQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText"



oscar.c.marin said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top