Split Database links

[Sam]

I have developed a small database which is setup with the front end and
backend files.

There are 12 linked tables withe the datafile located on our server.

Now the boss wants to be able to take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add records
in 1 table would also be useful).

I would like to be able to get the user to press a command button which
copies the data file into a local directory and updates the link paths for
the tables. This also incudes the linked images I have for the forms

I can handle getting information that is added back into the main file, just
the auto linking (user friendly) of the tables has me.

Any help?, I have looked at the code suggested on Allen Browne's tip site
but I can't see where that is going or how to adapt it to my use.

Regards

Sam

[Sam]

Thanks for you comments Allen

have come up with something that looks like it should work but haven't
tried it, is it possible to see if I am heading in the right direction?

Code as follows (in General module, name Module 1)

Function CheckConnect()
Dim db As Database, strnetpath As String, srtlocalpath As String, strsource
As String
Dim strfile As String
Dim i As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
strfile = "V3 Data.mdb"
strlocalpath = "C:\My Documents\"
strnetpath = "X:\AA PUBLIC FILES\Database\"
'check network path
If IsNull(Dir(strnetpath)) Then GoTo LocalPath
'else if db.path<> strnetpath then
For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strnetpath + strfile
db.TableDefs(1).RefreshLink
Next
End If
End

LocalPath:
MsgBox "Access has detected that the Network is Not Present" & Chr(13) &
"Press OK to Load Data From Local File", vbYesCancel

For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strlocalpath + strfile
db.TableDefs(1).RefreshLink
Next

End Function
Regards

Sam
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:6j*******************@news-server.bigpond.net.au...

It's a matter of setting the Connect property of each TableDef, remembering to RefreshLink.

On startup, you want to see if the data file is available. Use Dir(). If
it's there, then loop through the TableDefs, and the non-system files that
have a Connect property that's different, set it to the network path.

If the Dir on the network back end fails, you probably want to pop up a
MsgBox() asking whether to use the local copy of the data. If the user
agrees, then set the Connect property to the local data file. You may also
want to modify the title bar of the application to show it's a local copy
not the real data that is being updated. Use
dbEngine(0)(0).Properties("AppTitle"), and Application.RefreshTitleBar.

Presumably you will store the usual network path in a table so you know
where to look again next startup.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Sam" <sa********@hotmail.com> wrote in message
news:Ck*******************@news-server.bigpond.net.au...

I have developed a small database which is setup with the front end and
backend files.

There are 12 linked tables withe the datafile located on our server.

Now the boss wants to be able to take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add

records

in 1 table would also be useful).

I would like to be able to get the user to press a command button which
copies the data file into a local directory and updates the link paths for the tables. This also incudes the linked images I have for the forms

I can handle getting information that is added back into the main file,

just

the auto linking (user friendly) of the tables has me.

Any help?, I have looked at the code suggested on Allen Browne's tip site but I can't see where that is going or how to adapt it to my use.

Regards

Sam

[Allen Browne]

Yes, that's the general idea.

With each TableDef, check:
- it's name does not start with "MSYS" (the hidden system tables) or "~"
(deleted tables);
- it has a Connect property (i.e. you do not want to set it for locacl
tables).

There's a bit of debugging to do, e.g. you will need to use the MsgBox()
function (rather than statement) in the LocalPath code, and use an "i" (not
1) in the RefreshLink lines.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Sam" <sa********@hotmail.com> wrote in message
news:HR*******************@news-server.bigpond.net.au...

Thanks for you comments Allen

have come up with something that looks like it should work but haven't
tried it, is it possible to see if I am heading in the right direction?

Code as follows (in General module, name Module 1)

Function CheckConnect()
Dim db As Database, strnetpath As String, srtlocalpath As String, strsource As String
Dim strfile As String
Dim i As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
strfile = "V3 Data.mdb"
strlocalpath = "C:\My Documents\"
strnetpath = "X:\AA PUBLIC FILES\Database\"
'check network path
If IsNull(Dir(strnetpath)) Then GoTo LocalPath
'else if db.path<> strnetpath then
For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strnetpath + strfile
db.TableDefs(1).RefreshLink
Next
End If
End

LocalPath:
MsgBox "Access has detected that the Network is Not Present" & Chr(13) &
"Press OK to Load Data From Local File", vbYesCancel

For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strlocalpath + strfile
db.TableDefs(1).RefreshLink
Next

End Function
Regards

Sam
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:6j*******************@news-server.bigpond.net.au...

It's a matter of setting the Connect property of each TableDef,

remembering

to RefreshLink.

On startup, you want to see if the data file is available. Use Dir(). If
it's there, then loop through the TableDefs, and the non-system files that

have a Connect property that's different, set it to the network path.

If the Dir on the network back end fails, you probably want to pop up a
MsgBox() asking whether to use the local copy of the data. If the user
agrees, then set the Connect property to the local data file. You may also want to modify the title bar of the application to show it's a local copy not the real data that is being updated. Use
dbEngine(0)(0).Properties("AppTitle"), and Application.RefreshTitleBar.

Presumably you will store the usual network path in a table so you know
where to look again next startup.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Sam" <sa********@hotmail.com> wrote in message
news:Ck*******************@news-server.bigpond.net.au...

I have developed a small database which is setup with the front end and backend files.

There are 12 linked tables withe the datafile located on our server.

Now the boss wants to be able to take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add

records

in 1 table would also be useful).

I would like to be able to get the user to press a command button which copies the data file into a local directory and updates the link paths for the tables. This also incudes the linked images I have for the forms

I can handle getting information that is added back into the main

file, just

the auto linking (user friendly) of the tables has me.

Any help?, I have looked at the code suggested on Allen Browne's tip

site but I can't see where that is going or how to adapt it to my use.

Regards

Sam

[Allen Browne]

1. Use error handling to recover.
If you don't know how to use error handling, see:
http://allenbrowne.com/ser-23a.html

2. Make sure you have excluded the system, temp, and local tables. Assign
the value to a string variable, and Debug.Print it to check it's okay. For
example the path needs the trailing slash. (You might also try the ampersand
for concatenation.)

3. Names of tables
Dim tdf As DAO.TableDef
For each tdf In db.TableDefs
If Left(tdf.Name, 4) = "MSys" ...

4. ???

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Sam" <sa********@hotmail.com> wrote in message
news:Jz*******************@news-server.bigpond.net.au...

Good to know I am on the right track, however the code that I have has a few errors
1. The line: If isnull(Dir(strnetpath)) Then GoTo LocalPath
gives an error (when logged off the server): Device not available
2. The line : db.TableDefs(i).Connect = ";Database=" +
strlocalpath + strfile also gives an error "Invalid operation"
3. You mentioned the system and hidden tables starting with msys and ~, I can't find a way to look at the start of a table name. I have no local
tables, so the need to not reconnect those is not required.
4. The Line I have in as a statement is like it is as I wanted to show I want to put the code there but havn't found how to write what I want it to
do (yet) ( 'else if db.path<> strnetpath then)
Any help, I am trying to brute force my way though learning the code but it seems like I have a long way to go!

Regards

Sam

"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:JR*******************@news-server.bigpond.net.au...

Yes, that's the general idea.

With each TableDef, check:
- it's name does not start with "MSYS" (the hidden system tables) or "~"
(deleted tables);
- it has a Connect property (i.e. you do not want to set it for locacl
tables).

There's a bit of debugging to do, e.g. you will need to use the MsgBox()
function (rather than statement) in the LocalPath code, and use an "i" (not

1) in the RefreshLink lines.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Sam" <sa********@hotmail.com> wrote in message
news:HR*******************@news-server.bigpond.net.au...

Thanks for you comments Allen

have come up with something that looks like it should work but haven't tried it, is it possible to see if I am heading in the right direction?
Code as follows (in General module, name Module 1)

Function CheckConnect()
Dim db As Database, strnetpath As String, srtlocalpath As String,

strsource

As String
Dim strfile As String
Dim i As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
strfile = "V3 Data.mdb"
strlocalpath = "C:\My Documents\"
strnetpath = "X:\AA PUBLIC FILES\Database\"
'check network path
If IsNull(Dir(strnetpath)) Then GoTo LocalPath
'else if db.path<> strnetpath then
For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strnetpath + strfile db.TableDefs(1).RefreshLink
Next
End If
End

LocalPath:
MsgBox "Access has detected that the Network is Not Present" & Chr(13) & "Press OK to Load Data From Local File", vbYesCancel

For i = 0 To db.TableDefs.Count - 1
db.TableDefs(i).Connect = ";Database=" + strlocalpath + strfile db.TableDefs(1).RefreshLink
Next

End Function
Regards

Sam
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:6j*******************@news-server.bigpond.net.au...
> It's a matter of setting the Connect property of each TableDef,
remembering
> to RefreshLink.
>
> On startup, you want to see if the data file is available. Use Dir(). If

> it's there, then loop through the TableDefs, and the non-system

files

that

> have a Connect property that's different, set it to the network

path. >
> If the Dir on the network back end fails, you probably want to pop up

a > MsgBox() asking whether to use the local copy of the data. If the

user > agrees, then set the Connect property to the local data file. You may also

> want to modify the title bar of the application to show it's a local

copy

> not the real data that is being updated. Use
> dbEngine(0)(0).Properties("AppTitle"), and Application.RefreshTitleBar. >
> Presumably you will store the usual network path in a table so you know > where to look again next startup.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
>
> "Sam" <sa********@hotmail.com> wrote in message
> news:Ck*******************@news-server.bigpond.net.au...
> > I have developed a small database which is setup with the front

end and

> > backend files.
> >
> > There are 12 linked tables withe the datafile located on our

server. > >
> > Now the boss wants to be able to take a laptop offsite and still

have > > access, (even if read only) to the main data file (to be able to add > records
> > in 1 table would also be useful).
> >
> > I would like to be able to get the user to press a command button

which

> > copies the data file into a local directory and updates the link paths for
> > the tables. This also incudes the linked images I have for the forms > >
> > I can handle getting information that is added back into the main

file,

> just
> > the auto linking (user friendly) of the tables has me.
> >
> > Any help?, I have looked at the code suggested on Allen Browne's tip site
> > but I can't see where that is going or how to adapt it to my use.
> >
> > Regards
> >
> > Sam