UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Ado Copyfromrecordset Access2excel Missing Data, Access 2000    
 
   
rmoreno
post Jun 26 2013, 09:10 AM
Post#1



Posts: 52
Joined: 26-July 05
From: Texas


Hello All,
I have an unusual problem that I just can't seem to figure out. I have an Access database that I use to summarize my data with a series of querys. I have an Excel spreadsheet that I use for graphing. I have written VBA code in my Excel application that copies data from my Access query into a specific spreadsheet in Excel. My problem is that I have a field in my query that doesn't copy over into my spreadsheet. This query field (DateSaved) is a UNIX timestamp that I convert to mm/dd/yyyy hh:nn:ss and takes into account Daylight Savings Time. Here is my converstion code:
ateSaved: DateAdd("h",IIf([ConvDate] Between DLookUp("BeginDST","tbl_DST","intYear=" & Year([ConvDate])) And DLookUp("EndDST","tbl_DST","intYear=" & Year([ConvDate])),0,-1),[ConvDate])
My question is this, why doesn't this field copy over into my spreadsheet. Does it have something to do with the DLOOKUP that I use or is it something else?
Below is the VBA code I use for the data transfer into Excel:
[CODE]
Sub Access2Excel_DataCopy()
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim wks As Worksheet

'open connection to database
con.ConnectionString = "c:\Data\Access2Excel_DataCopy.mdb"
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open

'open recordset
rst.ActiveConnection = con

'open target query
sQry = "Select * From Query1"
rst.Open sQry

'set/clear/output to target worksheet
Set wks = Sheets("Access2Excel_DataCopy")
wks.Range("A5:M60000").ClearContents
wks.Range("A5").CopyFromRecordset rst
Range("A4").Select

'clean up
Set rst = Nothing
con.Close
Set con = Nothing

End Sub
[\CODE]
Go to the top of the page
 
dmhzx
post Jun 26 2013, 09:33 AM
Post#2



Posts: 7,115
Joined: 22-December 10
From: England


Some thoughts/comments:
You have checked that the query itself is creating the field? and fillng it properly ?
Is it the 'FIELD' that's not being copied, or is it going over but b;ank?
Why are you using ADO and not DAO -- I don't know if it make a difference , but I use DAO and copyfromrecordset like this, and apart from a couple of REALLY strange quriks, it is pretty relibale.
Go to the top of the page
 
rmoreno
post Jun 26 2013, 09:43 AM
Post#3



Posts: 52
Joined: 26-July 05
From: Texas


The Excel column is completely blank. No data.
Oused ADO vs DAO because that was it seems liked the best way to do the data transfer.
would love to see how to convert my code into a DAO method just to see if the missing data would show up. Can you offer a suggestion on how to do this?
Go to the top of the page
 
LPurvis
post Jun 26 2013, 10:12 AM
Post#4


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


Hi
>> The Excel column is completely blank. No data.
Fair enough, though (as asked above) what about the query itself in the Access UI and the recordset based upon said query?
When you open those - you see the field values returned as you'd expect?
Cheers
Go to the top of the page
 
rmoreno
post Jun 26 2013, 10:23 AM
Post#5



Posts: 52
Joined: 26-July 05
From: Texas


The data is there when I run the Access query. No problems.
The data is NOT there when I try to CopyFromRecordset into my spreadsheet using Excel VBA code.
eal head scratcher. Any ideas?
Go to the top of the page
 
dmhzx
post Jun 26 2013, 10:28 AM
Post#6



Posts: 7,115
Joined: 22-December 10
From: England


Make sure that DAO is in the references. - You may have to remove ADO
omment out all the ADO stuff.
Dim rst as dao.recordset
set rst = currentdb.openrecordset("Query1")
and then continue on from
'set/clear/output to target worksheet
Set wks = Sheets("Access2Excel_DataCopy")
wks.Range("A5:M60000").ClearContents
wks.Range("A5").CopyFromRecordset rst
Good luck
Go to the top of the page
 
LPurvis
post Jun 26 2013, 10:35 AM
Post#7


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


