?
Solved

VB6 Recordset open from file issue

Posted on 2010-09-03
2
Medium Priority
?
880 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 2000 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

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

830 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