Access background execution (1 Viewer)

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
Hi,
I have a code to extract the files in attachments and save them in another location.
This code loop to all files attached in one record and sometimes it takes time.

once the code get started. It takes long time to be conpleted and this prevents the users from working on that form until the saving is complete.

is there any workaround to run this code in away where the user can continue working in the form while the code is running?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Hi. Since Access is single-threaded, the only other solution I could think of is to maybe run the extraction process using an external database application.
 

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
Hi. Since Access is single-threaded, the only other solution I could think of is to maybe run the extraction process using an external database application.
Thanks for reply.. can you elaborate please
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Thanks for reply.. can you elaborate please
Basically, what I'm trying to say is, if you have DB1 for the users, create a DB2 for extracting the attachments and run it separately from DB1.
 

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
i see.. I am afraid I can’t as I am connecting to one sharepoint list

any other thoughts? Anyone?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
i see.. I am afraid I can’t as I am connecting to one sharepoint list

any other thoughts? Anyone?
Hi. Good luck; although, I don't understand your reasoning about connecting to one SharePoint List. How is that a limitation or a restriction for creating another db?
 

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
Hi. Good luck; although, I don't understand your reasoning about connecting to one SharePoint List. How is that a limitation or a restriction for creating another db?
In that list: the users make requests and attach some forms within the request. And in access I am creating an interface for administrators to manage the requests
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
In that list: the users make requests and attach some forms within the request. And in access I am creating an interface for administrators to manage the requests
Got that part, so what's stopping you from creating a separate DB to manage/manipulate the attachments? Just curious...
 

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
So the code is to copy the attachments and paste them in another share folder.
Most of the delay is coming while downloading the attachments from sharelist.
Maybe I am not understand your suggestion.
But if I create another Db, i will need to take the attachments from that list anyways.. right?!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
So the code is to copy the attachments and paste them in another share folder.
Most of the delay is coming while downloading the attachments from sharelist.
Maybe I am not understand your suggestion.
But if I create another Db, i will need to take the attachments from that list anyways.. right?!
Okay, all I'm saying is, you can try to move the process that's taking too slow to finish in your current DB to a separate DB. The process shouldn't change anything from what you're doing now.

For example, if you were physically moving a heavy object that it's taking your too slow to finish other tasks, my suggestion is to ask somebody else to move the object for you, so you can continue doing your other tasks.

Hope that makes sense...

By the way, if you're moving the attachments from one List to another, perhaps you can just use a Workflow (or Flow) in SharePoint to move them for you, instead of using Access. That would be similar to my suggestion, with the difference that the "external" process is using SharePoint instead of Access.

In any case, I do hope others with better ideas would chime in with better solutions. However, as I said though, we'll have to remember that Access is a single-threaded process. Cheers!
 

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
Okay, all I'm saying is, you can try to move the process that's taking too slow to finish in your current DB to a separate DB. The process shouldn't change anything from what you're doing now.

For example, if you were physically moving a heavy object that it's taking your too slow to finish other tasks, my suggestion is to ask somebody else to move the object for you, so you can continue doing your other tasks.

Hope that makes sense...

By the way, if you're moving the attachments from one List to another, perhaps you can just use a Workflow (or Flow) in SharePoint to move them for you, instead of using Access. That would be similar to my suggestion, with the difference that the "external" process is using SharePoint instead of Access.

In any case, I do hope others with better ideas would chime in with better solutions. However, as I said though, we'll have to remember that Access is a single-threaded process. Cheers!
Thanks for your time.. actually sharepoint has limitation in the size therefore I need to download them in a share folder.
In the even of closing the requests, the files are being copied.
If I am creating another database to handle this in which event you are proposing to put the code in?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Thanks for your time.. actually sharepoint has limitation in the size therefore I need to download them in a share folder.
In the even of closing the requests, the files are being copied.
If I am creating another database to handle this in which event you are proposing to put the code in?
Re: which event. That would depend on your process. The only thing I wasn't sure about my idea is what happens if we start the external process from the original DB. Would the original DB then still wait for the other DB to finish the process before it proceeds, because of single-threading? If that's the case (I'm not sure), then I would suggest initiating the external process on its own, outside of the first DB. Which means, you won't use any events from the original DB.

For example, here's one possible scenario. In the original DB, we could create a process where when the attachment is ready to be moved, we can flag it by indicating in a Yes/No field a value of True. So, you continue the process in DB1 without moving any attachments, but merely tagging them.

Then, at some point, perhaps on a regular schedule, you could start up DB2, and it will automatically move all attachments tagged with the value of True.

Just a thought...
 
Last edited:

Joy83

Member
Local time
Today, 03:10
Joined
Jan 9, 2020
Messages
116
Re: which event. That would depend on your process. The only thing I wasn't sure about my idea is what happens if we start the external process from the original DB. Would the original DB then still wait for the other DB to finish the process before it proceeds, because of single-threading? If that's the case (I'm not sure), then I would suggest initiating the external process on its own, outside of the first DB. Which means, you won't use any events from the original DB.

For example, here's one possible scenario. In the original DB, we could create a process where when the attachment is ready to be moved, we can flag it by indicating in a Yes/No field a value of True. So, you continue the process in DB1 without moving any attachments, but merely tagging them.

Then, at some point, perhaps on a regular schedule, you could start up DB2, and it will automatically move all attachments tagged with the value True automatically.

Just a thought...
Very nice
I like this..very helpful
One last thing, you said “automatically”
Is there away to schedule an event yo be run automatically in certain times
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Very nice
I like this..very helpful
One last thing, you said “automatically”
Is there away to schedule an event yo be run automatically in certain times
Re: schedule. Yes, you can use the Task Scheduler to automatically perform tasks unattended.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Many thanks for your help
Hi. You're welcome. I still hope someone else comes up with a better solution for you. Please let us know which way you go. Good luck!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 28, 2001
Messages
26,996
Unfortunately, I have to reinforce a couple of things theDBguy said.

First, an Access front-end is single-threaded. It may be that MSACCESS.EXE contains some things that run in parallel, but when it is running event code, you are definitely single-threaded. There is no direct "background task" sort of thing.

Second, synchronization would be a serious issue between two parallel front-ends. The idea of flagging records and then having a second process work on them would be good. To make the second process run on demand (not on a timer), you could look up "Access command-line switches" on the web and then specifically look at /X - the "Execute specific macro" switch. If you have done the marking and are ready for the background process to start, you could use a SHELL command to activate Access a second time using the /X:macroname option.

IF you do this, the macro can include a "RunCode" of a FUNCTION (and this use of a FUNCTION is crucial to RunCode). In this putative FUNCTION, you would write code to do the moving of marked records. The macro that you build must end with an Application.Quit action because otherwise you would have a "hanging" copy of Access in memory, which would not be a good idea.

The "janitorial" front-end would have to include code to assure that you only run one copy of it at a time and just allow it to silently exit if there is a conflict. You would have to set up some table in the back-end to keep track of who is doing what to whom at any given moment.

Note that there is no reason to run a separate front-end if you have the "janitorial" macro in your primary front-end file. As long you are careful in the RunCode function, you should not have any conflicts.
 

Users who are viewing this thread

Top Bottom