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
8  2224 
Could you post your code?
NeoPa 32,556
Expert Mod 16PB
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?
NeoPa 32,556
Expert Mod 16PB
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.
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. -
DoCmd.DeleteObject acTable, "BranchCategory"
-
DoCmd.DeleteObject acTable, "Suppliers"
-
DoCmd.DeleteObject acTable, "FromContacts"
-
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "BranchCategory", "BranchCategory"
-
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "Suppliers", "Suppliers"
-
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb", acTable, "FromContacts", "FromContacts"
-
Kill ("C:\GAPs_Expedite_Folder\Expedite Status Update.mdb")
-
VBA.Shell Chr(34) & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\GAPs_Expedite_Folder\Update.mdb" & Chr(34), vbNormalFocus
-
DoCmd.Quit (acQuitSaveAll)
-
This is the "Update" DB code which runs when the DB opens -
Application.SetOption "Auto Compact", False
-
Name "C:\GAPs_Expedite_Folder\Updated Expedite Status Update.mdb" As "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb"
-
VBA.Shell Chr(34) & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\GAPs_Expedite_Folder\Expedite Status Update.mdb" & Chr(34), vbNormalFocus
-
DoCmd.Quit (acQuitSaveAll)
-
Then the main DB will run the following code when recalled by "Update" DB. -
If CurrentProject.Name = "Expedite Status Update.mdb" Then
-
If FormExists("Update Form") = True Then
-
On Error Resume Next
-
DoCmd.DeleteObject acForm, "Update Form"
-
Kill ("C:\GAPs_Expedite_Folder\Update.mdb")
-
MsgBox "Update Complete. You may now continue with the updated program."
-
Else
-
If FormExists("Update Complete") = True Then
-
DoCmd.DeleteObject acForm, "Update Complete"
-
End If
-
End If
-
End If
-
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
 NeoPa 32,556
Expert Mod 16PB
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 : - Doesn't return a handle of any sort.
- 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. - Option Compare Database
-
Option Explicit
-
-
'Windows API Variable Prefixes
-
'cb = Count of Bytes (32-bit)
-
'w = Word (16-bit)
-
'dw = Double Word (32-bit)
-
'lp = Long Pointer (32-bit)
-
'b = Boolean (32-bit)
-
'h = Handle (32-bit)
-
'ul = Unsigned Long (32-bit)
-
-
Private Const conUseShowWindow = &H1&
-
Private Const conNormalPriority = &H20&
-
Private Const conInfinite = -1&
-
-
Private Type typStartupInfo
-
cbLen As Long
-
lpReserved As String
-
lpDesktop As String
-
lpTitle As String
-
dwX As Long
-
dwY As Long
-
dwXSize As Long
-
dwYSize As Long
-
dwXCount As Long
-
dwYCount As Long
-
dwFillAtt As Long
-
dwFlags As Long
-
wShowWindow As Integer
-
cbReserved2 As Integer
-
lpReserved2 As Long
-
hStdIn As Long
-
hStdOut As Long
-
hStdErr As Long
-
End Type
-
-
Private Type typProcInfo
-
hProc As Long
-
hThread As Long
-
dwProcID As Long
-
dwThreadID As Long
-
End Type
-
-
Private Declare Function CreateProcessA Lib "kernel32" ( _
-
ByVal lpApplicationName As Long, _
-
ByVal lpCommandLine As String, _
-
ByVal lpProcessAttributes As Long, _
-
ByVal lpThreadAttributes As Long, _
-
ByVal bInheritHandles As Long, _
-
ByVal dwCreationFlags As Long, _
-
ByVal lpEnvironment As Long, _
-
ByVal lpCurrentDirectory As Long, _
-
lpStartupInfo As typStartupInfo, _
-
lpProcessInformation As typProcInfo) As Long
-
Private Declare Function WaitForSingleObject Lib "kernel32" ( _
-
ByVal hHandle As Long, _
-
ByVal dwMilliseconds As Long) As Long
-
Private Declare Function CloseHandle Lib "kernel32" ( _
-
ByVal hObject As Long) As Long
-
-
'ShellWait() executes a command synchronously (Shell() works asynchronously).
-
Public Sub ShellWait(strCommand As String, _
-
Optional intWinStyle As Integer = vbNormalFocus)
-
Dim objProcInfo As typProcInfo
-
Dim objStart As typStartupInfo
-
-
'Initialize the typStartupInfo structure:
-
With objStart
-
.cbLen = Len(objStart)
-
.dwFlags = conUseShowWindow
-
.wShowWindow = intWinStyle
-
End With
-
'Start the shelled application:
-
Call CreateProcessA(lpApplicationName:=0&, _
-
lpCommandLine:=strCommand, _
-
lpProcessAttributes:=0&, _
-
lpThreadAttributes:=0&, _
-
bInheritHandles:=1&, _
-
dwCreationFlags:=conNormalPriority, _
-
lpEnvironment:=0&, _
-
lpCurrentDirectory:=0&, _
-
lpStartupInfo:=objStart, _
-
lpProcessInformation:=objProcInfo)
-
'Wait for the shelled application to finish
-
Call WaitForSingleObject(hHandle:=objProcInfo.hProc, _
-
dwMilliseconds:=conInfinite)
-
Call CloseHandle(hObject:=objProcInfo.hProc)
-
End Sub
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
NeoPa 32,556
Expert Mod 16PB
...
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).
NeoPa 32,556
Expert Mod 16PB
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet...
|
by: I Are Confused |
last post by:
I am trying to open Database #2 while in Database #1 and delete a
table that is in #2 so that I can copy a table from #1 into #2 - all
through a function. My Database #2, as part of the start up,...
|
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The...
|
by: wandali |
last post by:
When I want to edit a MS Database, I have to run the following command:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"\\Server\Directory\File.mdb" /wrkgrp...
|
by: Mike Bridge |
last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me
the error "Join expression not supported". Apparently, Access "fixed" it for...
|
by: lauren quantrell |
last post by:
Is there a way to open the MS Outlook address book using VBA and then
be able to do something with the return value?
I want users to click an icon to open the Outlook address book then
when an...
|
by: CM |
last post by:
Hi there:
I have a web project which can be open and run without problem. I didn't
open the web application for a period and during which I didn't modified any
IIS items, but now I cannot open any...
|
by: CM |
last post by:
Hi there:
I have a web project which can be open and run without problem. I didn't
open the web application for a period and during which I didn't modified any
IIS items, but now I cannot open any...
|
by: bazzer |
last post by:
hey,
im trying to access a microsoft access database from an ASP.NET web
application in visual basic 2003.NET. i get the following error when i
try running it:
Server Error in...
|
by: Ian |
last post by:
I have a single file Access database that was written in Access 2000, I
have had the MDE over a network of 3x PC’s for several years without any
problems. Now I am using Access 2003 but I have left...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
