?
Solved

Problem with Data in Recordset

Posted on 2011-03-09
3
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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