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

Access 2000 visual basic code runs too fast for backup to complete

P: 92
How do,
For backing up my database(Access 2000), i am trying to just back up the tables and their data, as this will save on space and allow for importing back into the actual database. I want the data copied into a dated backup database so a specific restore can be performed and old backups arent lost.

The vb code i have makes a new blank database and copies the data into tables through docmd.runSQL. Both aspects of the code work fine; the problem arises when i want to do both in one go. The time it takes to create the blank database is far greater than the time the vb code takes to execute, resulting in
"Run-Time error '3045':
Could not use 'C:\Docu.....Backup 2007-02-15 22.16.55.mdb'; file already in use."

because the new database is still being created, so the docmd.runSQL cannot be executed.

Is there any way to make the code 'wait' until the new database has finished being created? Also is it possible to override the confirmation messages that popup when copying over the data, because i want this to be an automatic process - ie: Invisible to the user - and 6 confirmation messages dont help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTableBackup_Click()
  2. Dim strDB As String
  3. Dim SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails As String
  4. Dim appAccess As Access.Application
  5.  
  6.     strDB = CurrentProject.Path & "\Backup " & Format(Now, "YYYY-MM-DD HH.nn.ss") & ".mdb"
  7.  
  8.     Set appAccess = CreateObject("Access.Application")
  9.     appAccess.NewCurrentDatabase strDB
  10.  
  11. SQLBackuptblBookings = "Select * Into tblBookingsBackup IN '" & strDB & "' " & "FROM tblBookings "
  12. SQLBackuptblOutingDetails = "Select * Into tblOutingDetailsBackup IN '" & strDB & "' " & "FROM tblOutingDetails "
  13. SQLBackuptblPersonalDetails = "Select * Into tblPersonalDetailsBackup IN '" & strDB & "' " & "FROM tblPersonalDetails "
  14. DoCmd.RunSQL SQLBackuptblBookings
  15. DoCmd.RunSQL SQLBackuptblOutingDetails
  16. DoCmd.RunSQL SQLBackuptblPersonalDetails
  17.  
  18. End Sub
Code ends on the last line "End Sub"

If I copy the tables into a database that already exists, then there's no problem. The need for individual backups at every deletion requires a new database for each backup, which creates the problem.

All help is much appreciated, as well as other ideas to get around this problem.

NDayave
Feb 15 '07 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,597
How do,
For backing up my database(Access 2000), i am trying to just back up the tables and their data, as this will save on space and allow for importing back into the actual database. I want the data copied into a dated backup database so a specific restore can be performed and old backups arent lost.

The vb code i have makes a new blank database and copies the data into tables through docmd.runSQL. Both aspects of the code work fine; the problem arises when i want to do both in one go. The time it takes to create the blank database is far greater than the time the vb code takes to execute, resulting in
"Run-Time error '3045':
Could not use 'C:\Docu.....Backup 2007-02-15 22.16.55.mdb'; file already in use."

because the new database is still being created, so the docmd.runSQL cannot be executed.

Is there any way to make the code 'wait' until the new database has finished being created? Also is it possible to override the confirmation messages that popup when copying over the data, because i want this to be an automatic process - ie: Invisible to the user - and 6 confirmation messages dont help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTableBackup_Click()
  2. Dim strDB As String
  3. Dim SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails As String
  4. Dim appAccess As Access.Application
  5.  
  6.     strDB = CurrentProject.Path & "\Backup " & Format(Now, "YYYY-MM-DD HH.nn.ss") & ".mdb"
  7.  
  8.     Set appAccess = CreateObject("Access.Application")
  9.     appAccess.NewCurrentDatabase strDB
  10.  
  11. SQLBackuptblBookings = "Select * Into tblBookingsBackup IN '" & strDB & "' " & "FROM tblBookings "
  12. SQLBackuptblOutingDetails = "Select * Into tblOutingDetailsBackup IN '" & strDB & "' " & "FROM tblOutingDetails "
  13. SQLBackuptblPersonalDetails = "Select * Into tblPersonalDetailsBackup IN '" & strDB & "' " & "FROM tblPersonalDetails "
  14. DoCmd.RunSQL SQLBackuptblBookings
  15. DoCmd.RunSQL SQLBackuptblOutingDetails
  16. DoCmd.RunSQL SQLBackuptblPersonalDetails
  17.  
  18. End Sub
Code ends on the last line "End Sub"

