By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Backing up the DB before deleting all information

100+
P: 135
Hello

I created a DB for costing and pricing lots of products (Bill of Materials) for a "project". I couldnt find the way to design the DB so that ALL the data for DIFFERENT projects could reside on the same DB. (ie. right now users start with a new project with a clean DB, empty tables, etc...for the NEXT project they will have to delete everything, tables, etc so they could start with the new project).

How can create a button which will save a backup file (with the name of the current poject of the current DB) and then clear (delete) all the information of that current DB so that users could start with a new project???

Can anyone give me some ideas?
Is there a simple way to be able to classify all the information relating to EACH project so that everything could reside on the same DB ????

Thanks,
Gilberto
Sep 20 '07 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Add in a project table to your design, then add a field in each related table for the projectID. That way, any record of any table can belong to an individual project.

All queries and code will need to be checked, however, to ensure they handle the data for the required project only. As it currently stands I expect they all work to the idea that all records can be processed as it only knows of one project.

Have a look in Normalisation and Table structures and see if you can understand what that's telling you. There is great benefit to you and your databases if you can.
Sep 20 '07 #2

100+
P: 135
Add in a project table to your design, then add a field in each related table for the projectID. That way, any record of any table can belong to an individual project.

All queries and code will need to be checked, however, to ensure they handle the data for the required project only. As it currently stands I expect they all work to the idea that all records can be processed as it only knows of one project.

Have a look in Normalisation and Table structures and see if you can understand what that's telling you. There is great benefit to you and your databases if you can.
Thanks NeoPa.

I have read the article, it is very interesting and i think i did understand it. However due to my limited experience with access i decided not to follow the "project" style by now. So what i need is just that after the current project is finished (which could last several months). The user could just press a save current project button and type the name for the file. Then another button to start a new project, that would CLEAR certain forms.

Could you tell me how to do this??? So far ive just worked with buttons but just with their default options. I dont know how to code these ones.

Thanks again,
Gilberto
Sep 24 '07 #3

Expert 100+
P: 218
Hi Gilberto,

This could get complicated (depends on your experience & understanding)

You will need to place code within the Click event of your command button. This code should:-
  1. Create the new copy of the current project;
  2. Clear the various elements (as required);
  3. Allow the user to rename objects, where necessary;
  4. Save the new project;
  5. Switch to the new project;
  6. Close the existing project.
To copy the current project, you need to the Windows API, as follows:-
Make a declaration:-
Expand|Select|Wrap|Line Numbers
  1. Public Declare Function CopyFile Lib "kernel32" _
  2. Alias "CopyFileA" (ByVal lpExistingFileName As String, _
  3. ByVal lpNewFileName As String, ByVal bFailIfExists As Long) _
  4. As Long
Use this function for the project copy:-
Expand|Select|Wrap|Line Numbers
  1. Public Function APIFileCopy(src As String, Dest As String, _
  2. Optional FailIfDestExists As Boolean) As Boolean'PURPOSE: COPY FILES
  3. 'PARAMETERS: src: Source File (FullPath)
  4.             'dest: Destination File (FullPath)
  5.             'FailIfDestExists (Optional):
  6.             'Set to true if you don't want to
  7.             'overwrite the destination file if
  8.             'it exists
  9.             'Returns (True if Successful, false otherwise)
  10. 'EXAMPLE:
  11. 'dim bSuccess as boolean
  12. 'bSuccess = APIFileCopy ("C:\MyFile.txt", "D:\MyFile.txt")
  13. Dim lRet As Long
  14. lRet = CopyFile(src, Dest, FailIfDestExists)
  15. APIFileCopy = (lRet > 0)
  16. End Function

HTH

Steve
Sep 24 '07 #4

100+
P: 135
Hi Gilberto,

This could get complicated (depends on your experience & understanding)

You will need to place code within the Click event of your command button. This code should:-
  1. Create the new copy of the current project;
  2. Clear the various elements (as required);
  3. Allow the user to rename objects, where necessary;
  4. Save the new project;
  5. Switch to the new project;
  6. Close the existing project.
To copy the current project, you need to the Windows API, as follows:-
Make a declaration:-
Expand|Select|Wrap|Line Numbers
  1. Public Declare Function CopyFile Lib "kernel32" _
  2. Alias "CopyFileA" (ByVal lpExistingFileName As String, _
  3. ByVal lpNewFileName As String, ByVal bFailIfExists As Long) _
  4. As Long
Use this function for the project copy:-
Expand|Select|Wrap|Line Numbers
  1. Public Function APIFileCopy(src As String, Dest As String, _
  2. Optional FailIfDestExists As Boolean) As Boolean'PURPOSE: COPY FILES
  3. 'PARAMETERS: src: Source File (FullPath)
  4.             'dest: Destination File (FullPath)
  5.             'FailIfDestExists (Optional):
  6.             'Set to true if you don't want to
  7.             'overwrite the destination file if
  8.             'it exists
  9.             'Returns (True if Successful, false otherwise)
  10. 'EXAMPLE:
  11. 'dim bSuccess as boolean
  12. 'bSuccess = APIFileCopy ("C:\MyFile.txt", "D:\MyFile.txt")
  13. Dim lRet As Long
  14. lRet = CopyFile(src, Dest, FailIfDestExists)
  15. APIFileCopy = (lRet > 0)
  16. End Function

HTH

