Solved

VB6 Recordset open from file issue

Posted on 2010-09-03
2
821 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…

746 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

13 Experts available now in Live!

Get 1:1 Help Now