Access Database Manipulation

[mateo107]

In my program, I'm going to give users the option to "purge" the database of older records. So, I need the following to work behind the scenes.

I'm going to create a "duplicate" database, with the same table structures as the original, however, this database is going to have no data in it. I need the program to

a) copy this database (named templatedb.mdb) from the root of the App.Path folder, to the same location as a file named: archive.mdb

b) then, i'm going to scan the records in the current db (which I know how to code) , but how do i move the record (exactly as it exists) to the archive.mdb file and then delete it from the old database (db1.mdb)

I know how to work with recordset information (as in rs.MoveFirst, rs.edit, rs.update), but I'm not sure how to copy the database and not sure how to copy the whole record and then delete it.

ANy ideas? (BTW this is an access 97 mdb file)

[dglienna]

You want to compact the database, by creating a second version, then delete the first copy, and rename the second to the original name.
Here is the code that we run periodically.

Screen.MousePointer = vbHourglass
  ' and compact it
    DBEngine.CompactDatabase App.Path & "\templatedb.mdb", App.Path  & "\templatedb2.mdb"
  ' check that compacted version exists
  If Len(Dir(App.Path  & "\templatedb2.mdb")) > 0 Then
  ' delete original and copy compacted one to that name
  Kill App.Path  & "\templatedb.mdb"
  Do While Len(Dir(App.Path  & "\templatedb.mdb")) > 0
  DoEvents               ' wait for delete
  Loop
  
' now rename compacted version
Name App.Path  & "\templatedb2.mdb" As App.Path  & "\templatedb.mdb"
Do While Len(Dir(App.Path  & "\templatedb.mdb")) = 0
DoEvents        ' wait for copy to complete
Loop
End If
Screen.MousePointer = vbDefault

[dee-u]

You could use Action Query to quickly add and delete tons of records....

[mateo107]

DG - can you tell me what the COMPACT will do? Because, I was looking to only remove certain records that are set within the application.

I.E.,(and this part is already done), only remove records flagged as inactive. But I just don't know how to 'move' this records out.

D-- could you elaborate on the "action query"?

or, anyone else?

[dee-u]

Compact:
1. Reorganizes a table’s pages so they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database. If a table has a primary key defined, Microsoft Access copies the records in primary key order, which makes reading ahead more efficient. This produces an effect similar to clustered indexes in Microsoft SQL Server; however, unlike true clustered indexes, Microsoft Access doesn’t maintain primary key order when users add, delete, or modify records after the database is compacted. If a table doesn’t have a primary key, Microsoft Access copies records in the order that the records are stored on disk.
2. Reclaims unused space created by object and record deletions. When objects or rows are deleted from the database, the space they occupied is marked as available. However, the size of the database doesn’t shrink unless the database is compacted.
3. Resets incrementing AutoNumber fields (called Counter fields in Microsoft Access versions 2.0 and earlier) so the next value allocated will be one more than the last undeleted record.
4. Regenerates the table statistics used in the query optimization process. These statistics can become out-of-date over time, typically due to transactions that were rolled back or to the database not being closed properly.
5. Flags all queries so that they will be recompiled the next time the query is run. This is important because database statistics can change, which could cause a previously compiled query to contain inaccurate optimization information.

Search in Microsoft Access Help and Jetsql35.hlp for more on compact and action queries....

[dglienna]

I thought that compacting the database would speed up the archive process. I looked like the same code to compact, so I thought that you could incorporate it.

[kulitag]

sir, in regard of dbengine, how can i call it, error message says, object required

[dee-u]

Have you reference the Microsoft DAO 3.something library?

[dglienna]

oops. i think that's from DAO. I'll post back if I can find out for sure.

[dee-u]

Yes, dbengine is for DAO.

[RobCrombie]

Assuming one is using ADO for their DB stuff, is it risking install/run problems if they are making references to DAO ?

If so would a solution using JRO be 'safer' ?
VBCity JRO compacting

[dglienna]

Our app is working fine with both ADO and DAO.

[kulitag]

sir can i use it with ado instead? y i don't have a microsoft dao 3.0, i have downloaded the service pack 6.

[dee-u]

I also have SP6, you could use 3.6 or 3.51.

[dglienna]

I'm not sure, but I assume that you'd have to use the JRO example above if you didn't want to use DAO.

It looks like ADO, too.

Public Function CompactDB(ByRef SourceDB As String, _
                          ByRef DestDB As String, _
                          Optional ByRef Pswd As String = "") As Boolean
