Hi
I have a access database which has been working for a number of years on a
single computer.
I have just put it on a network - 1 machine has fe/be and 2 have fe

Some word documents can be opened from the home form by code from command
buttons with this example


Private Sub Command59_Click()
Dim Word As Object
Set Word = CreateObject("Word.Application")
Word.Visible = True
On Error Resume Next
Word.Documents.Open FileName:="C:\data\Dairy
Stuff\custletters\pricelist0608.doc"
If Err.Number = 5174 Then
MsgBox "YourFileField" & " not found"
Exit Sub
End If


End Sub


However the path on the main machine is now different to the other 2.

to avoid having to go into the code

My thought was to have a table in the be holding the paths with the main
machine 1 and the other two 2
The registation table on the front ends would carry 1 or2
then modify the code above to
If =DLookUp("[wordpath]","[REGISTRATIONtbl]")= 1 Then

At this point I`ve got stuck -
So is this the best approach and if so what do I need to follow with to get
the grab the path ?

TIA
David

David B wrote:
If you have 3 machines, 2 with the data mdb (backend) on the local C:
drive and another on a network, you might want to consider getting the
backend file name's path. Find a table that is linked. Let's pretend
it's called LinkedTableName.

The following function would be placed in a code module.

Public Function BEPath()
Dim s As String
Dim t As TableDef
Dim d As Database

Set d = CurrentDb

Set t = d.TableDefs("LinkedTableName")
s = Mid(t.Connect, 11) 'gets the backend file name
d.Close
Set d = Nothing

BEPath = Left(s, InStrRev(s, "\"))

End Function

If the Backend mdb is C:\Apps\Test\MyApp.mdb, the above function will
return C:\Apps\Test\.

If you were to change the line to
BEPath = Left(s, 3)
it would return C:\.

Thus you could concatenate like

Word.Documents.Open FileName:=bepath() &
"\data\DairyStuff\custletters\pricelist0608.do c" and it would be expand
to C:\data\DairyStuff\custletters\pricelist0608.doc if it were on the C:
drive.

Salad <salad (AT) oilandvinegar (DOT) com> wrote in
news:kLWdndgDaaNOzrLQnZ2dnUVZ_r2dnZ2d (AT) earthlink (DOT) com:

What's the point here of initializing a database variable? Why not
just have the function be this:

Public Function BEPath(strTable As String) As String
Dim strTemp As String

strTemp = Mid(CurrentDB.TableDefs(strTable).Connect, 11)
PathFromFileName = Left(strTemp, InStrRev(strTemp, "\")
End Function

Or, better yet, two functions:

Public Function BEDatabase(strTableName As String) As String
BEDatabase = Mid(CurrentDB.TableDefs(strTableName).Connect, 11)
End Function

Public Function PathFromFileName(strPath As String) As String
PathFromFileName = Left(strPath, InStrRev(strTemp, "\")
End Function

....and then you'd get the path to the back end with:

?PathFromFileName(BEDatabase("MyTable"))

....and you have a function, PathFromFileName, that can be used to
get the path from any file name.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Thanks for the ideas, much appreciated
DB


"David-W-Fenton" <dfassoc (AT) dfenton (DOT) com> wrote

Wow, I just use SQL Server Reporting Services to do stuff like this
(write word docs).

It's MUCH better solution than Word automation or exporting jet
reports to DOC





On Jan 13, 9:49*am, "David B" <david.bay... (AT) btconnect (DOT) com> wrote:

David-W-Fenton wrote:

Any idea why this code fails?
Dim t As TableDef
Dim s as string
Set t = CurrentDb.TableDefs("SomeTableName")
s = Mid(t.Connect, 11) 'gets the backend file name

It says Object invalid is not longer set.

Salad wrote:

You have a much better chance of figuring that out than we do. Step through
the code with the debugger and make sure the object variables are set. It's
likely to be the t variable since that's the only object variable in this
code snippet. Perhaps you supplied the wrong tabledef name ... ?

Bob Barrows wrote:

Dim d as database
set d = currentdb
and modify the Set t line to
Set t = d.tabledefs("SomeTableName")
it works.

Yet if works fine using David's code which was
s = Mid(CurrentDB.TableDefs(strTableName).Connect, 11)

Salad wrote:
Let me see if I can reproduce that in A2007 ... yes, I can easily reproduce
it. Now that I went through the exercise, I do vaguley remember this issue
but I no longer remember why it doesn't work. Something about the implicit
database variable returned by CurrentDB in memory going out of scope as soon
as the statement finishes executing. Change it to

Dim t As TableDef
Dim s As String
Set t = DBEngine(0)(0).TableDefs("tblinvoices")
s = Mid(t.Connect, 11) 'gets the backend file name

and it works fine

Bob Barrows wrote: