Link to home
Create AccountLog in
Avatar of aus_col
aus_col

asked on

Problem with Data in Recordset

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

Open in new window

screen.JPG
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of aus_col
aus_col

ASKER

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!!
:-)

It happens to all of us from time to time. I tend to lose my sunglasses on the top of my head!