Solved

Problem with Data in Recordset

Posted on 2011-03-09
3
272 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:aus_col
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35089731
The obvious first question: Are you absolutely certain you are pulling from the same Access file in the code that you are viewing in Access?

Kevin
0
 

Author Comment

by:aus_col
ID: 35089756
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!!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35089762
:-)

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now