checking for linked table files

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

Hello,
I am hoping someone can give me direction on a problem I am having with my
database. I have one database that is a front end and it links to tables in
a backend data file. It is setup to open at a splash screen and then go to a
main menu. During that transition, I would like to have it check to see if
the backend data file is available. If it is, I want to display a message
box, or simple form, that shows what the backend file location, complete
path, is. If it is not available in the specified path and file name, I
want it to open another form where they can choose a data file and relink to
that. I have the form for choosing the data file and relinking to the the
new tables complete, but I don't know how to write the code for any of the
other parts.
I am very new to Visual Basic, so please make any instructions you include
as obvious and simple as possible.
I great appreciate any help you can provide.
Beth
 
I'm sure there are better ways of doing a couple of the things that I'm
doing here, but the following works for me. I'm not sure how you expect
your users to try and break your db.... if they're just arbitrarily going to
move the back-end then who knows what you suspect they're going to do?
You'll need a reference to DAO.

Anyway... in your splash form.... have something like this...

Private Sub Form_Load()
If Not LinkedTableExists Then
'If not there, forget about opening this form and open the relinking
form
DoCmd.Close acForm, "Splash"
DoCmd.OpenForm "ReLink"
Else
'..and if it is there then display the path.
'Needs to be played with to just get the path
Me.DataPath = CurrentDb.TableDefs("Orders").Connect
End If
End Sub

Function LinkedTableExists() As Boolean
'....there must be a better way of doing this....
On Error GoTo Fail:
'Just checking one table at the moment. You may want to check more
LinkedTableExists = (DCount("[OrderID]", "Orders") > -1)
Exit Function
Fail:
'If the DCount fails, then assume that the be has moved.
LinkedTableExists = False
End Function

In the ReLink form, you'll need to have some way of browsing for files or
folders. Take a look at...
http://www.mvps.org/access/api/api0001.htm or
http://www.mvps.org/access/api/api0002.htm

I've just got a text box called txtNewPath at the moment, and a button with
this code....

Private Sub btnReLink_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
'Loop through existing tables, delete the existing one, and relink
For Each tdf In db.TableDefs
'Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.DeleteObject acTable, tdf.Name
DoCmd.TransferDatabase acLink, "Microsoft Access", _
Me.txtNewPath + "\YourBE.mdb", acTable, tdf.Name, tdf.Name
End If
Next
Set db = Nothing
'Reopen the splash form so that it tries again
DoCmd.Close acForm, "ReLink"
DoCmd.OpenForm "Splash"
End Sub

Hopefully that'll give you something to work with. Good luck.
 
Thank you.
I will try this out and see how it works. I am sure some users will try to
break the db, but the 2 issues I am having are accessing the backend file
over a very unreliable network, (so the link dissappears) and using multiple
backend files that the user chooses from a master list of optional files.

Have a nice night.
Beth

Rob Oldfield said:
I'm sure there are better ways of doing a couple of the things that I'm
doing here, but the following works for me. I'm not sure how you expect
your users to try and break your db.... if they're just arbitrarily going to
move the back-end then who knows what you suspect they're going to do?
You'll need a reference to DAO.

Anyway... in your splash form.... have something like this...

Private Sub Form_Load()
If Not LinkedTableExists Then
'If not there, forget about opening this form and open the relinking
form
DoCmd.Close acForm, "Splash"
DoCmd.OpenForm "ReLink"
Else
'..and if it is there then display the path.
'Needs to be played with to just get the path
Me.DataPath = CurrentDb.TableDefs("Orders").Connect
End If
End Sub

Function LinkedTableExists() As Boolean
'....there must be a better way of doing this....
On Error GoTo Fail:
'Just checking one table at the moment. You may want to check more
LinkedTableExists = (DCount("[OrderID]", "Orders") > -1)
Exit Function
Fail:
'If the DCount fails, then assume that the be has moved.
LinkedTableExists = False
End Function

In the ReLink form, you'll need to have some way of browsing for files or
folders. Take a look at...
http://www.mvps.org/access/api/api0001.htm or
http://www.mvps.org/access/api/api0002.htm

I've just got a text box called txtNewPath at the moment, and a button with
this code....

Private Sub btnReLink_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
'Loop through existing tables, delete the existing one, and relink
For Each tdf In db.TableDefs
'Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.DeleteObject acTable, tdf.Name
DoCmd.TransferDatabase acLink, "Microsoft Access", _
Me.txtNewPath + "\YourBE.mdb", acTable, tdf.Name, tdf.Name
End If
Next
Set db = Nothing
'Reopen the splash form so that it tries again
DoCmd.Close acForm, "ReLink"
DoCmd.OpenForm "Splash"
End Sub

Hopefully that'll give you something to work with. Good luck.


Beth said:
Hello,
I am hoping someone can give me direction on a problem I am having with my
database. I have one database that is a front end and it links to
Click to expand...
tables
in
a backend data file. It is setup to open at a splash screen and then go
Click to expand...
to
a
main menu. During that transition, I would like to have it check to see if
the backend data file is available. If it is, I want to display a message
box, or simple form, that shows what the backend file location, complete
path, is. If it is not available in the specified path and file name, I
want it to open another form where they can choose a data file and
Click to expand...
relink
to
that. I have the form for choosing the data file and relinking to the the
new tables complete, but I don't know how to write the code for any of the
other parts.
I am very new to Visual Basic, so please make any instructions you include
as obvious and simple as possible.
I great appreciate any help you can provide.
Beth
Click to expand...
Click to expand...
 
Hmm. I don't know how your network works, but have to say that I _really_
don't like the idea of "multiple backend files" at all. One user connected
to one, another to another.... bleaugh....


