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" <sam_vk5ksa@hotmail.com> wrote in message
news:Jzb8b.96065$bo1.32087@news-server.bigpond.net.au...[color=blue]
> Good to know I am on the right track, however the code that I have has a[/color]
few[color=blue]
> 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 ~,[/color]
I[color=blue]
> 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[/color]
I[color=blue]
> 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[/color]
it[color=blue]
> seems like I have a long way to go!
>
> Regards
>
> Sam
>
>
>
> "Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
> news:JR98b.95979$bo1.57475@news-server.bigpond.net.au...[color=green]
> > 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"[/color]
> (not[color=green]
> > 1) in the RefreshLink lines.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users -
http://allenbrowne.com/tips.html
> >
> > "Sam" <sam_vk5ksa@hotmail.com> wrote in message
> > news:HR78b.95839$bo1.29657@news-server.bigpond.net.au...[color=darkred]
> > > Thanks for you comments Allen
> > >
> > > have come up with something that looks like it should work but[/color][/color][/color]
haven't[color=blue][color=green][color=darkred]
> > > tried it, is it possible to see if I am heading in the right[/color][/color][/color]
direction?[color=blue][color=green][color=darkred]
> > >
> > > Code as follows (in General module, name Module 1)
> > >
> > > Function CheckConnect()
> > > Dim db As Database, strnetpath As String, srtlocalpath As String,[/color]
> > strsource[color=darkred]
> > > 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 +[/color][/color]
> strfile[color=green][color=darkred]
> > > db.TableDefs(1).RefreshLink
> > > Next
> > > End If
> > > End
> > >
> > >
> > >
> > > LocalPath:
> > > MsgBox "Access has detected that the Network is Not Present" & Chr(13)[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> > > "Press OK to Load Data From Local File", vbYesCancel
> > >
> > > For i = 0 To db.TableDefs.Count - 1
> > > db.TableDefs(i).Connect = ";Database=" + strlocalpath +[/color][/color][/color]
strfile[color=blue][color=green][color=darkred]
> > > db.TableDefs(1).RefreshLink
> > > Next
> > >
> > > End Function
> > >
> > >
> > > Regards
> > >
> > > Sam
> > > "Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
> > > news:6jU7b.94053$bo1.20324@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[/color][/color][/color]
Dir().[color=blue]
> If[color=green][color=darkred]
> > > > it's there, then loop through the TableDefs, and the non-system[/color][/color][/color]
files[color=blue][color=green]
> > that[color=darkred]
> > > > have a Connect property that's different, set it to the network[/color][/color][/color]
path.[color=blue][color=green][color=darkred]
> > > >
> > > > If the Dir on the network back end fails, you probably want to pop[/color][/color][/color]
up[color=blue]
> a[color=green][color=darkred]
> > > > MsgBox() asking whether to use the local copy of the data. If the[/color][/color][/color]
user[color=blue][color=green][color=darkred]
> > > > agrees, then set the Connect property to the local data file. You[/color][/color][/color]
may[color=blue][color=green]
> > also[color=darkred]
> > > > want to modify the title bar of the application to show it's a local[/color]
> > copy[color=darkred]
> > > > not the real data that is being updated. Use
> > > > dbEngine(0)(0).Properties("AppTitle"), and[/color][/color]
> Application.RefreshTitleBar.[color=green][color=darkred]
> > > >
> > > > Presumably you will store the usual network path in a table so you[/color][/color]
> know[color=green][color=darkred]
> > > > where to look again next startup.
> > > >
> > > > --
> > > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > > Tips for Access users -
http://allenbrowne.com/tips.html
> > > >
> > > > "Sam" <sam_vk5ksa@hotmail.com> wrote in message
> > > > news:CkT7b.93928$bo1.55697@news-server.bigpond.net.au...
> > > > > I have developed a small database which is setup with the front[/color][/color][/color]
end[color=blue][color=green]
> > and[color=darkred]
> > > > > backend files.
> > > > >
> > > > > There are 12 linked tables withe the datafile located on our[/color][/color][/color]
server.[color=blue][color=green][color=darkred]
> > > > >
> > > > > Now the boss wants to be able to take a laptop offsite and still[/color][/color]
> have[color=green][color=darkred]
> > > > > access, (even if read only) to the main data file (to be able to[/color][/color][/color]
add[color=blue][color=green][color=darkred]
> > > > records
> > > > > in 1 table would also be useful).
> > > > >
> > > > > I would like to be able to get the user to press a command button[/color]
> > which[color=darkred]
> > > > > copies the data file into a local directory and updates the link[/color][/color]
> paths[color=green][color=darkred]
> > > for
> > > > > the tables. This also incudes the linked images I have for the[/color][/color][/color]
forms[color=blue][color=green][color=darkred]
> > > > >
> > > > > I can handle getting information that is added back into the main[/color]
> > file,[color=darkred]
> > > > just
> > > > > the auto linking (user friendly) of the tables has me.
> > > > >
> > > > > Any help?, I have looked at the code suggested on Allen Browne's[/color][/color][/color]
tip[color=blue][color=green][color=darkred]
> > > site
> > > > > but I can't see where that is going or how to adapt it to my use.
> > > > >
> > > > > Regards
> > > > >
> > > > > Sam[/color]
> >
> >[/color]
>
>[/color]
