473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Backing up the DB before deleting all information

135 100+
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
14 1721
NeoPa
32,556 Expert Mod 16PB
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
Gilberto
135 100+
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
cyberdwarf
218 Expert 100+
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
Gilberto
135 100+
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
TSIGOS1
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
Gilberto
135 100+
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
32,556 Expert Mod 16PB
It deletes the data only :)
Sep 24 '07 #8
Gilberto
135 100+
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
32,556 Expert Mod 16PB
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
Gilberto
135 100+
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
NB. There is no facility to "Save As" on an Access database.
Sep 26 '07 #13
Gilberto
135 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

5
by: mark | last post by:
Hi I'm hoping that some could point me in the right direction for "best practice" in: 1) securely backing up a complete sql7 and 2000 server containing many databases. 2) backing up an...
3
by: war_wheelan | last post by:
I am having a problem backing up my database and TLog files due to a lack of local diskspace. The db file is about 30GB and the TLog is about 20GB each on a different hard disk. Each disk doesn't...
3
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
12
by: jim | last post by:
I'd like to get some .net sample code that shows me how to make a complete backup of a hard drive (like my C: drive) to another location (say my D: drive) while the C: drive is in use. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.