Retrieve result of query from Access

O

oscar.c.marin

Norman Jones need not answer directing to erlandsen page.

I need to obtain the resulting data from a query and paste into Excel. The
following procedure results in an error saying something like: The connection
cannot be used to make this operation. It is closed or is not valid in this
context.

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"

TIA.
 
M

Mike

try this
Sub testAccessConn()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Application.ScreenUpdating = False
'Call ObjectVarDeclare


Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)


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
[fechaingreso]," & _
"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
[fechafinal]," & _
"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

End Sub

oscar.c.marin said:
Norman Jones need not answer directing to erlandsen page.

I need to obtain the resulting data from a query and paste into Excel. The
following procedure results in an error saying something like: The connection
cannot be used to make this operation. It is closed or is not valid in this
context.

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"

TIA.
Click to expand...
 
O

oscar.c.marin

Mike said:
try this
Sub testAccessConn()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Application.ScreenUpdating = False
'Call ObjectVarDeclare


Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)


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
[fechaingreso]," & _
"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
[fechafinal]," & _
"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

End Sub

oscar.c.marin said:
Norman Jones need not answer directing to erlandsen page.

I need to obtain the resulting data from a query and paste into Excel. The
following procedure results in an error saying something like: The connection
cannot be used to make this operation. It is closed or is not valid in this
context.

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"

TIA.
Click to expand...
Click to expand...


Thanks Mike. Let's try a simpler approach by calling a query already
existing in the database, and proven to work correctly in Access supplying
the data as requested.

The query name is "qryVacations".

The code - which does not work and gives the same resulting prompt that the
connection cannot be used to perform the operation because it's closed or not
valid in this context appears - is simpler taking advantage of the
CopyFromRecordSet method, follows:

Sub testAccessConn_2()
Application.ScreenUpdating = False
Call ObjectVarDeclare

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)

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" ====> Error marked here
rngEmployees.CopyFromRecordset rs

End Sub

TIA
 
M

Mike

This is what i have used
Private Sub moveProduct()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim rowNumber As Long

'C:\PathToYourMdb\Ilsa.mdb (Change)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\PathToYourMdb\Ilsa.mdb;Persist Security Info=False"
'sSQL = "Replace with your query"
sSQL = "SELECT Field1, Field2 From TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
rowNumber = 2 'Starting Row Number for data
Do While (Not rs.EOF)
Range("A" & rowNumber) = rs.Fields("Field1").Value
Range("B" & rowNumber) = rs.Fields("Field2").Value
rowNumber = rowNumber + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

oscar.c.marin said:
Mike said:
try this
Sub testAccessConn()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Application.ScreenUpdating = False
'Call ObjectVarDeclare


Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)


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
[fechaingreso]," & _
"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
[fechafinal]," & _
"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

End Sub

oscar.c.marin said:
Norman Jones need not answer directing to erlandsen page.

I need to obtain the resulting data from a query and paste into Excel. The
following procedure results in an error saying something like: The connection
cannot be used to make this operation. It is closed or is not valid in this
context.

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"

TIA.
Click to expand...
Click to expand...


Thanks Mike. Let's try a simpler approach by calling a query already
existing in the database, and proven to work correctly in Access supplying
the data as requested.

The query name is "qryVacations".

The code - which does not work and gives the same resulting prompt that the
connection cannot be used to perform the operation because it's closed or not
valid in this context appears - is simpler taking advantage of the
CopyFromRecordSet method, follows:

Sub testAccessConn_2()
Application.ScreenUpdating = False
Call ObjectVarDeclare

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sQuery As String
Dim rngEmployees As Range
Dim rngEmpLastName As Range
Dim intColIndex As Integer

Set rngEmployees = Range("vacations_names").Offset(1, 0)
Set rngEmpLastName = Range("vacations_names").Offset(1, 1)

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" ====> Error marked here
rngEmployees.CopyFromRecordset rs

End Sub

TIA
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import data from query in Access 4
Error on connection to recordset 2

Top