If I copy the tables into a database that already exists, then there's no problem. The need for individual backups at every deletion requires a new database for each backup, which creates the problem.

All help is much appreciated, as well as other ideas to get around this problem.

NDayave
You can use the Sleep() API Function to pause program execution for a specified number of milliseconds but I can make no promises. Here is the Declaration and Call. Let me know how you make out - I'll keep an eye on this Post.
Expand|Select|Wrap|Line Numbers
  1. Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  2.  
  3. Sleep (6000)      'suspends Program execution for 6 seconds
Feb 16 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
  1. Store an empty (template) database in the destination folder.
  2. Using FileSystemObject.CopyFile() copy your template database to a new (correctly named) file.
This should now be in a position to handle your SQL commands. It should also enable you to save various settings in the template file that might otherwise be more complicated to set up.
Feb 16 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
You may also want to check in Application Automation to help you tidy up your automation code.
Feb 16 '07 #4

P: 92
You may also want to check in Application Automation to help you tidy up your automation code.
I have changed the code to copy a blank template then copy the tables and it works a treat:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTableBackup_Click()
  2. Dim fso, fs, Src, Dst, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails As String
  3.  
  4.     Src = CurrentDb.Name
  5.     Blnk = CurrentProject.Path & "\Backup\Backup Blank.mdb"
  6.     Dst = CurrentProject.Path & "\Backup\Backup " & Format(Now, "YYYY-MM-DD HH.nn.ss") & ".mdb"
  7.  
  8.     Set fso = CreateObject("Scripting.FileSystemObject")
  9.     Set fs = fso.GetFolder(CurrentProject.Path & "\Backup")
  10.     fso.CopyFile Blnk, Dst
  11.  
  12.  
  13. SQLBackuptblBookings = "Select * Into tblBookingsBackup IN '" & Dst & "' " & "FROM tblBookings "
  14. SQLBackuptblOutingDetails = "Select * Into tblOutingDetailsBackup IN '" & Dst & "' " & "FROM tblOutingDetails "
  15. SQLBackuptblPersonalDetails = "Select * Into tblPersonalDetailsBackup IN '" & Dst & "' " & "FROM tblPersonalDetails "
  16. DoCmd.RunSQL SQLBackuptblBookings
  17. DoCmd.RunSQL SQLBackuptblOutingDetails
  18. DoCmd.RunSQL SQLBackuptblPersonalDetails
  19.  
  20. End Sub
I've looked at the Application Automation, I think i understand what's goign on, but i have no idea where to put the .Visible part into my code.

I know its best to try to learn myself, but im totally lost on this one.
Thanks for the advice,

NDayave
Feb 16 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
Your present code doesn't look as if it's using Application Automation any more :confused:
Generally, it's a better idea to build the code from the tutorial rather than try to apply the tutorial to existing code. That way you don't propagate any existing mistakes.
Feb 16 '07 #6

P: 92
Your present code doesn't look as if it's using Application Automation any more :confused:
Generally, it's a better idea to build the code from the tutorial rather than try to apply the tutorial to existing code. That way you don't propagate any existing mistakes.

I changed the code to copy the blank database, rename and then copy the tables into it. If i stick with creating a new blank database then the original problem persists. Is there no other way to override the confirmation messages?
Feb 16 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Can you post your current code and try to explain clearly exactly what is going wrong at which point, then I can look at finding a way through for you.
Feb 17 '07 #8

P: 92
My original code that created the new blank database was:
Expand|Select|Wrap|Line Numbers
  1. 1)Private Sub cmdTableBackup_Click()
  2. 2)Dim strDB As String
  3. 3)Dim SQLBackuptblBookings, SQLBackuptblOutingDetails, 4)SQLBackuptblPersonalDetails As String
  4. 5)Dim appAccess As Access.Application
  5. 6)
  6. 7)     strDB = CurrentProject.Path & "\Backup " & Format(Now, "YYYY-MM-DD HH.nn.ss") & ".mdb"
  7. 8)
  8. 9)     Set appAccess = CreateObject("Access.Application")
  9. 10)    appAccess.NewCurrentDatabase strDB
  10. 11)
  11. 12)SQLBackuptblBookings = "Select * Into tblBookingsBackup IN '" & strDB & "' " & "FROM tblBookings "
  12. 13)SQLBackuptblOutingDetails = "Select * Into tblOutingDetailsBackup IN '" & strDB & "' " & "FROM tblOutingDetails "
  13. 14)SQLBackuptblPersonalDetails = "Select * Into tblPersonalDetailsBackup IN '" & strDB & "' " & "FROM tblPersonalDetails "
  14. 15)DoCmd.RunSQL SQLBackuptblBookings
  15. 16)DoCmd.RunSQL SQLBackuptblOutingDetails
  16. 17)DoCmd.RunSQL SQLBackuptblPersonalDetails
  17. 18)
  18. 19)End Sub
