Hi
i have two databases one (source) exports to another table
the second (search) is linked to the second table.
the Export from the source happens when the dabase is closed.
No problems so far

i have linked the search dbase and the table(could be several)

problem is if they are both source and seach are open
the source database crashes because it is trying to export to the
table.

what i am thinking of doing is checking if the search is open
if it is stop the export
if closed export

i have been told that i could check using the DIr function
Could anyone tell me how to do this using VB or a macro function

thanks


kevin

In news:(E-Mail Removed),
kevcar40 <(E-Mail Removed)> wrote:
> Hi
> i have two databases one (source) exports to another table
> the second (search) is linked to the second table.
> the Export from the source happens when the dabase is closed.
> No problems so far
>
> i have linked the search dbase and the table(could be several)
>
> problem is if they are both source and seach are open
> the source database crashes because it is trying to export to the
> table.
>
> what i am thinking of doing is checking if the search is open
> if it is stop the export
> if closed export
>
> i have been told that i could check using the DIr function
> Could anyone tell me how to do this using VB or a macro function

I can't quite picture exactly what you're doing from your description.
Do you mean that you are exporting from the source database into the
search database, but that the export fails when the search database is
open? Does it actually crash -- that is, cause the Access to go
belly-up -- or does the export operation just fail because the target
database is locked?

I have a few thoughts on this:

1. It shouldn't be necessary for the search database to be closed, so
long as it is not opened exclusively. Maybe you can solve the problem
by ensuring that the search database isn't opened exclusively.

2. Do you really need to export to the search database at all? Can the
search database just link to the table(s) in the source database?

3. If you have to, you can make a weak check on whether the source
database by testing whether there's an LDB file corresponding to it in
the same folder. You could use the Dir function for this, as someone
told you. For example, if the source database is
"Q:\SomePath\SomeDB.mdb", then

If Len(Dir("Q:\SomePath\SomeDB.ldb")) = 0 Then
' Either the database is closed, or it is opened read-only.
Else
' Either the database is open, or some unusual circumstance
' has kept Access from cleaning up the LDB file.
End If

Note that this is a weak check -- the presence of the LDB file doesn't
absolutely mean the database is open, and its absence doesn't absolutely
mean it's closed.

4. Better, in my view, would be to use proper error-handling to catch an
error that may be raised when you attempt to export, interpret that
error and explain to the user that they can't export now because the
database is locked by another user, and then exit gracefully.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for reply
i export after an update to the source table.
if i were to try and open the search in a non exclusive state how can
i do this?


On 9 Nov, 15:50, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com> wrote:
> Innews:(E-Mail Removed),
>
>
>
>
>
> kevcar40 <kevca...@btinternet.com> wrote:
> > Hi
> > i have two databases one (source) exports to another table
> > the second (search) is linked to the second table.
> > the Export from the source happens when the dabase is closed.
> > No problems so far
>
> > i have linked the search dbase and the table(could be several)
>
> > problem is if they are both source and seach are open
> > the source database crashes because it is trying to export to the
> > table.
>
> > what i am thinking of doing is checking if the search is open
> > if it is stop the export
> > if closed export
>
> > i have been told that i could check using the DIr function
> > Could anyone tell me how to do this using VB or a macro function
>
> I can't quite picture exactly what you're doing from your description.
> Do you mean that you are exporting from the source database into the
> search database, but that the export fails when the search database is
> open? Does it actually crash -- that is, cause the Access to go
> belly-up -- or does the export operation just fail because the target
> database is locked?
>
> I have a few thoughts on this:
>
> 1. It shouldn't be necessary for the search database to be closed, so
> long as it is not opened exclusively. Maybe you can solve the problem
> by ensuring that the search database isn't opened exclusively.
>
> 2. Do you really need to export to the search database at all? Can the
> search database just link to the table(s) in the source database?
>
> 3. If you have to, you can make a weak check on whether the source
> database by testing whether there's an LDB file corresponding to it in
> the same folder. You could use the Dir function for this, as someone
> told you. For example, if the source database is
> "Q:\SomePath\SomeDB.mdb", then
>
> If Len(Dir("Q:\SomePath\SomeDB.ldb")) = 0 Then
> ' Either the database is closed, or it is opened read-only.
> Else
> ' Either the database is open, or some unusual circumstance
> ' has kept Access from cleaning up the LDB file.
> End If
>
> Note that this is a weak check -- the presence of the LDB file doesn't
> absolutely mean the database is open, and its absence doesn't absolutely
> mean it's closed.
>
> 4. Better, in my view, would be to use proper error-handling to catch an
> error that may be raised when you attempt to export, interpret that
> error and explain to the user that they can't export now because the
> database is locked by another user, and then exit gracefully.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)- Hide quoted text -
>
> - Show quoted text -

In news:(E-Mail Removed),
kevcar40 <(E-Mail Removed)> wrote:
> Thanks for reply
> i export after an update to the source table.
> if i were to try and open the search in a non exclusive state how can
> i do this?

It's not about you opening it exclusively -- it's about making sure that
others don't open it exclusively. For that, three conditions must be
met:

1. The users must have full permissions -- read, write, create, and
delete -- on the folder containing the database.

2. The users must not have their Access option "Default open mode" (on
the Advanced tab of the Tools -> Options... dialog) set to Shared, not
Exclusive. Shared is the installation default for this option, so you
shouldn't need to change it. But you never know ...

Also, users mustn't explicitly specify Exclusive mode when they open the
database from the File -> Open... dialog, but that's not likely.

3. Users must not make design changes to any object or code. Even if
you've opened a database in shared mode, if you then make design
changes, Access promotes that to exclusive mode. If it can't do that,
it won't let you save the changes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)