Solved

VB6 Recordset open from file issue

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best way to parse out a json string in VB6? 10 172
Exit a vb6 apps when a calling it apps closes 15 49
CROSS APPLY 4 51
SQL query with cast 38 43
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

803 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