Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Question Unanswered: Having a Trauma with updating!!!

    Greets;

    I'd appreciate any kind of help where possible. I have created a large Access database (Access 2000/3), that covers 5 projects, and it regulary needs tending to.

    At present I visit the location, open the db and editing to my hearts content, the only downside to this is that it's additional travelling for me, and they often shut before my work is complete, hence another day's visiting.

    I have tried to use the import/export when I have created updated forms etc, but all it does is add it into the db with the suffix (1) at the end of the item name, instead of replacing the item completly.

    What I want to be able to do, is create the updates at home, Email them to the project, and have them click an update button, and 'et voila' The db shuts itself down and reloads with the new changes.

    I know there would be a way to do this but how??

    Thanks again for any help, it is much appriciated.

    Dan

  2. #2
    Join Date
    Jul 2005
    Posts
    50
    Why don't you make your tables .csv files or .xls files instead.
    Email them the files requiring update and give them instructions where to save the file. VOILA! Your tables will be updated!

    In the case of you updating your forms, delete your old Form before importing the new one. Try this code on for size:

    Code:
    ' A Subroutine to make a function call for the delete code
    Sub DeleteForm()
    
    Dim sSuccess As String
    
    
        sMDDBPath = "C:\Documents and Settings\y0pcq0\My Documents\mlbDev\Databases\DeleteFormTarget.mdb"
        lType = 2 ' (2 is the Form type in MS Access)
        sObjectName = "Form1"
    
        sSuccess = DeleteAccessObject(sMDDBPath, lType, sObjectName)
    
        MsgBox sSuccess
    
    End Sub
    
    ' The Function to call
    Public Function DeleteAccessObject(ByVal sMdbPath As String, _
                                      ByVal lObjectType As Long, _
                                      ByVal sObjectName As String) _
                                      As String
        
    Dim objAcc As New Access.Application
        
        On Error GoTo ErrHandler
        
        objAcc.OpenCurrentDatabase sMdbPath
        objAcc.DoCmd.DeleteObject lObjectType, sObjectName
        objAcc.CloseCurrentDatabase
        objAcc.Quit
        Set objAcc = Nothing
        DeleteAccessObject = "Success"
    ErrHandler:
        DeleteAccessObject = "Failed: " & err.Description
        Exit Function
        
    End Function
    Instead of popping up a msgbox about the status, you could even have the result emailed to you for verification, complete with any error messages!

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    The updating of the tables is not something i need worry about. The data is stored in a seperate "backend" db.

    With regards to the code, forgive me, but i'm not sure how it would integrate into the db, as when it comes to code i'm a noob.

    When i have done an 'update', i need in my exsiting db one button, that runs/manages the update.

    This button needs to find and link with the update file then replace the exsisting forms/reports in the current db with the ones contained in the update db file.

    Not sure if it is possible, but hey-ho.

    thanks again

  4. #4
    Join Date
    Jul 2005
    Posts
    50
    Perhaps I misundertood your post. Let me re-state what I think you are saying:

    It appeared to me you were saying you needed to import one or more updated forms into an Access db, but when you attempt to do the import, you get another form with an appended number.

    This ALSO occurs if you try to import a table of the same name or a query of the same name ... Access' way of protecting your data is to simply bring the table, query or form in with a slightly modified name.

    My way of overcoming your issue would be to either rename the original object and then import, or delete it and import.

    This is what the procedure I wrote for you does ... it deletes the old form to make room for the new.

  5. #5
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Thanks for the code,

    How do i implement this procedure into my DB? Would it go into the exsisting db or the new one (ie the one with the only the amended forns etc)?

    When it comes to the db users to update how would they go about it? would they click a button that would initiate this procedure, and would it know without having to put in a list of the form names in advance what it is to delete then replace? Or would it be best that it deleted all forms and replaced them all with new ones??

    thanks again;

    Dan

  6. #6
    Join Date
    Jul 2005
    Posts
    50
    I would actually prefer a VB executable that does maintenance on the Production database when you send them the revisions.

    If you don't have VB, you could create a Maint.mdb that deletes the form in the remote (Production) .mdb, then copies the form revisions you have from that Maint.mdb over to the Production.mdb.

    I would only maintain the Forms you revise.

    You can use an AutoExec macro to automate the whole process ... all they have to do is open your Maint.mdb.

    What I hate about this solution is having a maint app for someone else to fool around with. Make sure they understand your process; a written procedure would be helpful.

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    If you don't have VB, you could create a Maint.mdb that deletes the form in the remote (Production) .mdb, then copies the form revisions you have from that Maint.mdb over to the Production.mdb.

    I don't have VB, but this idea, sounds great. In effect i could create a db, with one form that the user sees, with a button (update) and the AutoExec would do the rest? The maint.mdb would have to be in the same directory (to make things less complicated as the master.mdb to ease any complications.

    I would only maintain the Forms you revise.

    This way, i think that would be easier to do as the update script would be in the maint.mdb file, rather than the master.mdb file.

    You can use an AutoExec macro to automate the whole process ... all they have to do is open your Maint.mdb.

    How would i do this? are there commands in the macro builder that would make it easy?

    What I hate about this solution is having a maint app for someone else to fool around with. Make sure they understand your process; a written procedure would be helpful.

    Yeah, that would be a good idea, they'd be informed of the update and my procedure. Also I'd make it a one button one click operation, and lock the maint.mdb database down, so they can't tamper with any of it, as i have done with the master.mdb.

    Thanks again, If you'd be able to tell me how to do the autoexec, that would be fine and it seems this solution is the answer to my problems.

  8. #8
    Join Date
    Jul 2005
    Posts
    50
    1. Go to Macros in the Object Window

    2. Click "New" in the Menu

    (Your macro is temporarily named: Macro1)

    3a. In the "Action" column, Select "RunCode"
    3b. In Action Argument "Function Name", choose the Function or Subroutine you will be executing.

    (Now, assuming you only want to run one function ... which is possible if you call all others from that function...)

    4a. Next line choose "Quit" as an "Action".
    4b. Action Arguments/Options = "Save All" (Should be the Default.)

    Now ... to get it to run automatically when the database is opened, click the Save button and name it "AutoExec".

    Ain't that wonderful!

    Just remember when you work on the .mdb to press the "SPACE" key so it doesn't run automatically, unless you want it to.

  9. #9
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    sorry to be a pain,

    I got as far as starting the new macro off, and clicked run code, and was faced with the function builder...

    I have no idea how to build functions, i am sorry, I have no idea where to go from there.

    I'm sorry to bother you for more help on this subject, as you have been a great help so far, but how do i go about creating a funtion/subroutine to open the main db, delete the necessary forms, and replace them then with the new forms form the new db?

    Thanks again;

    dan ;0)

  10. #10
    Join Date
    Jul 2005
    Posts
    50
    Code:
    ' A Subroutine to make a function call for the delete code
    Sub DeleteForm()
    
    Dim sSuccess As String
    
    
        sMDDBPath = "C:\Documents and Settings\y0pcq0\My Documents\mlbDev\Databases\DeleteFormTarget.mdb"
        lType = 2 ' (2 is the Form type in MS Access)
        sObjectName = "Form1"
    
        sSuccess = DeleteAccessObject(sMDDBPath, lType, sObjectName)
    
        MsgBox sSuccess
    
    End Sub
    
    ' The Function to call
    Public Function DeleteAccessObject(ByVal sMdbPath As String, _
                                      ByVal lObjectType As Long, _
                                      ByVal sObjectName As String) _
                                      As String
        
    Dim objAcc As New Access.Application
        
        On Error GoTo ErrHandler
        
        objAcc.OpenCurrentDatabase sMdbPath
        objAcc.DoCmd.DeleteObject lObjectType, sObjectName
        objAcc.CloseCurrentDatabase
        objAcc.Quit
        Set objAcc = Nothing
        DeleteAccessObject = "Success"
    ErrHandler:
        DeleteAccessObject = "Failed: " & err.Description
        Exit Function
        
    End Function


    ... to delete the forms, remember?

    Then, use code to do the import of forms (like you would import any other object like a query or table.

    You have to learn to think of these things as objects with the three primary members:

    1. Properties
    2. Methods
    3. Events

  11. #11
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Thanks again for your help;

    As i mentioned before, when it comes to code, i am at a loss, a complete noob. Thr frontend design with access i can cope with but the code is a whole new ball game.

    I'll not bother you anymore with this, as i really don't know where to begin, I think i'll go back to just going over there and doing things manually myself.

    The code means little to nothing to me, and i wouldn't know where to edit it or be able to make it paste the new form(s), and i don't know what any of it means.

    Thanks you very much for your time, but I'm absolutely useless.

    I don't know which db the code is meant to go into, nor how to create the other code that exports the new forms to the old db.

    Sorry for wasting your time, I thought it would be simpler and I'd be able to manage it. Once again Thanks.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    danlindley,

    Relax! The pieces of what you want/need are all here. To accomplish the task, create a new database. In that database, create a new form.

    On the new form, add a command button (cancel the wizard if it pops up). In the propeties for the button, go to Events, select "On Click" and set it to "[Event Procedure]" then click the "..." to the right side of that field.

    You are now in the "module window" behind the form you just created.

    This is where you can put the code.

    Save the form.

    Now, as for getting this to run automatically, you can do a couple of quick things. First, back in the database window, click Tools|Startup. In this menu, select your Form in the Display Form/Page option and click ok.

    This will open the form when the database is started.

    Back in the form, select the form itself (by clicking the upper left corner) so you can see the form's properties. The form has an event called "On Open" which you can put code behind following the same steps as above. Any code you put here runs when the form is opened. Since the form is opened when the DB opens, this becomes your "Automatic" code.

    The code can be very straight forward. You can essentailly use the code you get from your macro that you use now. There's a way to convert a macro to code (so you can see how it works, edit it, etc.) but alas, I don't know how to do it. It's one function I have never used. Perhaps somebody can help, or it may even be in the help file.

    Your code needs to do is connect to your DB, delete the existing object, copy the new object in and save.

    Back in your original post, using macros may work if you insert the DELETE command prior to the COPY command.

    Good luck and welcome to the forum,
    tc

  13. #13
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Woo hoo....

    Thank you very much.

    I have finally managed to get it to work, and i used a macro to copy the new parts in the updater.mdb to the exsisting after the delete command ran. It worked on my computer a treat, i just hope it works well with the new path for my client, on thier computer!

    THANK YOU BOTH VERY MUCH, IT IS HIGHLY APPRECIATED.

    Dan

  14. #14
    Join Date
    Jul 2005
    Posts
    50
    With the scope of the task you were undertaking, I thought I might be getting too detailed to take you menu-by-menu ... you're a brave fellow to dive into it. I'm glad we could help!

  15. #15
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Red face New Issues with export/import

    I think i spoke too soon.

    The script ran like a dream, however, the form (i used a splash screen form for testing the update wizard) had inbuilt event procedures, which got (how can i say) forgotten in the export process.

    this meant that when the form was run, the splashscreen didn't close itself and open the login form, therefore it basically locked down the database. It also makes the buttons not work on forms too.

    I've fixed everything now, but does anyone know why it does this and if there is anyway around it, short of removing my macros and buttons from all my forms?

Posting Permissions

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