Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DAO connection with bound objects; need to disconnect from DB, copy DB, then reconnect...

Posted on 2006-04-23
12
Medium Priority
?
411 Views
Last Modified: 2013-12-25
am re-writing an application which uses DAO connections and bound obects on a form. Ideally I need to keep the functionality that the DAO data object provides- of being able to quickly move through records , however, I also need to allow the user to 'back-up' or make a copy of the database file. The user should then be able to work with the original database file, but swap back to connecting to the backup if there is a mistake. It is sort of a limited 'redo'/ 'undo' function, where I am saving a copy of the entire database as the 'redo' or 'undo' to take the user to a previous 'state' of essentially the same database.

Although I have tried tracing through each step of my code and figuring out which connections are currently made to the database, and which recordsets are connected, as well as which DAO data objects themselves are currently 'connected' I am consistently getting either 'run time error 3426- action cancelled by an associated object' when I try to close the database (which I assume has something still connected to it?), or 'run time error 70- permission denied' when I try to use tempFS.MoveFile to take the database that I am using and move it to a backup location, in order to swap in the saved database.

Here is some of the code I am using:

Private Function dbUndo() As Integer
Dim tempRS As Recordset
...
     tempDB.Recordsets.Refresh
        If tempDB.Recordsets.Count > 0 Then
            For Each tempRS In tempDB.Recordsets
                tempRS.Close        << Although I see six recordsets for tempDB- after closing the first I get error 3426
            Next
        End If
 ...


Assuming I get to this point and can close the database without the 3426 error... when I try to move the db file I then get the permission denied error

       tempDB.Close
        tempFS.MoveFile DPATH$ & DBNAME$, DPATH$ & "BAK\UNRE\R\" & DBNAME$ & ".re"
 
If I manage to get through both of those problems (by commenting lines out, or temporarily putting line in during debug) I try to be careful and reset each of these connections:
       
        Set tempDB = OpenDatabase(DPATH$ & DBNAME$, False, False)
        Set DataCustomer.Recordset = tempDB.OpenRecordset("CUSTOMER", dbOpenTable)
            DataCustomer.Refresh

I would not consider myself to be a beginner or medium experience programmer, however am really starting to feel like one! I have only worked with VB4 and then VB6 for a few months while converting the above project.

I may be looking in completely the wrong direction or approaching this problem incorrectly, but I would like to able to connect, disconnect and reconnect to one of two databases, while maintaining the ability to keep the integrity of the data binding to my form objects.... is this possible?

TIA for any help you can offer....

500 points because this project is now 3 months past deadline and I finally figured I wasn't going to get it on my own!

0
Comment
Question by:derekshall
  • 4
  • 3
  • 3
10 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 1000 total points
ID: 16522032
Yes, it is possible. You need to look at disconnected recordsets and I would also suggest that you make updates and inserts using SQL syntax as opposed to ADO.Update and Insert methods.

Leon
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16524649
Leon,

>>You need to look at disconnected recordsets <<
I believe the questioner (for better or worse) is using DAO, not ADO.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16524823
Well, then he should switch, or my advise would have been totally useless. And, we can't have that now, can we? ;)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:derekshall
ID: 16527585
Wish I could switch! But am stuck with code that is running DAO and no time to change.... that will be for the next release. (For the record- all added modules to this software has been written using ADO- however the core program is now 20 years old and still running DAO) The other option I have looked at is how to identify if a recordset/ database is 'open'/'connected' then I can process the file operation immediately or close the open connections before continuing. Is there a way to do this? I have tried:

        If tempDB.Recordsets.Count > 0 Then
            For Each tempRS In tempDB.Recordsets
                tempRS.Close        << Although I see six recordsets for tempDB- after closing the first I get error 3426
            Next
        End If

but get the 3426 error. Very frustrating!

Thanks for the help so far.... hoping that there is a solution or work around here- but not too optimistic from everything I am hearing....
0
 

Author Comment

by:derekshall
ID: 16528420
As another thought- how difficult would it be to simply remove the DAO objects from the form and swap in Ado objects... then do a find replace on the names. There will be some functions that would need to be tweaked in order to work properly- though a lot are very similar if not the same (?)... but besides that is there anything else that would trip things up? Anything I would need to consider as potential nightmare scenarios?

(Bearing in mind that this is an app w/ approx 35k lines of code... not huge- but enough for a single programmer!)

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16528543
No, it won't be that easy. They have a lot of similarities but a lot of differences as well.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16530094
>>however the core program is now 20 years old and still running DAO<<
Not to quibble with you, but DAO was introduced with VB3 circa 1994.  Windows 3.0 was not even invented 20 years ago.

>>As another thought- how difficult would it be to simply remove the DAO objects from the form and swap in Ado objects<<
Don't even consider this, unless you want to pull the rest of your hair out.  Contrary to popular belief, DAO is not interchangeable with ADO.  If you do that, be prepared to re-test and if all goes well (doubtful) marvel at how much slower everything operates with ADO.  That is not because ADO is necessarily slow (another myth) it is just that developers are not prepared to redesign using ADO and instead expect it "to work just like DAO".  This is simply not the case.
0
 

Author Comment

by:derekshall
ID: 16531333
>>however the core program is now 20 years old and still running DAO<<
>>Not to quibble with you, but DAO was introduced with VB3 circa 1994.  Windows 3.0 was not even invented 20 years ago.<<

You are absolutely correct... I could have written that sentence much more clearly...
The program is OLD! Originally in DOS then evolved into vb3.. vb4... and so was using DAO. 20ish years from the programs conception I have now moved it into vb6... where theoretically I have more options! Hence the 'still running DAO'. All new code uses ADO, but I was trying to find something of a shortcut to make the old code workable with the new set of functionality that it is supposed to have, without re-writing all db interaction.

Anyway- it looks like switching from DAO to ADO is not a time efficient solution... and DAO is not playing nicely with the disconnect, copy, reconnect idea....

I am not sure where that leaves me- but as a desperately grabbing at straws type thought... is there any way to intercept the errors that are thrown and do some fancy error handling/ redirection sort of thing?



0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 16531387
>>is there any way to intercept the errors that are thrown and do some fancy error handling/ redirection sort of thing?<<
To be honest with you (and now he spills the beans) once upon a time many years ago, I used DAO and (God forbid) data controls (and boy was that a lesson), the one bug I never managed to workaround/solve was the infamous "action cancelled by an associated object".  My solution:  I told the users to ignore it and re-input any lost data!  I always attributed it to the Data Control.  But I never used Data Controls again and I never got that error either.

I am sorry to be the harbinger of bad news.

On a more constructive note, have you considered making a copy of the table instead of the database.  It might actually save you any further hair pulling and at least you would not get the "run time error 70- permission denied" error :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16531393
Also, you may be able to troubleshoot when you are leaving connections open by monitoring the existence of the *.ldb file (at least I think it is called that, it has been too long)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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 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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 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