What if the back-end "moves"?

I realize that this isn't the main reason why people split databases into a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to keep
track of their patients and generate reports. Eventually it will be run on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it with
several medical residents and they are carrying their copies on portable USB
flash memory keys (each one essentially a separate Access .mdb file). The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all. What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends (which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John

"John S. Ford, MD" wrote in message

I realize that this isn't the main reason why people split databases
into a front-end and a back-end but here is my problem. I'm using
Access 2000.

I'm developing a database app to be used in a hospital for residents
to keep track of their patients and generate reports. Eventually it
will be run on a main server and each work station will contain the
front-end and the server will carry the back-end. For now however,
I'm "beta" testing it with several medical residents and they are
carrying their copies on portable USB flash memory keys (each one
essentially a separate Access .mdb file). The testers are simply
running it directly off the keys. I'm just trying to iron out the
usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms,
queries, modules etc. but the underlying table structure hasn't
changed at all. What I want to do is split the database on each of
the resident's own copies. That way, if I make a change to the
"front-end" objects, I can simply replace the tester's front-ends and
leave their individual back-ends (which contain their underlying
data) unchanged. This way their patient lists won't be disrupted
while they're testing the program.

The problem is that different computers in the hospital assign
different drive identifications to their keys (some assign G:/, some
assign E:/). Will Access 2000 have a problem finding the back-end
(even though the front-end and back-end will be sitting in the same
folder in the memory key) if the residents run the program on
different computers? Is there a way around this?

Any help greatly appreciated!

You could use a modified version of the table-relinking code at

http://www.mvps.org/access/tables/tbl0009.htm

to relink the tables at startup. If the database pointed to by a linked
table isn't found, you would want to automatically check for it in the
same folder as the front-end, before prompting the user to locate it via
a browse window.

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

(please reply to the newsgroup)

John.

One option is that the users, in the meantime, can manually re-link
using the Linked Table Manager if the location of the files is changed.

Another option is to incorprate code into your application, which will
prompt the user to locate the backend .mdb if it can't find it. Here is
some example code, which I will quote in full because I can't remember
where I originally got it from :-)

----------------
Private Sub Form_Load()
If CheckLinks() = False Then
If RelinkTables() = False Then
DoCmd.Close acForm, "Startup"
CloseCurrentDatabase
End If
End If
End Sub


'Store all of the following code in a module named RefreshLinks (or
something similar.)

Option Explicit
Option Compare Database

Public Const conAppTitle = "MyProgram"
Public Const DataMdb = "MyDat.mdb"
Public Const PgmMdb = "MyPgm.mdb"
Public Const SomeTable = "MyTableName"

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA"
(pOpenfilename As OPENFILENAME) As Boolean
Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA"
(pOpenfilename As OPENFILENAME) As Boolean

Type MSA_OPENFILENAME
strFilter As String
lngFilterIndex As Long
strInitialDir As String
strInitialFile As String
strDialogTitle As String
strDefaultExtension As String
lngFlags As Long
strFullPathReturned As String
strFileNameReturned As String
intFileOffset As Integer
intFileExtension As Integer
End Type

Const ALLFILES = "All Files"

Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Public Function CheckLinks() As Boolean
'Check links to the DataMdb database; returns True if links are OK.
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

'Open a linked table to see if connection information is correct.
On Error Resume Next
Set rst = dbs.OpenRecordset(SomeTable)

'If there's no error, return True.
If Err = 0 Then
CheckLinks = True
Else
CheckLinks = False
End If

End Function

Public Function RelinkTables() As Boolean
Dim strSearchPath As String, strFileName As String, intError As
Integer,
strError As String

Const conMaxTables = 8
Const conNonExistentTable = 3011
Const conNotNorthwind = 3078
Const conNwindNotFound = 3024
Const conAccessDenied = 3051
Const conReadOnlyDatabase = 3027

Dim strPath As String
Dim strNewDB As String

