By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,851 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.

Open Access mdb from mdb delete error

P: 78
Ok, I wish there was a more clear way to word my problem in one sentence, but there isn't. Let me explain what I am trying to do.

I have a db that I am constantly doing updates to. I send it out to different people. Here is the problem. With each update it has a lot of blank information that is person specific. What I want to do is have the file automatically update the information from the db they are using to the new one, remove the old and replace with this one.

I have that basically figured out. In order for this work I have an "Update" db. The purpose of this db is simply to rename the db to the original name. I realized this needs to happen since you cannot rename when it is open.

I have a procedure that does the update and opens the "Update" db, then closes itself out. I have the "Update" db set to load and rename the other db to the right name, then open the db, and close itself out.

Then the newly named db opens and deletes the "Update" db. Leaving only the newly named db behind.

The problem is that when the code runs to open the "Update" db and close, the "Update" db code runs before the other db can close...not allowing it to be renamed. Same goes for deleting "Update" db after rename is complete.

I need to know if there is some way to slow the code down. I tried a timer, but that appears to pause all of access(both db stay open). I am at a loss. If I can get this to work it would be a success.

I am using win 2000 with access 2000.
Please, let me know if you need clarification on anything.

Thank you in advance.

Nick
Aug 8 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 112
Could you post your code?
Aug 8 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
This seems to imply you're running at least some of your code asynchronously. Are you using Shell() at all? If so I have recently finished a small module that simulates Shell() but synchronously. Is this where you're at?
Aug 9 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
Another important question of course is, have you considered a Front End / Back End database (Front-End / Back-End (FE/BE)) set-up? I suspect this may resolve your current issue, and make the whole process a lot more straightforward to boot.
Aug 9 '08 #4

P: 78
Neopa,

