Solved

VB6 Recordset open from file issue

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Set the max value for a column 7 37
Following an example - removing duplicate strings 4 51
Add a task in Outlook from access 11 35
Passing value to a stored procedure 8 91
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

920 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

16 Experts available now in Live!

Get 1:1 Help Now