On Error GoTo ErrHandler
 
    Dim JRO As JRO.JetEngine
    Dim SourceCnn As String
    Dim DestCnn As String
     
    Set JRO = New JRO.JetEngine
    
    ' Kill the backup file if it currently exists
    If Dir(DestDB) <> vbNullString Then Kill DestDB
     
    ' Build the SourceConnection ConnectionString
    SourceCnn = "Provider=Microsoft.Jet.OLEDB.4.0" & _
                ";Data Source=" & SourceDB & _
                ";Jet OLEDBbig grinatabase Password=" & Pswd
    
    ' Build the DestConnection ConnectionString
    DestCnn = "Provider=Microsoft.Jet.OLEDB.4.0" & _
              ";Data Source=" & DestDB & _
              ";Jet OLEDB:Engine Type=5" & _
              ";Jet OLEDBbig grinatabase Password=" & Pswd
    
    ' Create a compacted replica
    JRO.CompactDatabase SourceCnn, DestCnn
    
    ' Kill the original and rename the replica
    Kill SourceDB
    Name DestDB As SourceDB
    CompactDB = True
    
ErrHandler:
    Set JRO = Nothing
    
    If Err.Number <> 0 Then
        CompactDB = False
        Err.Raise Err.Number, Err.Source, Err.Description
    End If
End Function

[RobCrombie]

Rob
Assuming one is using ADO for their DB stuff, is it risking install/run problems if they are making references to DAO ?

David
Our app is working fine with both ADO and DAO.

I'm not saying it can't be done.
I had an app that was all DAO, and over a period of time, I upgraded the db stuff to ADO. Thus it was running with both quite happily.
However, That was running on a pc that I had ensured was 'installed' with both.

If someone is creating an app for wide distribution, and they have taken precautions to ensure that it will install and run on various pc's using ADO.
Do they now have to take extra precautions to ensure the DAO reference will not cause problems (on some PCs) ?

[kulitag]

ei guys thanks for the time and info!

[dee-u]

Do they now have to take extra precautions to ensure the DAO reference will not cause problems

What problem for example? If it would work on other PC's then I believe it would also work on others without fear of problems....

[mateo107]

I don't mean to sound ungreatful - because what we've got going here certainly does help.. but does anyone know the code that I was looking for specifically (not compacting, etc.. although i WILL use that later on)...

I just need to create a new DB (copy an MDB file) and then copy a record and move it to the new database, and delete it from the old)...?

Thanks!

[randem]

mateo107,

If I understand you correctly... This should help. Look under the heading of Import DBase IV files into an Access 2000 Database. There is code that will help you create an Access database and tables on the fly (and copy records also).

You should be able to modify the code to get what you want.

[dglienna]

I would set up a field that you can set for records that you want to archive. Then you could just run a query and insert those records into a new table and delete them from the main table

[RobDog888]

mateo107, I need to know if your db is a multiuser db or single user db? In other words, is it on a server or netwok
share. From you Pm I gathered that if its a single db per user, you may be able to disconnect from the db, perform
a file copy of the db and rename it as Archive_MM-DD-YYYY.mdb, then reconnect to the orginal db and delete
records. After that you can disconnect from the db and do a compact and repair, re connect and your set.

Now if your a multiuser db then your going to have to lock everyone out so you can perfom thin action. Either way
they need to be out even with a filecopy.

[dee-u]

I have actually done before what you are trying to achieve now but that was in my College days....

Perhaps this might be of help....

This example selects all records in the Employees table and copies them into a new table named Emp Backup.

Sub SelectIntoX()
 
    Dim dbs As Database
    Dim qdf As QueryDef
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
 
    ' Select all records in the Employees table 
    ' and copy them into a new table, Emp Backup.
    dbs.Execute "SELECT Employees.* INTO " _
        & "[Emp Backup] FROM Employees;"
        
    ' Delete the table because this is a demonstration.
    dbs.Execute "DROP TABLE [Emp Backup];"
 
dbs.Close
 
End Sub

This is the syntax:
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

Where externaldatabase is the path to an external database. For a description of the path....

Another example....

SELECT CustomerID
FROM Customers
IN OtherDB.mdb 
WHERE CustomerID Like "A*";

[RobDog888]

Yes, but you would have to still copy over a blank database to Select records Into
Plus, if you need to copy reports/forms/etc. it makes it harder.

[dee-u]

Before when I am progamming to do a back-up of my database I would create a blank database then copy all or some those records of my tables to that blank database...

I wish mateo107 would not need to copy forms/reports....

[mateo107]

