I have been trying to figure something out all day. I have some code in Excel which pulls data from a table in Access and using CopyFromRecordset puts the data on a worksheet.
The really frustrating thing - is the recordset contains different values from the access table. I have tried using DAO instead, and casting the numbers to integer when updating (in case of sneaky decimal places) but to no avail.
I also looped through the recordset and the values in the actual recordset were incorrect.
Anybody got any ideas?
Thanks
The code is below. I have also attached the results from the query
Private Sub get_data(ByVal str_res As String, ByVal int_row As Integer)Dim obj_rs As New ADODB.RecordsetDim str_sql As StringDim str_conn As StringDim obj_conn As New ADODB.Connectionstr_sql = "SELECT * FROM tbl_adm_stats"str_conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & str_filename & ";"obj_conn.ConnectionTimeout = 0obj_conn.Open str_connobj_rs.CursorLocation = adUseClientobj_rs.Open str_sql, obj_conn, adOpenDynamic, adLockOptimistic, adCmdTableDirectWorksheets(1).Cells(int_row, 1).CopyFromRecordset obj_rsobj_rs.Closeobj_conn.CloseSet obj_rs = NothingSet obj_conn = NothingEnd Sub
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
i am an idiot - reading a file from a network drive which has been re-mapped to a different server. d'oh!!! sometimes need someone to state the obvious. thank you!!
zorvek (Kevin Jones)
:-)
It happens to all of us from time to time. I tend to lose my sunglasses on the top of my head!
Your help has saved me hundreds of hours of internet surfing.