Domc
asked on
Problem using CopyFromRecordset command
Access Error: Class does not support automation or does not support expected interface
Sub PasteNewGeogData()
Dim xlWB As Object
Dim strPath As String
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim fieldValue As Long
Dim xLSheet As Variant
Dim qdf As DAO.QueryDef
On Error GoTo After_Error
strPath = "\\Mac\AAIM\Portfolios\"
strFileName = "GraphAccessGeogData.xlsx"
Set xlWB = Nothing
If IsValidOLELinkEstablished(
'Set worksheet
Set xLSheet = xlWB.Sheets("Sheet2")
xlWB.Application.Calculati
xlWB.Application.Screenupd
'Get reference to database and relevant query
Set db = CurrentDb
Set qdf = CurrentDb.QueryDefs("qry_U
qdf.Parameters("TempVars!H
Set rs = qdf.OpenRecordset()
xLSheet.range("A1").copyfr
Sub PasteNewGeogData()
Dim xlWB As Object
Dim strPath As String
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim fieldValue As Long
Dim xLSheet As Variant
Dim qdf As DAO.QueryDef
On Error GoTo After_Error
strPath = "\\Mac\AAIM\Portfolios\"
strFileName = "GraphAccessGeogData.xlsx"
Set xlWB = Nothing
If IsValidOLELinkEstablished(
'Set worksheet
Set xLSheet = xlWB.Sheets("Sheet2")
xlWB.Application.Calculati
xlWB.Application.Screenupd
'Get reference to database and relevant query
Set db = CurrentDb
Set qdf = CurrentDb.QueryDefs("qry_U
qdf.Parameters("TempVars!H
Set rs = qdf.OpenRecordset()
xLSheet.range("A1").copyfr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dale Fye
xlSheet should be declared as Object, not Variant.
ASKER
Thanks for help. Changing xlSheet to Object had no effect.
Taking off the parentheses changes the error to
"No current record" - at exactly the same place
Taking off the parentheses changes the error to
"No current record" - at exactly the same place
still, xlSheet should be declared as an object.
re:
should this line be:
You might want to put a page break on the line after that, and check the value of Tempvars!HASelected.
Then run the subsequent line of code (F8) to create the recordset and in the immediate window try:
re:
qdf.Parameters("TempVars!HASelected") = TempVars("HASelected").Valueshould this line be:
qdf.Parameters("[HASelected]") = TempVars!HASelected.ValueYou might want to put a page break on the line after that, and check the value of Tempvars!HASelected.
Then run the subsequent line of code (F8) to create the recordset and in the immediate window try:
?rs.BOF
?rs.EOFrs.MoveLast
?rs.RecordCount
rs.MoveFirstASKER
Yes rs definitely works. I'd actually already inserted a break point, copied the rs variable into the watch window and you can see it has a record count of 51 - which is exactly as it should be. So rs is a definite recordset - that's not the issue. I've attached a breakdown of its properties in case of help.
Screenshot-2019-08-09-at-18.34.35.jpg
Screenshot-2019-08-09-at-18.34.35.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Daniel,
I think that's that was suggested above - i.e. just removing the brackets.
That just changes the error into - "no current record"
Thanks for the suggested function but would prefer to fix mine if at all possible as don't think it's far off.
I think that's that was suggested above - i.e. just removing the brackets.
That just changes the error into - "no current record"
Thanks for the suggested function but would prefer to fix mine if at all possible as don't think it's far off.
ASKER
My apologies - removing the brackets did solve it - although not sure I understand why as the documentation on that function showed brackets.
It must have been code on the next line that caused the no current record error. Thanks to all
It must have been code on the next line that caused the no current record error. Thanks to all
You're welcome Domc!