Wooohooo! Yes, now we're getting where I need to be! I'm learning so much day to day with VB, but there are still lots that I don't know (obviously)...

Thank you all for your latest responses... to respond to your inquiries...

My program is going to be distributed with the actual database (db1.mdb) as well as the "template" database, which will be empty from data, but will contain the proper tables as (same as) db1. So,

I was planning on coding my "archive" button to do just what RobDog was suggesting... Copy template.mdb (single user - unopened database) to the folder App.Path\Archive\MM-DD-YYYY_backup.mdb.

THEN I'd cycle through each record in my table to look and see if the "Archive" property was set = True. If it was, I want to copy that record to the MM-DD-YYY_backup.mdb and then delete it from db1. Then, probably I'll run a repair/compact as suggested above to clean up db1.

Based on this, do we have all the code in these posts to accomplish what I'm looking for? How would I copy and then rename template.mdb without user intervention?

Thanks so much! ! ! I'm soo very close to finish thanks to all the good people at VBForums.com! I can't wait for everyone who's helped me to see the final product!!! :-D

[RobDog888]

Instead of a blank template db you could just copy the closed db and rename it. Then in the original one you
can delete the ones marked for archiving. Then do a C&R and re connect.

Less transactions to delete the archived ones from the live db then it is to copy all the archive records to the template db.

You can use the CopyFile API. Its better then the FileCopy builtin function because it has the overwrite parameter.

Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA" (ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

[dee-u]

Or use the FileSYstemObject....

[mateo107]

Ya know, I didn't even THINK of that option! WOW! Talk about a great forum!! I love this place...

Hopefully I can get some coding done this weekend, and repost with either a RESOLVED or with my troubles early next week!

Thanks again all!

[mateo107]

Hey - I just got to thinking?

Would it be quicker/easier to just CREATE a new file (TXT file saved as a CSV File) with just the records I'd like?

Would anyone be able to help me with syntax for:

a) creating a new TXT file (no user intervention)
b) (I can get the data, say, as a string iString)
c) add iString <plus a hard line break> for each iString (how do I write iString to the new txt file?
d) save and close the txt file and rename the extention CSV...

I think this may work better for my archiving/indexing purposes.

[RobCrombie]

There is a concept called 'Persisting data' that would be more appropriate.
Basically you disconnect your rs, and save it to file.
Or you can create(fabricate) a rs, and save that to file.

MS has called it various things over the last few years.
In their zeal to purge all things VB6 off their site, it will be hard to find the examples they had. Here are a couple -
http://msdn.microsoft.com/library/de...ersistdata.asp
http://msdn.microsoft.com/library/de...ersistence.asp

Here is a link on disconnected rs- http://msdn.microsoft.com/library/de...ORecordset.asp


When googling, you can use words such as -
rst
fabricate
persist
disconnected
ADTG
There is also an XML format, for those who like overkill
(I used the word wan.ers but it gets swear word filtered)

You save the rs to file, and later you can open it back, and you have a rs in memory that you can navigate like a normal rs, etc

I think that would be a better approach than a csv file.

[RobCrombie]

Don't say I don't look after you -

Private RS_Recordset As ADODB.Recordset
Private Sub Form_Load()
    Set RS_Recordset = New ADODB.Recordset
    With RS_Recordset
        .Fields.Append "OrderID", adInteger
        .Fields.Append "TestField", adVarChar, 50
        .Fields.Refresh
        .Open
    
        .AddNew
        .Fields("OrderID") = 1
        .Fields("TestField") = "Fabricated Recordset Record 1"
        .Update
        .AddNew
        .Fields("OrderID") = 2
        .Fields("TestField") = "Fabricated Recordset Record 2"
        .Update
        
        .MoveFirst
    End With
    Dim myField As Field
    Do Until RS_Recordset.EOF
        For Each myField In RS_Recordset.Fields
            Debug.Print myField.Name & " " & myField.Value
        Next
        RS_Recordset.MoveNext
    Loop
    
    Dim FileName As String
    FileName = App.Path & "\MyData.ADTG"
    If Dir(FileName) <> "" Then
        Kill FileName
    End If
    RS_Recordset.Save FileName, adPersistADTG
    
    Dim RS_Test As ADODB.Recordset
    Set RS_Test = New ADODB.Recordset
    RS_Test.Open FileName
    
    Do Until RS_Test.EOF
        For Each myField In RS_Test.Fields
            Debug.Print myField.Name & " " & myField.Value
        Next
        RS_Test.MoveNext
    Loop
End Sub