'Look for the DataMdb database in the same folder.
strSearchPath = CurrentDb().Name
strSearchPath = Left$(strSearchPath, Len(strSearchPath) -
Len(Dir(strSearchPath)))

If (Dir(strSearchPath & DataMdb) "") Then
strFileName = strSearchPath & DataMdb
Else
'Can't find DataMdb, so display the Open dialog box.
MsgBox "Can't find linked tables in the " & DataMdb & " database." &
vbCrLf & "You must locate " & DataMdb & " in order to use " & conAppTitle &
".", vbExclamation
strFileName = FindDatFile(strSearchPath)
If strFileName = "" Then
strError = "Sorry, you must locate " & DataMdb & " to open " &
conAppTitle & "."
GoTo Exit_Failed
End If
End If

'Fix the links.
If RefreshLinks(strFileName, strSearchPath) = True Then
RelinkTables = True
Exit Function
End If

' If it failed, display an error.
Select Case Err
Case conNonExistentTable, conNotNorthwind
strError = "File '" & strFileName & "' does not contain the
required
" & DataMdb & " tables."
Case Err = conNwindNotFound
strError = "You can't run " & conAppTitle & " until you locate
the "
& DataMdb & " database."
Case Err = conAccessDenied
strError = "Couldn't open " & strFileName & " because it is
read-only or located on a read-only share."
Case Err = conReadOnlyDatabase
strError = "Can't relink tables because " & conAppTitle & " is
read-only or is located on a read-only share."
Case Else
strError = Err.Description
End Select

Exit_Failed:
MsgBox strError, vbCritical
RelinkTables = False

End Function

Private Function RefreshLinks(strFileName As String, strSearchPath As
String) As Boolean

Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb()

For Each tdf In dbs.TableDefs
If Len(tdf.Connect) 0 And (tdf.Attributes And dbAttachedTable) Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf

RefreshLinks = True

End Function


Function FindDatFile(strSearchPath) As String
'Displays the Open dialog box for the user to locate the DataMdb
database. Returns the full path to it.
Dim msaof As MSA_OPENFILENAME
msaof.strDialogTitle = "Where Is " & DataMdb & "?"
msaof.strInitialDir = strSearchPath
msaof.strFilter = MSA_CreateFilterString("Databases", "*.mdb")

MSA_GetOpenFileName msaof

FindDatFile = Trim(msaof.strFullPathReturned)

End Function

Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
Dim strFilter As String, intRet As Integer, intNum As Integer

intNum = UBound(varFilt)
If (intNum -1) Then
For intRet = 0 To intNum
strFilter = strFilter & varFilt(intRet) & vbNullChar
Next
If intNum Mod 2 = 0 Then
strFilter = strFilter & "*.*" & vbNullChar
End If
strFilter = strFilter & vbNullChar
Else
strFilter = ""
End If

MSA_CreateFilterString = strFilter
End Function

Function MSA_ConvertFilterString(strFilterIn As String) As String
Dim strFilter As String, intNum As Integer, intPos As Integer,
intLastPos As Integer

strFilter = ""
intNum = 0
intPos = 1
intLastPos = 1

Do
intPos = InStr(intLastPos, strFilterIn, "|")
If (intPos intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos -
intLastPos) & vbNullChar
intNum = intNum + 1
intLastPos = intPos + 1
ElseIf (intPos = intLastPos) Then
intLastPos = intPos + 1
End If
Loop Until (intPos = 0)

intPos = Len(strFilterIn)
If (intPos = intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos -
intLastPos + 1) & vbNullChar
intNum = intNum + 1
End If

If intNum Mod 2 = 1 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

If strFilter "" Then
strFilter = strFilter & vbNullChar
End If

MSA_ConvertFilterString = strFilter
End Function

Private Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer
Dim of As OPENFILENAME, intRet As Integer

MSAOF_to_OF msaof, of
of.Flags = of.Flags Or OFN_HIDEREADONLY
intRet = GetSaveFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetSaveFileName = intRet
End Function