Steve
Thanks for the reply Steve. Im trying to understand this and set the proper code, however im very new with access. If theres any way you could explain with simpler terms or a bit more direct coding, that would be amazing. Otherwise i will just try and try and i really appreciate the help either way.

Just one question, i can see in the code, a file beign saved is .txt, shouldnt it be .mdb???
Also could you give me an example of some code for a button to CLEAR certain tables???

Thanks,
Gilberto
Sep 24 '07 #5

P: 24
Clear the data of certain tables:
Expand|Select|Wrap|Line Numbers
  1. Public Sub DELETE_PRODUCTS_TABLE()
  2. constr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  3.          "C:\DB.mdb" & ";"
  4.  
  5. Dim con As ADODB.Connection
  6. Dim db As Database
  7. Set con = New ADODB.Connection
  8. con.ConnectionString = constr
  9. Dim recset As ADODB.Recordset
  10.  
  11.  
  12. con.Open
  13. mydb =  "C:\DB.mdb"
  14. With con
  15. Set db = OpenDatabase(mydb)
  16.  
  17. Dim v As TableDef
  18.  
  19. For Each v In db.TableDefs
  20. table_name = v.Name
  21. If table_name = "TABLE TO DELETE"  Then
  22. delstr = "DELETE * FROM " & "[" & table_name & "]" & ";"
  23. Debug.Print delstr
  24. con.Execute delstr
  25. End If
  26. Next v
  27. End With
  28. End Sub
This way it searches through all the tables and it can decide which you want to delete, so u can put wildcards like:

Expand|Select|Wrap|Line Numbers
  1.  IF LEFT(table_name ,4)="TBA1" then
Regards
Sep 24 '07 #6

100+
P: 135
CLEAR THE DATA OF CERTAIN TABLES:

Public Sub DELETE_PRODUCTS_TABLE()
constr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
"C:\DB.mdb" & ";"

Dim con As ADODB.Connection
Dim db As Database
Set con = New ADODB.Connection
con.ConnectionString = constr
Dim recset As ADODB.Recordset


con.Open
mydb = "C:\DB.mdb"
With con
Set db = OpenDatabase(mydb)

Dim v As TableDef

For Each v In db.TableDefs
table_name = v.Name
If table_name = "TABLE TO DELETE" Then
delstr = "DELETE * FROM " & "[" & table_name & "]" & ";"
Debug.Print delstr
con.Execute delstr
End If
Next v
End With
End Sub

THIS WAY IT SEARCHES THROUGH ALL THE TABLES AND IT CAN DECIDE WHICH YOU WANT TO DELETE, SO U CAN PUT WILDCARDS LIKE:

IF LEFT(table_name ,4)="TBA1" then
REGARDS
Thanks for the reply.

Im using this code to clear table "engineering". I just have two questions?
Does this code deletes the whole table or just deletes the recods?? (i only need to clear the records)
Where should i write the code? (button_click event???)

Thanks again
Sep 24 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
It deletes the data only :)
Sep 24 '07 #8

100+
P: 135
It deletes the data only :)
Thanks NeoPa, it was really useful information. Im just still struggling with the actual SAVE of the db.

thanks for your time and effort,
gilberto
Sep 25 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
If you have a database with the code in to clear itself down, you can put in a restriction such that it tests the full path of the database and only run if it is NOT equal to the value matching where you want your master database to be kept.
To copy the database originally you can simply copy the MDB file from Windows Explorer (The code method can still work but is not necessary if you prefer to copy it manually.)
One of the forms can be designed to manage the cleardown and would include stripping some tables of their data as well as (potentially) deleting some objects if that's what you need.

Is this how you envisage it working?
What, if any, problems do you have with this approach?
Sep 25 '07 #10

100+
P: 135
If you have a database with the code in to clear itself down, you can put in a restriction such that it tests the full path of the database and only run if it is NOT equal to the value matching where you want your master database to be kept.
To copy the database originally you can simply copy the MDB file from Windows Explorer (The code method can still work but is not necessary if you prefer to copy it manually.)
One of the forms can be designed to manage the cleardown and would include stripping some tables of their data as well as (potentially) deleting some objects if that's what you need.

Is this how you envisage it working?
What, if any, problems do you have with this approach?
Thanks Neo. Indeed i already have the code to clear certain tables. Your approach is right and i have no problems i was only wondering if there was a code i could run on button_click that would ask the user for the location and that will "SAVE AS" the current database (istead of manually having to copy the MDB in windows explorer).

Thanks,
Gilberto
Sep 26 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
In that case Gilberto (having chosen the more complicated approach) you don't want what I was talking about at all. You need to look at post #4 for help with that. Bear in mind you are trying to copy a file which is currently open and is actually processing the copy (unless you were to create code in a separate database to do all the copying). This is really quite unnecessarilly complex and messy, but it's your choice obviously.
Sep 26 '07 #12

NeoPa
Expert Mod 15k+
P: 31,186
NB. There is no facility to "Save As" on an Access database.
Sep 26 '07 #13

100+
P: 135
NB. There is no facility to "Save As" on an Access database.

Thanks Neo i get it. It is indeed much more complicated. I will just stick with the manual copy/paste.

Thanks for all the help,
Gilberto
Sep 27 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
No problem Gilberto.
You can automate the copying of the main database via command scripts or Windows Scripting Host scripts (VB Script; JavaScript; etc), but that's another matter and depends on your comfort in that area (Not Access related obviously).
Sep 27 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.