Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    160

    Unanswered: MS Access BackUp Problem

    I have a database for customer orders. Once the order is complete I want to send that Customer information and Order information to History database so that I can store the customer Information. I don't want the Order and customer information in the First database once it is completed.

    Any Help!

    Thanks
    Vad
    Vad

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: MS Access BackUp Problem

    Originally posted by vad
    I have a database for customer orders. Once the order is complete I want to send that Customer information and Order information to History database so that I can store the customer Information. I don't want the Order and customer information in the First database once it is completed.

    Any Help!

    Thanks
    Vad
    Hi Vad...

    How are you deciding that "the order is complete"... Is there a Shipped Date field in your table or???

    I've done this before... In my process we wanted the records to stay in the current db for a certain amount of time... say for month-end reports or something... so I added a boolean field that gets set to True for Completed records... Then I made a select query to show the records for which Completed is true... Then I exported the query to the Backup database... and then ran a Delete query (same criteria) to drop them from the current table...

    You could do all of this in a macro... or code... Up to you...

    HTH

  3. #3
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Thank U. If the order is completed then I will send. Now I got your point.

    My doubt is, is there a way to do the addition and deletion what we r discussing automatically once the job is completed.

    Like AfterUpdate event of a combox will run a Query to Add or Delete records.

    Thanks
    Vad


    Originally posted by Trudi
    Hi Vad...

    How are you deciding that "the order is complete"... Is there a Shipped Date field in your table or???

    I've done this before... In my process we wanted the records to stay in the current db for a certain amount of time... say for month-end reports or something... so I added a boolean field that gets set to True for Completed records... Then I made a select query to show the records for which Completed is true... Then I exported the query to the Backup database... and then ran a Delete query (same criteria) to drop them from the current table...

    You could do all of this in a macro... or code... Up to you...

    HTH
    Vad

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: MS Access BackUp Problem

    Originally posted by vad
    Thank U. If the order is completed then I will send. Now I got your point.

    My doubt is, is there a way to do the addition and deletion what we r discussing automatically once the job is completed.

    Like AfterUpdate event of a combox will run a Query to Add or Delete records.

    Thanks
    Vad
    Definitely there is a way to do this...
    Create the queries needed... Save them...
    Then in the AfterUpdate event of a combo box (for example)
    add either the code to run the action queries and the export (in the correct order)... or create and save a macro to do it and execute that in the event...

    Do you need me to make a sample DB so that you can see what I mean?

  5. #5
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    That will be really great if u can send me one.

    Thanks
    Vad

    Originally posted by Trudi
    Definitely there is a way to do this...
    Create the queries needed... Save them...
    Then in the AfterUpdate event of a combo box (for example)
    add either the code to run the action queries and the export (in the correct order)... or create and save a macro to do it and execute that in the event...

    Do you need me to make a sample DB so that you can see what I mean?
    Vad

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: MS Access BackUp Problem

    Originally posted by vad
    That will be really great if u can send me one.

    Thanks
    Vad
    No problem at all Vad... I'm almost done the sample dbs...

    There are just so many ways to do this that I'm trying not to make myself do more than one sample... lol

  7. #7
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Thank u. I appreciate your help!

    Vad.

    Originally posted by Trudi
    No problem at all Vad... I'm almost done the sample dbs...

    There are just so many ways to do this that I'm trying not to make myself do more than one sample... lol
    Vad

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: MS Access BackUp Problem

    Originally posted by vad
    Thank u. I appreciate your help!

    Vad.
    Here you go... Sorry it took so long... They seem to want to make me work today!! ... Imagine that... lol

    Make sure you read the Word document first...
    HTH...
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Oh! That happens some times.
    Thank u ver much for ur patience and for ur help. I will try with this.

    Vad

    Originally posted by Trudi
    Here you go... Sorry it took so long... They seem to want to make me work today!! ... Imagine that... lol

    Make sure you read the Word document first...
    HTH...
    Vad

  10. #10
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Hi! Trudi,

    The sample u have sent to me dealt with one table. Here I have 4 tables. I am strugling with all these four tables. How can I send all the four tables information to the history database.

    Do i need to send each table independently. All the 4 tables are related to each other. Later I must able to retrieve the information from the history database.

    While I was trying to make a table using make table query and Import that one to History database as a table it is saying that "Customer ID Appears several times"

    Any help appreciated.

    Thanks
    vad

    Originally posted by vad
    Oh! That happens some times.
    Thank u ver much for ur patience and for ur help. I will try with this.

    Vad
    Vad

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: MS Access BackUp Problem

    Originally posted by vad
    Hi! Trudi,

    The sample u have sent to me dealt with one table. Here I have 4 tables. I am strugling with all these four tables. How can I send all the four tables information to the history database.

    Do i need to send each table independently. All the 4 tables are related to each other. Later I must able to retrieve the information from the history database.

    While I was trying to make a table using make table query and Import that one to History database as a table it is saying that "Customer ID Appears several times"

    Any help appreciated.

    Thanks
    vad
    Hi Vad...

    Yes, you'll need to do the same type of thing with each table... Personally (like I said in my readme file) I would just create the backup database and link the tables within to the main database so that I could run the append and delete queries (or SQL in code) from the main one... It'd save at least a bit of Automation code... But it's up to you...

    Since there are relationships involved, make sure you append the records to the tables in the right order... If you don't, you'll get error messages saying "Can't append ... no related records in primary table..."... or something like that... lol... Just think it through... For example, you'd have to have appended the customer before you could append order records for that customer... Make sense?

    Check that you are not trying to append records that will duplicate unique values... If the customer ID is a primary key, you won't be able to add two records with the same customer number... Make sure you're analyzing your needs thoroughly before getting into actually doing the backup...

    I don't know how normalized your database is... so it's hard for me to get into specifics...

    HTH

  12. #12
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Just now I got the history database working. Thanks for ur help and the sample database.

    If I got struck any where I will ask you.

    Thanks

    Vad

    Originally posted by Trudi
    Hi Vad...

    Yes, you'll need to do the same type of thing with each table... Personally (like I said in my readme file) I would just create the backup database and link the tables within to the main database so that I could run the append and delete queries (or SQL in code) from the main one... It'd save at least a bit of Automation code... But it's up to you...

    Since there are relationships involved, make sure you append the records to the tables in the right order... If you don't, you'll get error messages saying "Can't append ... no related records in primary table..."... or something like that... lol... Just think it through... For example, you'd have to have appended the customer before you could append order records for that customer... Make sense?

    Check that you are not trying to append records that will duplicate unique values... If the customer ID is a primary key, you won't be able to add two records with the same customer number... Make sure you're analyzing your needs thoroughly before getting into actually doing the backup...

    I don't know how normalized your database is... so it's hard for me to get into specifics...

    HTH
    Vad

  13. #13
    Join Date
    Aug 2003
    Posts
    160

    Re: MS Access BackUp Problem

    Append Query is working fine but After appending the query, while I am trying to delete that record from the current database it is giving me error message Like "could not delete from specified tables ms access ".

    Any Help!

    Thanks
    Vad



    Originally posted by vad
    Just now I got the history database working. Thanks for ur help and the sample database.

    If I got struck any where I will ask you.

    Thanks

    Vad
    Vad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •