Help on Query Problem!!!Urgent! (1 Viewer)

[bokarinho]

Hello,

I have an access database application that is stored in a network share on my network. The database is has a macro procedure that is consisted by queries and run code functions or other stuff that has a purpose of uploading some data from a text file that is on the share too. There is a weird problem though. I have a scheduled job that has the role of running the macro every half an hour and uploads the data that come through the text file into the database. Everything seems to work fine during the hours that i am at work, the macro works, no problems at all. But when i come to work early in the morning the system has encountered an error in the standard procudure and it says:

"The microsoft Jet Database Engine cannot open the file '<query-name>'. It is already opened exclusively by another user, or you need permission to view its data."

The weird thing is that after i close the database to vanish the problem, all the other work day the macro works without any problem at all. The symptom seems to appear early in the morning that i come to work. Only that time. The database is splitted into front end and back end, and all users share theirs front end via a vbs script i have constructed so the main front end never gets opened. The macro is run by the administrator in the server machine that holds the database, all users have read-write permissions. I dont know what to do next. Thanks in advance.

 

[JamesMcS]

Are there any other scheduled tasks that run on the server around the same time the error pops up? Depending on the server software you might be able to look at some logs and see when it actually happens. Maybe a backup runs or something?

 

[bokarinho]

Are there any other scheduled tasks that run on the server around the same time the error pops up? Depending on the server software you might be able to look at some logs and see when it actually happens. Maybe a backup runs or something?

Thanks for your reply,

It is strange i had a look right now and no other jobs start or end or are close at the time the scheduled job starts. I dont know, i think that it looks like it sees the first time it starts that some other user has locked the database. But after i close the error i wrote and close the database on the server, although the macro executes every half an hour and people in the morning are logged in, it does not happen again all the morning. Any ideas?

 

[JamesMcS]

Hmmm. What about if you open the database exclusively? Go to file, open, select the database then drop down the options next to the 'open' button, there's an option in there to open the DB exclusively.

Doesn't sound like a user related problem but you never know... nothing in the server event log either around that time is there?

 

[vbaInet]

Your database is not split correctly. Each user should have a copy of the front end on their computer (not on a shared folder) which "connects" to the back end on the server.

There's lots of information about this on here (if you perform a search) and a quick googling might help too.

 

[bokarinho]

Hmmm. What about if you open the database exclusively? Go to file, open, select the database then drop down the options next to the 'open' button, there's an option in there to open the DB exclusively.

Doesn't sound like a user related problem but you never know... nothing in the server event log either around that time is there?

Nothing on the event viewer, i can open the datbase in exclusive manner. The problem lies when i try to run the .bat file i have first time in the morning, the bat file is like,

start msaccess .... /x macroName

Then it stays in a query i have in the macro and reports the message i sent you. Next executions of the bat file every half hour also fail, (i have to close the previous instances first) because it is stuck on the error and complains with the same message every time. If i close everything before the next execution and clean the problem, for all day works like a charm. It is very weird...

 

[JamesMcS]

That might be why, if the bat file is running every half an hour and the database isn't closed before the next execution it'll come up saying it's already opened by another user.

Have a look at the CloseDatabase macro command...

 

[bokarinho]

Your database is not split correctly. Each user should have a copy of the front end on their computer (not on a shared folder) which "connects" to the back end on the server.

There's lots of information about this on here (if you perform a search) and a quick googling might help too.

I have tested that and for reasons that not all people have partitions D: in my company or whatever, i prefered the shared folder path. I could throw via vbs code the frontend part into C:\AppName (All pc have the C:\ Partition) but i dont think that this is the reason for my problem. All people work great even if the frontend part is in the shared folder. Also this helps us too, in order to have other info. But the problem lies in the first execution of the database macro, that problem does not continue through the day though.

 

[bokarinho]

That might be why, if the bat file is running every half an hour and the database isn't closed before the next execution it'll come up saying it's already opened by another user.

Have a look at the CloseDatabase macro command...

This is where i must agree with you, somehow i think of the database does not close correctly, or something is happening first. In the macro i close the database with the command:

SetWarnings -> Yes
Quit -> Saveall

Do i have to prefer another way of forcing closing the database?

 

[JamesMcS]

I haven't used it before but see what closedatabase does. Nor have I used Quit in a macro, but if Setwarnings is True before the Quit command, could it be asking you for something before it closes?

 

[bokarinho]

I haven't used it before but see what closedatabase does. Nor have I used Quit in a macro, but if Setwarnings is True before the Quit command, could it be asking you for something before it closes?

No it is not that, it does not asks something before it closes, it says the error during the execution of the macro.

 

[JamesMcS]

So the error comes up the first time the batch file runs? That's strange. I thought it must be that the database is already open when the macro executes the second time.

To be honest I wouldn't have it run through a batch file like this anyway. I'd have set up a loop in VBA to run the import every 30 minutes, and just leave the DB open all the time....

 

[bokarinho]

So the error comes up the first time the batch file runs? That's strange. I thought it must be that the database is already open when the macro executes the second time.

To be honest I wouldn't have it run through a batch file like this anyway. I'd have set up a loop in VBA to run the import every 30 minutes, and just leave the DB open all the time....

What i think of is that the first time the macro runs, stays stuck, then the consecutive runs every half an hour stuck and report the error i give becasuse the database is left open and stuck in the macro by administrator. The question is why in the first time the macro does not finishes and stops in the query, but all the other day it works great?

 

[JamesMcS]

That should give you a place to start investigating then. What steps are involved in the macro? If it's not finishing the first run, is there a problem witht he text file you're trying to import? and so on.

 

[vbaInet]

I have tested that and for reasons that not all people have partitions D: in my company or whatever, i prefered the shared folder path. I could throw via vbs code the frontend part into C:\AppName (All pc have the C:\ Partition) but i dont think that this is the reason for my problem. All people work great even if the frontend part is in the shared folder. Also this helps us too, in order to have other info. But the problem lies in the first execution of the database macro, that problem does not continue through the day though.

The question of giving each user a copy of the front end is not a matter of choice but a matter of best practice and what is right - to avoid corruption and the sort of problem you are experiencing.

Running the db via a VBScript is still launching the front end.

Give each user a copy of the fe.

 

[bokarinho]

The question of giving each user a copy of the front end is not a matter of choice but a matter of best practice and what is right - to avoid corruption and the sort of problem you are experiencing.

Running the db via a VBScript is still launching the front end.

Give each user a copy of the fe.

Through vbs i always give a copy of the current version of the frontend each time the user double clicks in his desktop icon. I dont ony run the db through vbs but i have written some code to copy the front end to the user and do the other stuff like opening the database with the linked tables and backend. Can you still help me with my problem?

 

[jesse]

I agree totally with what vbaInet says.

That being said the problem could also relate to the following. see:
http://stackoverflow.com/questions/137082/dont-have-exclusive-access-to-database-and-so-cannot-save-changes

 

[vbaInet]

Yes you can do that in VBScript, but is there no possibility that someone else has the database open whilst you're running the bat?

That link that jesse provided is also relevant. If you opened an object and set it to querydef somewhere in your code, it might be worth setting it to Nothing.

 

[bokarinho]

Thanks a lot for the help i will keep you informed if i resolve the matter. Wish you all the best.

 

[bokarinho]

Well the news are that i replaced all of my queries that read from the text file with code, surpisingly all seemed to work ok, but in the morning it says that the text file is opened exlusively from another user. Do you suggest in my code to tell, if the file_is_opened, close it, then reopen to read from it.
Any help?