Function MSA_SimpleGetSaveFileName() As String
Dim msaof As MSA_OPENFILENAME, intRet As Integer, strRet As String

intRet = MSA_GetSaveFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetSaveFileName = strRet
End Function

Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
Dim of As OPENFILENAME, intRet As Integer

MSAOF_to_OF msaof, of
intRet = GetOpenFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetOpenFileName = intRet
End Function

Function MSA_SimpleGetOpenFileName() As String
Dim msaof As MSA_OPENFILENAME, intRet As Integer, strRet As String

intRet = MSA_GetOpenFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetOpenFileName = strRet
End Function

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)
'This sub converts from the Win32 structure to the Microsoft Access
structure.
msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile,
vbNullChar) - 1)
msaof.strFileNameReturned = of.lpstrFileTitle
msaof.intFileOffset = of.nFileOffset
msaof.intFileExtension = of.nFileExtension
End Sub

Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)
'This sub converts from the Microsoft Access structure to the Win32
structure.
Dim strFile As String * 512
of.hwndOwner = Application.hWndAccessApp
of.hInstance = 0
of.lpstrCustomFilter = 0
of.nMaxCustrFilter = 0
of.lpfnHook = 0
of.lpTemplateName = 0
of.lCustrData = 0

If msaof.strFilter = "" Then
of.lpstrFilter = MSA_CreateFilterString(ALLFILES)
Else
of.lpstrFilter = msaof.strFilter
End If
of.nFilterIndex = msaof.lngFilterIndex

of.lpstrFile = msaof.strInitialFile _
& String(512 - Len(msaof.strInitialFile), 0)
of.nMaxFile = 511

of.lpstrFileTitle = String(512, 0)
of.nMaxFileTitle = 511

of.lpstrTitle = msaof.strDialogTitle

of.lpstrInitialDir = msaof.strInitialDir

of.lpstrDefExt = msaof.strDefaultExtension

of.Flags = msaof.lngFlags

of.lStructSize = Len(of)
End Sub

--------------------------

--
Steve Schapel, Microsoft Access MVP


