troubleshooting Question

Problem with Data in Recordset

Avatar of aus_col
aus_col asked on
Microsoft AccessMicrosoft Excel
3 Comments1 Solution295 ViewsLast Modified:
Hi

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.Recordset
Dim str_sql As String
Dim str_conn As String
Dim obj_conn As New ADODB.Connection


str_sql = "SELECT * FROM tbl_adm_stats"

str_conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & str_filename & ";"

obj_conn.ConnectionTimeout = 0
obj_conn.Open str_conn

obj_rs.CursorLocation = adUseClient

obj_rs.Open str_sql, obj_conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

Worksheets(1).Cells(int_row, 1).CopyFromRecordset obj_rs

obj_rs.Close
    
obj_conn.Close
   
Set obj_rs = Nothing
Set obj_conn = Nothing

End Sub
screen.JPG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros