-
Access Database Manipulation
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)
-
Re: Access Database Manipulation
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.
VB Code:
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
-
Re: Access Database Manipulation
You could use Action Query to quickly add and delete tons of records....
-
Re: Access Database Manipulation
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?
-
Re: Access Database Manipulation
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....
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
sir, in regard of dbengine, how can i call it, error message says, object required
-
Re: Access Database Manipulation
Have you reference the Microsoft DAO 3.something library?
-
Re: Access Database Manipulation
oops. i think that's from DAO. I'll post back if I can find out for sure.
-
Re: Access Database Manipulation
Yes, dbengine is for DAO.
-
Re: Access Database Manipulation
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
-
Re: Access Database Manipulation
Our app is working fine with both ADO and DAO.
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
I also have SP6, you could use 3.6 or 3.51.
-
Re: Access Database Manipulation
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.
VB Code:
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
-
Re: Access Database Manipulation
Quote:
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) ?
-
Re: Access Database Manipulation
ei guys thanks for the time and info!
-
Re: Access Database Manipulation
Quote:
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....
-
Re: Access Database Manipulation
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!
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
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
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
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.
VB Code:
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....
VB Code:
SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
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.... :D
-
Re: Access Database Manipulation
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
-
Re: Access Database Manipulation
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.
VB Code:
Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA" (ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long
-
Re: Access Database Manipulation
Or use the FileSYstemObject.... ;)
-
Re: Access Database Manipulation
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!
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
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.
-
Re: Access Database Manipulation
Don't say I don't look after you -
VB Code:
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