Using
>> set rst = currentdb.openrecordset("Query1")
ssumes that is the MDB in which this code is running - i.e. in the original code's:
>> "c:\Data\Access2Excel_DataCopy.mdb"
Had that been the case,
Set con = CurrentProject.Connection
would have made more sense originally.
If not (and it's not a linked table) then
set rst = OpenDatabase("c:\Data\Access2Excel_DataCopy.mdb").openrecordset("Query1")
There's nothing wrong with trying the DAO method.
On the previous reply though:
>>The data is there when I run the Access query. No problems.
>>The data is NOT there when I try to CopyFromRecordset into my spreadsheet using Excel VBA code.
There's a stage inbetween that you haven't checked.
Once opened - does the recordset contain that data in its column before you export it to Excel?
i.e.
CODE
rst.Open sQry
Debug.Print rst!DateSaved

Cheers.
Go to the top of the page
 
rmoreno
post Jun 26 2013, 10:41 AM
Post#8



Posts: 52
Joined: 26-July 05
From: Texas


I tried adding the "Debug.Print rst!DateSaved" but when I ran the code nothing seemed to happen. What is this line supposed to do?
Go to the top of the page
 
dmhzx
post Jun 26 2013, 10:46 AM
Post#9



Posts: 7,115
Joined: 22-December 10
From: England


Good points Leigh.
It hadn't occurred to me that query1 mght be somewhere else.
Oalso wonder if the 'problem' might be the line
sQry = "Select * From Query1"
The one time I had a problem with copyfromrecordset, (CFR) it was with a date field.
My original recordset was filtered (and as we know CFR ignores filters).
So I set up a new one
set rs2 = the filtered one.
That came over just fine, BUT Excel changed the format on another sheet (in the same workbook) to DATE in the same column that the rescordset had a date. - Yes only one oter sheet, and ne that my coding hadn't even looked at.
Took me hours to track that down to CFR - even after the two days it took for someon to notice.
Go to the top of the page
 
LPurvis
post Jun 26 2013, 10:48 AM
Post#10


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


>> What is this line supposed to do?
orry, that was an assumption on my part.
Hit Ctrl+G and you'll see the Immediate window. The output of that statement will appear there... (if non-Null ;-)
Go to the top of the page
 
rmoreno
post Jun 26 2013, 10:52 AM
Post#11



Posts: 52
Joined: 26-July 05
From: Texas


Thank you LPurvis, that helped (I'm a bit of a novice at code writing).
After typing CNTRL+G I ran the code. After the code ran the Immediate window showed nothing. No data. What does that mean?
ere's some additional information.
Interesting observation:
If I change my query field formula to just DateAdd("s",[Datesave],#12/31/1969 7:00:00 PM#) and remove any reference to the DLOOKUP function, then the date data shows up. Also to DMHZX's reference, Excel did change the field format to a m/d/yyyy format.
Why is that? I need to keep the DLOOKUP function to give me the correct dates and the proper formatting. Any ideas?
I hope this additional info helps.
Go to the top of the page
 
rmoreno
post Jun 26 2013, 02:42 PM
Post#12



Posts: 52
Joined: 26-July 05
From: Texas


Does anyone have any ideas on this?
Go to the top of the page
 
LPurvis
post Jun 26 2013, 03:45 PM
Post#13


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


If the immediate window showed nothing, then it implies the recordset didn't return the data.
Should there be a value in that column for *every* row of the results?
oving through the recordset would confirm it for you.
CODE
Do Until rst.EOF
    Debug.Print "DateSaved: " & rst!DateSaved
    rst.MoveNext
Loop

Trying the DAO code is still worthwhile.
Cheers.
Go to the top of the page
 
rmoreno
post Jun 27 2013, 08:31 AM
Post#14



Posts: 52
Joined: 26-July 05
From: Texas


Hi LPurvis. Just tried your latest suggestion. In the Immediate Window all I get is a bunch of "DateSaved: <blank>. Row after row of this.
o the Excel VBA module is not picking up the DateSaved content, but I can see the content when I run the query. Very confusing.
At this point I am open to any suggestions you or anyone else may have. What should I do next?
Go to the top of the page
 
dmhzx
post Jun 27 2013, 08:42 AM
Post#15



Posts: 7,115
Joined: 22-December 10
From: England


This seems to be dragging on a bit.
How about changing the query to a make table one, and then transferring the table?
At leaast you'll be able to see easily if you're getting the data OK, and can tackle whatever the real problem turns out to be.
Go to the top of the page
 
rmoreno
post Jun 27 2013, 08:46 AM
Post#16



Posts: 52
Joined: 26-July 05
From: Texas


Thought about that, but that would require that I run the Make Table query every 15 minutes. I don't want to do this.
Go to the top of the page
 
rmoreno
post Jun 27 2013, 08:49 AM
Post#17



Posts: 52
Joined: 26-July 05
From: Texas


Oh wait. I see what you are getting at DMHZX. Sorry, I misunderstood.
K. I ran a Make Table query and the DateSaved values DID show up. Does help to solve the mystery of the missing data?
Go to the top of the page
 
LPurvis
post Jun 27 2013, 08:49 AM
Post#18


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


I think we need to back up and answer some already asked questions. (That no doubt got missed or seemed to be an aside on my part.)
The application in which this code is running, is it the same as the target database?
"c:\Data\Access2Excel_DataCopy.mdb"
If so - then why the specific path and not just CurrentProject.Connection?
If you aren't, then where is this query that you've opened and tested upon? In the same application as the code?
But then how does it access those tables - are they linked tables?
If they are linked tables, then, once again, why not CurrentProject.Connection?
Your DLookUp values aren't going to be evaluated if the scope of the connection is external.
Cheers.
Go to the top of the page
 
LPurvis
post Jun 27 2013, 08:51 AM
Post#19


UtterAccess Editor
Posts: 16,339
Joined: 27-June 06
From: England (North East / South Yorks)


Make table would get around the issues I've addressed, but it leaves unanswered questions.
(The fact that it's dragging on doesn't mean that solving the incomplete puzzle isn't the best way forward.)
Go to the top of the page
 
rmoreno
post Jun 27 2013, 08:55 AM
Post#20



Posts: 52
Joined: 26-July 05
From: Texas


The path for the Access file is on my PC as I am developing the queries and the tables are linked to another Access file generated by a machine as it is doing its functions and savings its results.
The Excel file is where the VBA code resides and will be used by users who will execute an update button. This update button will then make a connection to the Access Back-End application, get the query data, save the query data into Excel spreadsheets then close the Access connection.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 11:57 PM