Line:
7) State variable strDB - File Name and Address
9) and 10) Create new blank database at stated Address
12) - 14) State SQL statements to copy over all 3 tables
15) - 17) Run stated SQL statements
19) End



This was then changed to copy a blank database rather than create a new one, which solved the initial problem of not being able to copy over tables:

Expand|Select|Wrap|Line Numbers
  1. 1)Private Sub cmdTableBackup_Click()
  2. 2)Dim fso, fs, Dst, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails As String
  3. 3)
  4. 4)
  5. 5)    Blnk = CurrentProject.Path & "\Backup\Backup Blank.mdb"
  6. 6)    Dst = CurrentProject.Path & "\Backup\Backup " & Format(Now, "YYYY-MM-DD HH.nn.ss") & ".mdb"
  7. 7)
  8. 8)    Set fso = CreateObject("Scripting.FileSystemObject")
  9. 9)    Set fs = fso.GetFolder(CurrentProject.Path & "\Backup")
  10. 10)   fso.CopyFile Blnk, Dst
  11. 11)
  12. 12)
  13. 13)SQLBackuptblBookings = "Select * Into tblBookingsBackup IN '" & Dst & "' " & "FROM tblBookings "
  14. 14)SQLBackuptblOutingDetails = "Select * Into tblOutingDetailsBackup IN '" & Dst & "' " & "FROM tblOutingDetails "
  15. 15)SQLBackuptblPersonalDetails = "Select * Into tblPersonalDetailsBackup IN '" & Dst & "' " & "FROM tblPersonalDetails "
  16. 16)DoCmd.RunSQL SQLBackuptblBookings
  17. 17)DoCmd.RunSQL SQLBackuptblOutingDetails
  18. 18)DoCmd.RunSQL SQLBackuptblPersonalDetails
  19. 19)
  20. 20)End Sub

Line:

5) State directory of the Blank database to be copied
6) State Name and Address of where to copy database to
8) - 10) Copy blank database and rename
13) - 15) State SQL statements to copy over all 3 tables
16) - 18) Run stated SQL statements
20) End


Although the new code has solved the problem of not being able to copy over the tables as the database to be copied to was still in use, I still receive several confirmation boxes to copy over the tables.

Would it be beneficial to stick with the original create new database code and add in the Sleep function mentioned by ADezii in post #2, which should also allow the automation coding to get rid of the confirmation boxes?

NDayave
Feb 17 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Would it be beneficial to stick with the original create new database code and add in the Sleep function mentioned by ADezii in post #2, which should also allow the automation coding to get rid of the confirmation boxes?
No!
Now you've explained what your problem is I think we can easily fix that.
Before running any of the action queries simply execute :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
When they're all finished remember to execute :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(True)
This won't block proper error messages, only the confirmation questions etc.
Feb 17 '07 #10

P: 92
with the original code the problem was in creating a new blank database. The code got to 'copy over the tables and data' before the new database was finished being created.

The code was then changed to copy a pre-created blank database, rename the copy and then copy the tables and their data into it. This work fine for copying over the tables.

The problem with the new code, is that apparently i cannot use 'automation' to get rid of the confirmation of copying tables and data, so the user would have to click ok several times before the transfer was complete.

I would like to know if there is another way around this, or that i should stay with the original code that creates a new database rather than copying, and using the sleep function mentioned earlier in the thread, which according to you supports the automation and would therefore get rid of the confirmations.

I hope this explains the problem in enough detail

NDayave
Feb 17 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
Your earlier explanation was fine in fact.
You may have missed it but I posted a reply just as you were creating your last post. I'm confident this is the correct solution for you :)
Feb 17 '07 #12

P: 92
Thats exactly what ive been looking for, thanks a lot. Im sure ill have future problems to keep you busy, thanks again

NDayave
Feb 18 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
You don't know how reassuring that is ;)
Glad to hear you got it sorted anyway.
Feb 18 '07 #14

Post your reply

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