Disconect linked tables opened in front end from back end with vba executed from be

[afromanam]

Whew..

OK, running access 2003 in win xpsp2 and access 2007 in win vista.

This is the question...

We have a db, split in front end (FE.mdb) and backend (BE.mdb)

FE has tons of linked tables from BE.

So the question is... I want to edit the design of a table in BE, is there a way to say, break the links or connections active to the table in the backend with code executed from the backend itself.

Please do not post answers where one could have a form open at startup in hidden view in the front end linked to a table in the backend and when a value is changed in such table a modal form pops ups and after a certain time it closes access, that is not the answer i'm looking for.

I was thinking, if there is a tabledefs.conne ct property in the front end, is there something similar in backend, i mean, is there something like a tabledefs.conne ct property for tables in the backed, and if it exists, could it be set to null so to break the links in the frontends. Access knows when a table is open in front ends, but what is that property,how could one access it, and if it can me modified?

I don't mean to be rude or anything like that, but please do not post answers like "a better approach to this would be...".

Please think of this question as a proof of concept, here the mean is the important, not the end itself.

Regads,

[puppydogbuddy]

Here are reference sources that tell what to do. The second is referenced in the first link.

http://www.granite.ab.ca/access/relinkingtables.htm

http://www.mvps.org/access/tables/tbl0009.htm

[afromanam]

Thanks for the prompt reply m8.

However, let me rephrase the question:

Can you check if a table in the BE.mdb is open in other databasese, be it in the FE or another database.

I saw the tabledefs.conne ct property, however that seems to work only with the linked tables on the FE.

However the question remains:

1. when someone has linked to a table in the BE and has it open in another database, you cannot edit the design of the table in the backednd. So there is a way in which Access knows that a table is open in another database.

2. Is there a way to know if a table in the BE is open in the FE without resorting to try to edit the design of the table and get the Open in Read-only mode prompt?

3. How could one break the connection or the links to the table in the backend.

Perhaps I'm not making myself clear:

Is there a way where one could have a form in the backend with a button where you could click it and it would close all the connections open on the tables in the frontend, so you could edit the designs of the tables?

I've doing some research but from what I've seen, you would have to meddle with closing transactions or closing connections, or even use ado, adodb or dao operations, and that's way over my head.

Regards,

[NeoPa]

So, if I understand you correctly, you want to be able to determine, with the BE database open, whether or not any of its tables are currently open from elsewhere. Presumably that would include from copies of the FE as well as someone opening the BE itself somewhere else. Also, any potential solution should not include code.

Is that a fair expression of your question?

[NeoPa]

As this doesn't quite fit that I will simply suggest it and allow you to explain that it doesn't meet your requirements...

Would trying to open BE in Exclusive mode work for you? You would immediately know if the database opened that :

1. None of the tables is currently opened.
2. None could be opened until you have closed BE.

[ADezii]

Thanks for the prompt reply m8.

However, let me rephrase the question:

Can you check if a table in the BE.mdb is open in other databasese, be it in the FE or another database.

I saw the tabledefs.conne ct property, however that seems to work only with the linked tables on the FE.

However the question remains:

1. when someone has linked to a table in the BE and has it open in another database, you cannot edit the design of the table in the backednd. So there is a way in which Access knows that a table is open in another database.

2. Is there a way to know if a table in the BE is open in the FE without resorting to try to edit the design of the table and get the Open in Read-only mode prompt?

3. How could one break the connection or the links to the table in the backend.

Perhaps I'm not making myself clear:

Is there a way where one could have a form in the backend with a button where you could click it and it would close all the connections open on the tables in the frontend, so you could edit the designs of the tables?

I've doing some research but from what I've seen, you would have to meddle with closing transactions or closing connections, or even use ado, adodb or dao operations, and that's way over my head.

Regards,

Is there a way where one could have a form in the backend with a button where you could click it and it would close all the connections open on the tables in the frontend, so you could edit the designs of the tables?

Not at all advisable!

[puppydogbuddy]

It sounds like the free Auto FE Updater at the link shown below will accomplish most, if not all of what you are asking for.

http://www.granite.ab.ca/Access/autofe.htm

Excerpt:

The utility guarantees a consistent front end to the user. You can lock out users if there is a problem in the backend or you need to make some schema changes.

[FishVal]

@PDB

The utility will, if required,
.....
allow an administrator to set or remove the lockout to prevent users from starting the app.

Does that mean "Passive shutdown" - like setting "Jet OLEDB:Connectio n Control" property?
Or it allows to logout user already logged in?

Regards,
Fish

[afromanam]

So, if I understand you correctly, you want to be able to determine, with the BE database open, whether or not any of its tables are currently open from elsewhere. Presumably that would include from copies of the FE as well as someone opening the BE itself somewhere else. Also, any potential solution should not include code.

Is that a fair expression of your question?

M8,

Thanks again for the response,

you want to be able to determine, with the BE database open, whether or not any of its tables are currently open from elsewhere. Presumably that would include from copies of the FE as well as someone opening the BE itself somewhere else.

