[RESOLVED] [Access] DB Corruptions

[JohnHamcoex]

I guess the next q would be why does your db get corrupted so often?

We have a lot of customers. It does not happen to any one database often, but we do see it about once a month over the whole customer base.

[RobDog888]

Usually corruptions occur in Access from improperly closing connections, not closing of connection(s) and multiple db technologies connected to the same db.

[JohnHamcoex]

Okay, here's another idea (and maybe this should be a different thread). I've been reading about persistent data. If I create a recordset of the one table I want to hide and then save it as a text file using:

oRs.Save "C:\Test.txt", adPersistADTG

(as you suggested in http://www.vbforums.com/showthread.p...t=export+table) and do this each time someone successfully opens the database, then I'd have a fairly current backup of the table.

My problem is I don't know how to import this back to restore the table. I think I can use oRs.Open to get the data into a recordset, but then what do I do? Do I loop through the recordset and Insert records into a restored empty table?

Thanks,

John

[RobDog888]

I'll split these posts to a new thread in the Office forum.

Yes, reading the saved table back in but may be best to use an INSERT INTO sql action statement to load the table.

[JohnHamcoex]

So, I am assuming JetComp will remove the hidden table in its entirety. Therefore, we'd replace it with an empty table and then loop through the opened recordset with INSERT INTO statements to restore all the data. Is that correct? In other words, the saved data does not save the table structure, right?

I presume using adPersistXML would be the same?

[JohnHamcoex]

After doing a lot of investigating, it seems that loading a recordset into an empty table is a non-trivial task. I could find no good examples of it anywhere.

It seems the INSERT usage (either INSERT INTO or INSERT SELECT) will require naming every field. I'd like to avoid that. Then I thought about opening a second recordset from the empty table and loop through the first recordset using ADDNEW for the table recordset, but I don't know what syntax to use to assign the values. Would I need to loop through all the fields on each record?

Any help is greatly appreciated.

John

[JohnHamcoex]

Okay, it's amazing what a good night's sleep and a big cup of coffee in the morning can do to help you figure things out. The code that works is more trivial than I thought it would be. This works:

With rsOne
     .Open App.Path & "\test.txt", "PROVIDER=MSPersist;", , , adCmdFile
     .MoveFirst
      rsTwo.Open "select * from tblTest", Conn, adOpenStatic, adLockOptimistic
   Do While .EOF = False
       rsTwo.AddNew
       For i = 0 To .Fields.Count - 1
            rsTwo.Fields(i).Value = .Fields(i).Value
       Next i
       rsTwo.Update
       .MoveNext
    Loop
   rsTwo.Close
   .Close
End With

John

[RobDog888]

I'm back John (was celebrating the 4th).

That will work but if you have alot of records it may be a performance issue.

[JohnHamcoex]

I'm back John (was celebrating the 4th).

That will work but if you have alot of records it may be a performance issue.

True. But hopefully it is a rare occurance, maybe once a month.

In any event, I think this is a pretty cool workaround for hiding db tables (as you described) for security, and having the table data backup in case a Compact and Repair deletes the table.

Works for me.

Thanks for all of your suggestions and help. You're a great resource.

John