Thanks for the posting, Front-end/Back-end will work (but unfortunately as a last resort at this Point. I would really like to get working what I have started on. Which is using shell(). I think you were absolutely correct. Below I will post the code and see if you can help me through it.

This is the main DB that has all the updates in it. This will run if they choose to Update the DB from an old version.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.DeleteObject acTable, "BranchCategory"
  2. DoCmd.DeleteObject acTable, "Suppliers"
  3. DoCmd.DeleteObject acTable, "FromContacts"
  4. DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "BranchCategory", "BranchCategory"
  5. DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "Suppliers", "Suppliers"
  6. DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "FromContacts", "FromContacts"
  7. Kill ("C:\GAPs_Expedite_Folder\Expedite Status Update.mdb")
  8. VBA.Shell Chr(34) & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\GAPs_Expedite_Folder\Update.mdb" & Chr(34), vbNormalFocus
  9. DoCmd.Quit (acQuitSaveAll)
  10.  
This is the "Update" DB code which runs when the DB opens
Expand|Select|Wrap|Line Numbers
  1. Application.SetOption "Auto Compact", False
  2. Name "C:\GAPs_Expedite_Folder\Updated Expedite Status Update.mdb" As "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb"
  3. VBA.Shell Chr(34) & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb" & Chr(34), vbNormalFocus
  4. DoCmd.Quit (acQuitSaveAll)
  5.  
Then the main DB will run the following code when recalled by "Update" DB.
Expand|Select|Wrap|Line Numbers
  1. If CurrentProject.Name = "Expedite Status Update.mdb" Then
  2.     If FormExists("Update Form") = True Then
  3.         On Error Resume Next
  4.         DoCmd.DeleteObject acForm, "Update Form"
  5.         Kill ("C:\GAPs_Expedite_Folder\Update.mdb")
  6.         MsgBox "Update Complete.  You may now continue with the updated program."
  7.     Else
  8.         If FormExists("Update Complete") = True Then
  9.             DoCmd.DeleteObject acForm, "Update Complete"
  10.         End If
  11.     End If
  12. End If
  13.  
Please let me know if there is anything you can do to help me with.

Ultimately all I want to do that I cannot do right now is change the name of the Main DB from "Updated Expedite Status Update" to "Expedite Status Update".

Thanks in advance.

Nick
Aug 11 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
I could have sworn I'd posted this before somewhere (recently), but I can't find it so I'll post it again.

This is a ShellWait() function and will replace the existing Shell() call. There is a slight difference though, in that this is a subroutine which :
  1. Doesn't return a handle of any sort.
  2. Doesn't return at all until the called command has completed.
This code is designed to copy and paste straight into a new module, which you can call what you like. I haven't tested it in this guise exactly, as I have had to remove any other code that shared my module, and any references to anything that isn't common (some of my other code). Having said that, the version I stripped it from is working perfectly and I couldn't be happier with it.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Windows API Variable Prefixes
  5. 'cb = Count of Bytes (32-bit)
  6. 'w  = Word (16-bit)
  7. 'dw = Double Word (32-bit)
  8. 'lp = Long Pointer (32-bit)
  9. 'b  = Boolean (32-bit)
  10. 'h  = Handle (32-bit)
  11. 'ul = Unsigned Long (32-bit)
  12.  
  13. Private Const conUseShowWindow = &H1&
  14. Private Const conNormalPriority = &H20&
  15. Private Const conInfinite = -1&
  16.  
  17. Private Type typStartupInfo
  18.     cbLen As Long
  19.     lpReserved As String
  20.     lpDesktop As String
  21.     lpTitle As String
  22.     dwX As Long
  23.     dwY As Long
  24.     dwXSize As Long
  25.     dwYSize As Long
  26.     dwXCount As Long
  27.     dwYCount As Long
  28.     dwFillAtt As Long
  29.     dwFlags As Long
  30.     wShowWindow As Integer
  31.     cbReserved2 As Integer
  32.     lpReserved2 As Long
  33.     hStdIn As Long
  34.     hStdOut As Long
  35.     hStdErr As Long
  36. End Type
  37.  
  38. Private Type typProcInfo
  39.     hProc As Long
  40.     hThread As Long
  41.     dwProcID As Long
  42.     dwThreadID As Long
  43. End Type
  44.  
  45. Private Declare Function CreateProcessA Lib "kernel32" ( _
  46.     ByVal lpApplicationName As Long, _
  47.     ByVal lpCommandLine As String, _
  48.     ByVal lpProcessAttributes As Long, _
  49.     ByVal lpThreadAttributes As Long, _
  50.     ByVal bInheritHandles As Long, _
  51.     ByVal dwCreationFlags As Long, _
  52.     ByVal lpEnvironment As Long, _
  53.     ByVal lpCurrentDirectory As Long, _
  54.     lpStartupInfo As typStartupInfo, _
  55.     lpProcessInformation As typProcInfo) As Long
  56. Private Declare Function WaitForSingleObject Lib "kernel32" ( _
  57.     ByVal hHandle As Long, _
  58.     ByVal dwMilliseconds As Long) As Long
  59. Private Declare Function CloseHandle Lib "kernel32" ( _
  60.     ByVal hObject As Long) As Long
  61.  
  62. 'ShellWait() executes a command synchronously (Shell() works asynchronously).
  63. Public Sub ShellWait(strCommand As String, _
  64.                      Optional intWinStyle As Integer = vbNormalFocus)
  65.     Dim objProcInfo As typProcInfo
  66.     Dim objStart As typStartupInfo
  67.  
  68.     'Initialize the typStartupInfo structure:
  69.     With objStart
  70.         .cbLen = Len(objStart)
  71.         .dwFlags = conUseShowWindow
  72.         .wShowWindow = intWinStyle
  73.     End With
  74.     'Start the shelled application:
  75.     Call CreateProcessA(lpApplicationName:=0&, _
  76.                         lpCommandLine:=strCommand, _
  77.                         lpProcessAttributes:=0&, _
  78.                         lpThreadAttributes:=0&, _
  79.                         bInheritHandles:=1&, _
  80.                         dwCreationFlags:=conNormalPriority, _
  81.                         lpEnvironment:=0&, _
  82.                         lpCurrentDirectory:=0&, _
  83.                         lpStartupInfo:=objStart, _
  84.                         lpProcessInformation:=objProcInfo)
  85.     'Wait for the shelled application to finish
  86.     Call WaitForSingleObject(hHandle:=objProcInfo.hProc, _
  87.                              dwMilliseconds:=conInfinite)
  88.     Call CloseHandle(hObject:=objProcInfo.hProc)
  89. End Sub
Aug 11 '08 #6

P: 78
Thank you for that. However, What I was looking for was quite the opposite of that. I needed the Shell Application to wait until the db that called it closed out completely before it ran its startup code.

I was able to figure it out with putting a timer code with Do Events in the loop. By placing that before the line that accessed the opposing db it paused the code but not all of access like it was doing before. Thank you for your help though.

I would still love to know if there is a way to rename a DB file while it is open....Even from within the DB itself.

Thanks Again

Nick
Aug 12 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
...
I would still love to know if there is a way to rename a DB file while it is open....Even from within the DB itself.
...
I will have a think about your other questions later, but as for this one, as when the database is open the file it resides in is open too (Access is not an application that opens the database file, gets what it wants for the time being and closes it again), it is not possible for the database to rename itself from directly within its own code. No Operating System would allow that (renaming an open file).
Aug 12 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
Rereading your post in less of a rush I see that you've found a perfectly workable solution which is good. I would have suggested something very similar except incorporating the DoEvents into a loop and testing the availability of the original database by trying a simple rename (to itself of course).

When it succeeds then your code is ready to break out of its loop and continue processing. Possibly from your experience this would be overkill. Well done anyway :)
Aug 12 '08 #9

Post your reply

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