Beth said:
Thank you.
I will try this out and see how it works. I am sure some users will try to
break the db, but the 2 issues I am having are accessing the backend file
over a very unreliable network, (so the link dissappears) and using multiple
backend files that the user chooses from a master list of optional files.

Have a nice night.
Beth

Rob Oldfield said:
I'm sure there are better ways of doing a couple of the things that I'm
doing here, but the following works for me. I'm not sure how you expect
your users to try and break your db.... if they're just arbitrarily
Click to expand...
going
to
move the back-end then who knows what you suspect they're going to do?
You'll need a reference to DAO.

Anyway... in your splash form.... have something like this...

Private Sub Form_Load()
If Not LinkedTableExists Then
'If not there, forget about opening this form and open the relinking
form
DoCmd.Close acForm, "Splash"
DoCmd.OpenForm "ReLink"
Else
'..and if it is there then display the path.
'Needs to be played with to just get the path
Me.DataPath = CurrentDb.TableDefs("Orders").Connect
End If
End Sub

Function LinkedTableExists() As Boolean
'....there must be a better way of doing this....
On Error GoTo Fail:
'Just checking one table at the moment. You may want to check more
LinkedTableExists = (DCount("[OrderID]", "Orders") > -1)
Exit Function
Fail:
'If the DCount fails, then assume that the be has moved.
LinkedTableExists = False
End Function

In the ReLink form, you'll need to have some way of browsing for files or
folders. Take a look at...
http://www.mvps.org/access/api/api0001.htm or
http://www.mvps.org/access/api/api0002.htm

I've just got a text box called txtNewPath at the moment, and a button with
this code....

Private Sub btnReLink_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
'Loop through existing tables, delete the existing one, and relink
For Each tdf In db.TableDefs
'Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.DeleteObject acTable, tdf.Name
DoCmd.TransferDatabase acLink, "Microsoft Access", _
Me.txtNewPath + "\YourBE.mdb", acTable, tdf.Name, tdf.Name
End If
Next
Set db = Nothing
'Reopen the splash form so that it tries again
DoCmd.Close acForm, "ReLink"
DoCmd.OpenForm "Splash"
End Sub

Hopefully that'll give you something to work with. Good luck.


Beth said:
Hello,
I am hoping someone can give me direction on a problem I am having
Click to expand...
Click to expand...
with
my tables
Click to expand...
go
to
Click to expand...
see
if
Click to expand...
name,
I relink
Click to expand...
Click to expand...
 
I can't agree more! Sounds all bad

Rob Oldfield said:
Hmm. I don't know how your network works, but have to say that I _really_
don't like the idea of "multiple backend files" at all. One user
connected
to one, another to another.... bleaugh....


Beth said:
Thank you.
I will try this out and see how it works. I am sure some users will try to
break the db, but the 2 issues I am having are accessing the backend file
over a very unreliable network, (so the link dissappears) and using multiple
backend files that the user chooses from a master list of optional files.

Have a nice night.
Beth

Rob Oldfield said:
I'm sure there are better ways of doing a couple of the things that I'm
doing here, but the following works for me. I'm not sure how you
expect
your users to try and break your db.... if they're just arbitrarily
Click to expand...
going
to
move the back-end then who knows what you suspect they're going to do?
You'll need a reference to DAO.

Anyway... in your splash form.... have something like this...

Private Sub Form_Load()
If Not LinkedTableExists Then
'If not there, forget about opening this form and open the
relinking
form
DoCmd.Close acForm, "Splash"
DoCmd.OpenForm "ReLink"
Else
'..and if it is there then display the path.
'Needs to be played with to just get the path
Me.DataPath = CurrentDb.TableDefs("Orders").Connect
End If
End Sub

Function LinkedTableExists() As Boolean
'....there must be a better way of doing this....
On Error GoTo Fail:
'Just checking one table at the moment. You may want to check more
LinkedTableExists = (DCount("[OrderID]", "Orders") > -1)
Exit Function
Fail:
'If the DCount fails, then assume that the be has moved.
LinkedTableExists = False
End Function

In the ReLink form, you'll need to have some way of browsing for files or
folders. Take a look at...
http://www.mvps.org/access/api/api0001.htm or
http://www.mvps.org/access/api/api0002.htm

I've just got a text box called txtNewPath at the moment, and a button with
this code....

Private Sub btnReLink_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
'Loop through existing tables, delete the existing one, and relink
For Each tdf In db.TableDefs
'Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.DeleteObject acTable, tdf.Name
DoCmd.TransferDatabase acLink, "Microsoft Access", _
Me.txtNewPath + "\YourBE.mdb", acTable, tdf.Name, tdf.Name
End If
Next
Set db = Nothing
'Reopen the splash form so that it tries again
DoCmd.Close acForm, "ReLink"
DoCmd.OpenForm "Splash"
End Sub

Hopefully that'll give you something to work with. Good luck.


Hello,
I am hoping someone can give me direction on a problem I am having
Click to expand...
with
my
database. I have one database that is a front end and it links to tables
in
a backend data file. It is setup to open at a splash screen and then
Click to expand...
go
to
a
main menu. During that transition, I would like to have it check to
Click to expand...
see
if
the backend data file is available. If it is, I want to display a message
box, or simple form, that shows what the backend file location, complete
path, is. If it is not available in the specified path and file
Click to expand...
name,
I
want it to open another form where they can choose a data file and relink
to
that. I have the form for choosing the data file and relinking to
the the
new tables complete, but I don't know how to write the code for any
of the
other parts.
I am very new to Visual Basic, so please make any instructions you include
as obvious and simple as possible.
I great appreciate any help you can provide.
Beth
Click to expand...
Click to expand...
Click to expand...
 
Back
Top