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
Solved

VB6 Recordset open from file issue

Posted on 2010-09-03
2
837 Views
Last Modified: 2012-05-10
Hi All,

I want to fetch data (this data rarely changes) from db server only once, and save the recordset into a file in local machine. When the data is required next time, the data should be taken from the local file. Thus the load of the db server can be reduced. This is my scenario.

My problem is...

While I run below sample code (this is not the actual code), I get the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'MyData.dat'

Private Sub Command1_Click()
    rs.Open "select * from aUnit", con, adOpenKeyset, adLockOptimistic
    rs.Save "MyData.dat", adPersistADTG
    rs.Close
    rs.Open "MyData.dat"    'Error causing line
End Sub

Whereas if i run the below code, it works.
Private Sub Command2_Click()
    rs.Open "MyData.dat"    'Works successfully
End Sub

The issue is that...
Reopening the same recordset object (that was already used to read from db) to read from data file, it does not work. It makes use of the connection already set for first open from db. How could i solve this issue?

Below code works when i created a new recordset object. But I should reuse the same recordset object, since the reference of the recordset is reused in other places of my application.

Private Sub Command1_Click()
    rs.Open "select * from aUnit", con, adOpenKeyset, adLockOptimistic
    rs.Save "MyData.dat", adPersistADTG
    rs.Close
    set rs = new ADODB.Recordset
    rs.Open "MyData.dat"    'Now works, since a new recordset object was created.
End Sub

Can you help me to reuse the same recordset object?

Thank you.
Regin

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    con.Open "mydsn", "myuser", "mypwd"
End Sub

Private Sub Command1_Click()
    Set rs = New ADODB.Recordset
    rs.Open "select * from aUnit", con, adOpenKeyset, adLockOptimistic
    rs.Save "MyData.dat", adPersistADTG
    rs.Close
    rs.Open "MyData.dat"    'Error causing line
End Sub

Private Sub Command2_Click()
    Set rs = New ADODB.Recordset
    rs.Open "MyData.dat"    'Works successfully
End Sub

Open in new window

0
Comment
Question by:Fi-es
2 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33595153
Can you try the following


Private Sub Command1_Click()
    Set rs = New ADODB.Recordset
    rs.Open "select * from aUnit", con, adOpenKeyset, adLockOptimistic
    rs.Save "C:\MyData.dat", adPersistADTG
    rs.Close
    rs.Open "C:\MyData.dat"", Options:=adCmdFile
End Sub

Open in new window

0
 

Author Comment

by:Fi-es
ID: 33595195
Thank you so much Dhaest.

Your solution solved my issue.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

839 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