John S. Ford, MD wrote:
I realize that this isn't the main reason why people split databases into a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to keep
track of their patients and generate reports. Eventually it will be run on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it with
several medical residents and they are carrying their copies on portable USB
flash memory keys (each one essentially a separate Access .mdb file). The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all. What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends (which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John

John,

I assume (hope!) that when this is located on the server, all of the
"backends" will be combined into one? Incidentally, I like the idea
of running your personal copy off a USB stick ... very cute.


The general rule is to lways use UNC format
(\\ServerShare\Folder(s)\MyDatabase.mdb) for linking rather than
mapped drives to avoid the issues that you're facing. Having siad
that, I'm not sure how a USB stick is addressed. It can be named and
it can be shared but i didn't have time to try all the combinations

I agree though that splitting it on the stick and linking to a mapped
drive won't work as all the computers will assign a different drive
letter automatically

Interesting, wish I had more time to experiment

Brett




On Fri, 12 Nov 2004 10:49:11 -0800, "John S. Ford, MD"
wrote:

I realize that this isn't the main reason why people split databases into a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to keep
track of their patients and generate reports. Eventually it will be run on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it with
several medical residents and they are carrying their copies on portable USB
flash memory keys (each one essentially a separate Access .mdb file). The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all. What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends (which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John



Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer

Yes Brett. Eventually there will be just one back end located on the
hospital's main server. What is UNC format?

John

"Brett Collings [429338]" wrote in message
...
John,

I assume (hope!) that when this is located on the server, all of the
"backends" will be combined into one? Incidentally, I like the idea
of running your personal copy off a USB stick ... very cute.


The general rule is to lways use UNC format
(\\ServerShare\Folder(s)\MyDatabase.mdb) for linking rather than
mapped drives to avoid the issues that you're facing. Having siad
that, I'm not sure how a USB stick is addressed. It can be named and
it can be shared but i didn't have time to try all the combinations

I agree though that splitting it on the stick and linking to a mapped
drive won't work as all the computers will assign a different drive
letter automatically

Interesting, wish I had more time to experiment

Brett




On Fri, 12 Nov 2004 10:49:11 -0800, "John S. Ford, MD"
wrote:

I realize that this isn't the main reason why people split databases into
a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to
keep
track of their patients and generate reports. Eventually it will be run
on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it
with
several medical residents and they are carrying their copies on portable
USB
flash memory keys (each one essentially a separate Access .mdb file).
The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all.
What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends
(which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory
key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John



Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer

Dirk, thanks for the code. It's very difficult and and obscure for me to
understand but I'm going to try to incorporate it into my app for a trial
run. Using the OpenDialogue code also seems beyond my meager programming
skills but I'll look into it. How do you specify the code to
"automatically" check for the tables in the same folder as the front-end?

John


You could use a modified version of the table-relinking code at

http://www.mvps.org/access/tables/tbl0009.htm

to relink the tables at startup. If the database pointed to by a linked
table isn't found, you would want to automatically check for it in the
same folder as the front-end, before prompting the user to locate it via
a browse window.

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

(please reply to the newsgroup)

Thanks for the suggestion and the code. I'm going to have to study the help
files for references to the Linked Table Manager.

John

"Steve Schapel" wrote in message
...
John.

One option is that the users, in the meantime, can manually re-link
using the Linked Table Manager if the location of the files is changed.

Another option is to incorprate code into your application, which will
prompt the user to locate the backend .mdb if it can't find it. Here is
some example code, which I will quote in full because I can't remember
where I originally got it from :-)

----------------
Private Sub Form_Load()
If CheckLinks() = False Then
If RelinkTables() = False Then
DoCmd.Close acForm, "Startup"
CloseCurrentDatabase
End If
End If
End Sub


'Store all of the following code in a module named RefreshLinks (or
something similar.)

Option Explicit
Option Compare Database

Public Const conAppTitle = "MyProgram"
Public Const DataMdb = "MyDat.mdb"
Public Const PgmMdb = "MyPgm.mdb"
Public Const SomeTable = "MyTableName"

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA"
(pOpenfilename As OPENFILENAME) As Boolean
Declare Function GetSaveFileName Lib "comdlg32.dll" Alias
"GetSaveFileNameA"
(pOpenfilename As OPENFILENAME) As Boolean

Type MSA_OPENFILENAME
strFilter As String
lngFilterIndex As Long
strInitialDir As String
strInitialFile As String
strDialogTitle As String
strDefaultExtension As String
lngFlags As Long
strFullPathReturned As String
strFileNameReturned As String
intFileOffset As Integer
intFileExtension As Integer
End Type

Const ALLFILES = "All Files"

Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Public Function CheckLinks() As Boolean
'Check links to the DataMdb database; returns True if links are OK.
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

'Open a linked table to see if connection information is correct.
On Error Resume Next
Set rst = dbs.OpenRecordset(SomeTable)

'If there's no error, return True.
If Err = 0 Then
CheckLinks = True
Else
CheckLinks = False
End If

End Function

Public Function RelinkTables() As Boolean
Dim strSearchPath As String, strFileName As String, intError As
Integer,
strError As String

Const conMaxTables = 8
Const conNonExistentTable = 3011
Const conNotNorthwind = 3078
Const conNwindNotFound = 3024
Const conAccessDenied = 3051
Const conReadOnlyDatabase = 3027

Dim strPath As String
Dim strNewDB As String

'Look for the DataMdb database in the same folder.
strSearchPath = CurrentDb().Name
strSearchPath = Left$(strSearchPath, Len(strSearchPath) -
Len(Dir(strSearchPath)))

If (Dir(strSearchPath & DataMdb) "") Then
strFileName = strSearchPath & DataMdb
Else
'Can't find DataMdb, so display the Open dialog box.
MsgBox "Can't find linked tables in the " & DataMdb & " database."
&
vbCrLf & "You must locate " & DataMdb & " in order to use " & conAppTitle
&
".", vbExclamation
strFileName = FindDatFile(strSearchPath)
If strFileName = "" Then
strError = "Sorry, you must locate " & DataMdb & " to open " &
conAppTitle & "."
GoTo Exit_Failed
End If
End If

'Fix the links.
If RefreshLinks(strFileName, strSearchPath) = True Then
RelinkTables = True
Exit Function
End If

' If it failed, display an error.
Select Case Err
Case conNonExistentTable, conNotNorthwind
strError = "File '" & strFileName & "' does not contain the
required
" & DataMdb & " tables."
Case Err = conNwindNotFound
strError = "You can't run " & conAppTitle & " until you locate
the "
& DataMdb & " database."
Case Err = conAccessDenied
strError = "Couldn't open " & strFileName & " because it is
read-only or located on a read-only share."
Case Err = conReadOnlyDatabase
strError = "Can't relink tables because " & conAppTitle & " is
read-only or is located on a read-only share."
Case Else
strError = Err.Description
End Select

Exit_Failed:
MsgBox strError, vbCritical
RelinkTables = False

End Function

Private Function RefreshLinks(strFileName As String, strSearchPath As
String) As Boolean

Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb()

For Each tdf In dbs.TableDefs
If Len(tdf.Connect) 0 And (tdf.Attributes And dbAttachedTable)
Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf

RefreshLinks = True

End Function


Function FindDatFile(strSearchPath) As String
'Displays the Open dialog box for the user to locate the DataMdb
database. Returns the full path to it.
Dim msaof As MSA_OPENFILENAME
msaof.strDialogTitle = "Where Is " & DataMdb & "?"
msaof.strInitialDir = strSearchPath
msaof.strFilter = MSA_CreateFilterString("Databases", "*.mdb")

MSA_GetOpenFileName msaof

FindDatFile = Trim(msaof.strFullPathReturned)

End Function

Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
Dim strFilter As String, intRet As Integer, intNum As Integer

intNum = UBound(varFilt)
If (intNum -1) Then
For intRet = 0 To intNum
strFilter = strFilter & varFilt(intRet) & vbNullChar
Next
If intNum Mod 2 = 0 Then
strFilter = strFilter & "*.*" & vbNullChar
End If
strFilter = strFilter & vbNullChar
Else
strFilter = ""
End If

MSA_CreateFilterString = strFilter
End Function

Function MSA_ConvertFilterString(strFilterIn As String) As String
Dim strFilter As String, intNum As Integer, intPos As Integer,
intLastPos As Integer

strFilter = ""
intNum = 0
intPos = 1
intLastPos = 1

Do
intPos = InStr(intLastPos, strFilterIn, "|")
If (intPos intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos -
intLastPos) & vbNullChar
intNum = intNum + 1
intLastPos = intPos + 1
ElseIf (intPos = intLastPos) Then
intLastPos = intPos + 1
End If
Loop Until (intPos = 0)

intPos = Len(strFilterIn)
If (intPos = intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos -
intLastPos + 1) & vbNullChar
intNum = intNum + 1
End If

If intNum Mod 2 = 1 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

If strFilter "" Then
strFilter = strFilter & vbNullChar
End If

MSA_ConvertFilterString = strFilter
End Function

Private Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer
Dim of As OPENFILENAME, intRet As Integer

MSAOF_to_OF msaof, of
of.Flags = of.Flags Or OFN_HIDEREADONLY
intRet = GetSaveFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetSaveFileName = intRet
End Function

Function MSA_SimpleGetSaveFileName() As String
Dim msaof As MSA_OPENFILENAME, intRet As Integer, strRet As String

intRet = MSA_GetSaveFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetSaveFileName = strRet
End Function

Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
Dim of As OPENFILENAME, intRet As Integer

MSAOF_to_OF msaof, of
intRet = GetOpenFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetOpenFileName = intRet
End Function

Function MSA_SimpleGetOpenFileName() As String
Dim msaof As MSA_OPENFILENAME, intRet As Integer, strRet As String

intRet = MSA_GetOpenFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetOpenFileName = strRet
End Function

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)
'This sub converts from the Win32 structure to the Microsoft Access
structure.
msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile,
vbNullChar) - 1)
msaof.strFileNameReturned = of.lpstrFileTitle
msaof.intFileOffset = of.nFileOffset
msaof.intFileExtension = of.nFileExtension
End Sub

Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)
'This sub converts from the Microsoft Access structure to the Win32
structure.
Dim strFile As String * 512
of.hwndOwner = Application.hWndAccessApp
of.hInstance = 0
of.lpstrCustomFilter = 0
of.nMaxCustrFilter = 0
of.lpfnHook = 0
of.lpTemplateName = 0
of.lCustrData = 0