Yes, you are correct, it would include also databases that are not the FE and that have linked tables to the BE.

Also, any potential solution should not include code.

Is that a fair expression of your question?

Not really I guess that this request would be extremely difficult if not impossible to carry out without VBA

Would trying to open BE in Exclusive mode work for you? You would immediately know if the database opened that :

1. None of the tables is currently opened.
2. None could be opened until you have closed BE.

I agree that Exclusive mode would be the solution, however, you would need to kick out users from their FE so nobody is in when you try to edit the tables' design, that is, you could open the file exclusively but you would have to wait until all the users who have the linked table open (whether is in the FE or another DB that has a linked table to the BE) exit. So, I think -should my question have an answer- you must break links (or flush links, end transactions or however this could be done) and then you could open the BE exclusively.

Regards,

[afromanam]

It sounds like the free Auto FE Updater at the link shown below will accomplish most, if not all of what you are asking for.

http://www.granite.ab.ca/Access/autofe.htm

Excerpt:

The utility guarantees a consistent front end to the user. You can lock out users if there is a problem in the backend or you need to make some schema changes.

M8 thanks for the answer, specially on sunday!

Just picture this (becuase it has happened to me before):

maybe someone has linked to a table in the BE from a DB that is not the FE (even if you have user-level security, they just could make a simple desktop shortcut to MSACCESS.EXE and with the /WRKGROUP option set to your mdw file and make a blank DB and link to the BE tables) and voila, all of your built-in hidden forms for kicking-users out are rendered useless for that user. That is why using a hidden form with timer event solution to kick users out is not the way to go, it would have to be a solution that would close all active connections to a table in the BE, but with code executed from the BE itself.

Regards,

PS I will take a look at the FE solution you provided,

[afromanam]

Not at all advisable!

M8 thanks for the advice,

Perhaps finding out if what I'm proposing is possible for Access could be a first step, then a solution could be found where all active transactions are commited prior to closing all connections.

PS Please feel free to propose any solutions to this problem, if we start making a mess out of the BE, we can't tell we weren't warned. :)

Regards,

[ADezii]

M8 thanks for the advice,

Perhaps finding out if what I'm proposing is possible for Access could be a first step, then a solution could be found where all active transactions are commited prior to closing all connections.

PS Please feel free to propose any solutions to this problem, if we start making a mess out of the BE, we can't tell we weren't warned. :)

Regards,

How about taking a more conservative approach, take a little time, and read the following Links:

How to Generate a User List
Passive Shutdown

P.S. - Passive Shutdown is designed to specifically limit the number of simultaneous Users that are accessing a Database, and preventing future log-ons, so that you may perform Administrative Tasks. Generating a User List to specifically see who is logged on, and Passive Shutdown to prevent further log-ons, may be a viable option (one-two punch) for you.

[NeoPa]

I agree that Exclusive mode would be the solution, however, you would need to kick out users from their FE so nobody is in when you try to edit the tables' design, that is, you could open the file exclusively but you would have to wait until all the users who have the linked table open (whether is in the FE or another DB that has a linked table to the BE) exit. So, I think -should my question have an answer- you must break links (or flush links, end transactions or however this could be done) and then you could open the BE exclusively.

Regards,

That's not quite it, as opening in Exclusive mode fails unless the whole database is unused. If the database is in use in any way, you would need to try again later.

I would echo ADezii's comment about never force-closing a link. That really would give you more trouble than you need. I would think if you needed something more direct than the simple exclusive-mode open, then this would be your best bet.

[afromanam]

How about taking a more conservative approach, take a little time, and read the following Links:

How to Generate a User List
Passive Shutdown

P.S. - Passive Shutdown is designed to specifically limit the number of simultaneous Users that are accessing a Database, and preventing future log-ons, so that you may perform Administrative Tasks. Generating a User List to specifically see who is logged on, and Passive Shutdown to prevent further log-ons, may be a viable option (one-two punch) for you.

M8, thanks for the reply

I'm sorry I didn't look in the forum more thorughly, will do better next time.

This feature is referred to as 'Passive' since there is no way to forcibly boot Users out of a Database.

OK, well it is certainly not the answer I was hoping for, but it is indeed the answer to the question I posed.

Thank you very much

[afromanam]

That's not quite it, as opening in Exclusive mode fails unless the whole database is unused. If the database is in use in any way, you would need to try again later.

I would echo ADezii's comment about never force-closing a link. That really would give you more trouble than you need. I would think if you needed something more direct than the simple exclusive-mode open, then this would be your best bet.

M8 thanks for the reply,

I've run into situations where I had the DB open in shared mode, went into Options, checked the Open in Exclusive Mode option; that would restrict users from entering, but would not kick out users currently logged in, so I'd have to wait until everyone logged out to make changes in the design of the table, but there would be times where a user would just lock their windows session and leave the DB with the linked table open during the whole weekend! I would then via the FE try to kick everyone out, but didn't have any option when the user linked the table from a DB other than the FE.

Regards