Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with Data in Recordset

Posted on 2011-03-09
3
Medium Priority
?
280 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

609 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