If msaof.strFilter = "" Then
of.lpstrFilter = MSA_CreateFilterString(ALLFILES)
Else
of.lpstrFilter = msaof.strFilter
End If
of.nFilterIndex = msaof.lngFilterIndex

of.lpstrFile = msaof.strInitialFile _
& String(512 - Len(msaof.strInitialFile), 0)
of.nMaxFile = 511

of.lpstrFileTitle = String(512, 0)
of.nMaxFileTitle = 511

of.lpstrTitle = msaof.strDialogTitle

of.lpstrInitialDir = msaof.strInitialDir

of.lpstrDefExt = msaof.strDefaultExtension

of.Flags = msaof.lngFlags

of.lStructSize = Len(of)
End Sub

--------------------------

--
Steve Schapel, Microsoft Access MVP


John S. Ford, MD wrote:
I realize that this isn't the main reason why people split databases
into a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to
keep
track of their patients and generate reports. Eventually it will be run
on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it
with
several medical residents and they are carrying their copies on portable
USB
flash memory keys (each one essentially a separate Access .mdb file).
The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms,
queries,
modules etc. but the underlying table structure hasn't changed at all.
What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends
(which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory
key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John

John

From the Database Window, go to the Tools|Database Utilities|Linked
Table Manager menu. You wouldn't want people mucking around with this
once your production version is out and about. But I expect it would be
reasonably easy to show the selected people doing your "beta testing"
how to use this.

--
Steve Schapel, Microsoft Access MVP


John S. Ford, MD wrote:
Thanks for the suggestion and the code. I'm going to have to study the help
files for references to the Linked Table Manager.

John

On Fri, 12 Nov 2004 23:32:11 -0800, "John S. Ford, MD"
wrote:

Yes Brett. Eventually there will be just one back end located on the
hospital's main server. What is UNC format?

John

UNC = Universal Naming Convention

The 'convention' is the format of the path command to a physical
location for files. Drive Mapping is a virtual location and as you've
found is unreliable.

In practical terms, it is easy to demonstrate. Let's assume
- your own personal computer's name is John, this is name you see in
Network Neighbourhood.

- your file is in
C:\My Documents\MedicalFiles\Workstuff\Databases\Interns .mdb

You can "Map" the folder Workstuff to say S:\ and it will appear in My
Computer as a drive with the notation
+ [] Workstuff on 'John' (S
.... and you would then be linking to S:\Databases\Interns.mdb. That's
what I mean by it not being a physical address as S: isn't a location,
just an alias.

The UNC address on the other hand identifies the actual physical
location of the database and always begins with the \\ characters. In
the case of our example, you use Network Neighbourhood when using
Linked Table Manager and you will end up with this as the address

\\John\My Documents\MedicalFiles\Workstuff\Databases\Interns .mdb

.... it's hard-wired all the links to the actual physical location.
Unless you move the backend, this can't be broken. It doesn't matter
what machine or where on the network you are linking from ... it
always is right.

I'm on a roll here, so I can't sign off without commenting on the
performance of databases on networks related to the location of the
database backend in the folder tree. The further away from the root
directory of the computer disk, the slower the database will run. I
used an unlikely path for where your file might be to save space in
the explanation but the reality is that in an Windows 2000 +
environment, that file will be at

C:\Documents and Settings\%UserName%\My
Documents\MedicalFiles\Workstuff\Databases\Interns .mdb

This will be much slower to read, write and query than if all your
database backends were in

C:\Documents and Settings\%UserName%\DataBackEnds

That'll do
Brett




"Brett Collings [429338]" wrote in message
.. .
John,

I assume (hope!) that when this is located on the server, all of the
"backends" will be combined into one? Incidentally, I like the idea
of running your personal copy off a USB stick ... very cute.


The general rule is to lways use UNC format
(\\ServerShare\Folder(s)\MyDatabase.mdb) for linking rather than
mapped drives to avoid the issues that you're facing. Having siad
that, I'm not sure how a USB stick is addressed. It can be named and
it can be shared but i didn't have time to try all the combinations

I agree though that splitting it on the stick and linking to a mapped
drive won't work as all the computers will assign a different drive
letter automatically

Interesting, wish I had more time to experiment

Brett




On Fri, 12 Nov 2004 10:49:11 -0800, "John S. Ford, MD"
wrote:

I realize that this isn't the main reason why people split databases into
a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to
keep
track of their patients and generate reports. Eventually it will be run
on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it
with
several medical residents and they are carrying their copies on portable
USB
flash memory keys (each one essentially a separate Access .mdb file).
The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all.
What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends
(which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory
key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John



Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer


Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer

John,

I have the answer! I chewed this one over for about an hour after the
last posting and the answer is so simple, it's embarrassing.

A: Set up each of the Beta-tester's BE (BackEnd) files on the Server!
With Linked Table Manager then link each of the tester's FE (FrontEnd)
files to his/her *own* BE using the UNC format. Remember, you get the
UNC address by linking using the "My Network Places" tree.

This way quite a few good things happen.
- The production environment gets tested as well
- Speed and functionality via the network is tested
- No matter what machine they use in the hospital, they will be
hard-wired to their own BE data
- The BE data is secure and backed up by the IT system
- You only have to do this once for each beta-tester, they do nothing.

Whew, that's a relief, it's 23:20 on Saturday night and now I can
finally go get into bed and sleep

Brett



On Fri, 12 Nov 2004 10:49:11 -0800, "John S. Ford, MD"
wrote:

I realize that this isn't the main reason why people split databases into a
front-end and a back-end but here is my problem. I'm using Access 2000.

I'm developing a database app to be used in a hospital for residents to keep
track of their patients and generate reports. Eventually it will be run on
a main server and each work station will contain the front-end and the
server will carry the back-end. For now however, I'm "beta" testing it with
several medical residents and they are carrying their copies on portable USB
flash memory keys (each one essentially a separate Access .mdb file). The
testers are simply running it directly off the keys. I'm just trying to
iron out the usability issues before we put this thing online.

As problems come up, I've had to make some changes to the forms, queries,
modules etc. but the underlying table structure hasn't changed at all. What
I want to do is split the database on each of the resident's own copies.
That way, if I make a change to the "front-end" objects, I can simply
replace the tester's front-ends and leave their individual back-ends (which
contain their underlying data) unchanged. This way their patient lists
won't be disrupted while they're testing the program.

The problem is that different computers in the hospital assign different
drive identifications to their keys (some assign G:/, some assign E:/).
Will Access 2000 have a problem finding the back-end (even though the
front-end and back-end will be sitting in the same folder in the memory key)
if the residents run the program on different computers? Is there a way
around this?

Any help greatly appreciated!

John



Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer