To link or not to link

[debbie]

I have an Access 2002 program that I install using Wise. The first
thing my program does when a front end is opened is re-link, then it
checks the version and if needed upgrades the backend.
My problem is that I have a new front end version upgrade that creates
several new tables in the backend and then links them. So when the
program opens up it checks the version in the backend and if needed
creates new tables & new links. Now when this new front end is
installed on another workstation the program knows that the upgrade has
been done so skips creating new tables & new links...so this front end
can't open forms based on the new tables, because the links aren't
there.
So I tried having the links in my front end before I created the
install and then the program has problems with the relinking code that
it does everytime the database is opened because it's trying to link to
tables that don't exist yet.

What is the standard procedure for the order of these things...Or do I
need two installs....one for the first time (to upgrade the backend)
and one for all the other workstations?

I'm very interested in doing things properly and not just hacking about
it.
Thanks in advance for any advice from the many helpful experts found
around here.
Debbie

[Arno R]

Hi Debbie,

Comments inline

"debbie" <de****@seaportnet.com> schreef in bericht news:11**********************@o13g2000cwo.googlegr oups.com...

I have an Access 2002 program that I install using Wise. The first
thing my program does when a front end is opened is re-link, then it
checks the version and if needed upgrades the backend.
I *never* relink anymore... I found relinking to be unreliable more than once.
Problems did occur *sometimes* when the backend-table(s) had changed (new fields, other indexes whatever)
Sorry: can't remember the problems *exactly* but when it occurred it was *real bad*. (wrong data presented)
From that moment (using Access 2.0 and A97) I *never* used relinking-code again.
I use A2k most of the time now and it is possible that in A2k and above the relink-problem is over but ...
I keep to my 'old' habit because it is simple and bullet-proof.
So when updating or when the backend-path changes, my programs always create all the links again (deleting all linked tables first).
I never 'forget' a table because the frontend 'knows' exacly which tables are needed ...
My problem is that I have a new front end version upgrade that creates
several new tables in the backend and then links them. So when the
program opens up it checks the version in the backend and if needed
creates new tables & new links. Now when this new front end is
installed on another workstation the program knows that the upgrade has
been done so skips creating new tables & new links...so this front end
can't open forms based on the new tables, because the links aren't
there.
You could check your needed links. (at least check for newly added tables)
So I tried having the links in my front end before I created the
install and then the program has problems with the relinking code that
it does everytime the database is opened because it's trying to link to
tables that don't exist yet.
You could (IMO you have to!) trap this error and in that case create your backend-tables first.
After that relink again.
What is the standard procedure for the order of these things...Or do I
need two installs....one for the first time (to upgrade the backend)
and one for all the other workstations?

Two installs would be a hassle. Don't do this! Not needed!
-- Check for the backend-version. Upgrade the backend if needed.
-- Relink (or better yet recreate the links)
I don't think what I suggested about recreating the links is 'standard' procedure but it is *my* standard procedure.

Arno R

[Pachydermitis]

Hi Debbie,
I have some databases that are very similar.
I have another school of thought than Arno - I relink tables all the
time in many Dbs with no issues. I think the relink issue was one from
versions past.
What I do is on my link table routine, if there is an error linking a
table it skips to the next table. You will see a similar example in
the subdatasheet code below.

I tried my updates with the tables not it the front end, but when you
create them in the back end, you can run into backend refresh issues
when you relink the tables you just built. It's easier leaving them in
the new Front end.

A couple things that will help performance in your database
tremendously (forgive me if you already know this). Turn off the
datasubsheets. Have a recordset that is always open. For example, I
hae a main form that has no need for a recordset, but it is linked to a
table with no data in it. That way the connection is never lost.
Below is my code for turning off the datasubsheets. You will have to
comment out my status bar and errhandler stuff. It requires DAO.

I hope this helps you

------------------------------------
to turn off the fe
TurnOffSubDataSheets
-------------------------------------
to turn off the be
Dim appBackEnd As Access.Application
Dim dbBackend As DAO.Database
Set appBackEnd = New Access.Application
With appBackEnd
.Visible = False
.OpenCurrentDatabase strBackendPath
TurnOffSubDataSheets appBackEnd
.CloseCurrentDatabase
End With
Set appBackEnd = Nothing

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

Function TurnOffSubDataSheets(Optional appAccess As Access.Application)
'************************************************* ***********************************
' Author Daniel Tweddell
' Revision Date 01/28/04
'
' In a multiuser database, the Sub Datasheets slow the database down
considerably.
' here we turn them off!
'************************************************* ***********************************
If Not bShowDebug Then On Error GoTo Err_Function
Dim dbCurrent As DAO.Database
Dim prpAdd As DAO.Property
Dim tdfChange As TableDef
Dim iObjectCount As Integer
Dim dblStatus As Double
Dim objQuery As AccessObject
Dim blnTableLoop As Boolean 'to use in the error handling so we
don't end up in an endless loop
Dim blnQueryLoop As Boolean 'to use in the error handling so we
don't end up in an endless loop
Const strPropName As String = "SubDataSheetName"
Const strPropVal As String = "[NONE]"

DoCmd.Hourglass True
DoCmd.OpenForm "fUpdateStatus" 'show our progress on a status form
Set frmStatus = Forms!fUpdateStatus
frmStatus!lblTitle.Caption = "Turning off Sub Datasheets"
frmStatus.RecordSource = "tRemoteLink" 'link to speed it up
ProgressBarUpdate 'reset the status
If appAccess Is Nothing Then Set appAccess = Application
With appAccess
Set dbCurrent = .CurrentDb
iObjectCount = dbCurrent.TableDefs.Count +
dbCurrent.QueryDefs.Count
For Each tdfChange In dbCurrent.TableDefs
dblStatus = dblStatus + (100 / iObjectCount) 'get the
current status per table
ProgressBarUpdate dblStatus, "Setting up " & tdfChange.Name
& ". . ." 'show the status
blnTableLoop = True
If Left(tdfChange.Name, 4) <> "Msys" Then
ChangeObjectProperty tdfChange, strPropName, 10,
strPropVal
End If
NextTable:
blnTableLoop = False
Next
For Each objQuery In .CurrentData.AllQueries
If Left(objQuery.Name, 14) <> "qReconciliatio" Then
dblStatus = dblStatus + (100 / iObjectCount) 'get the
current status per table
ProgressBarUpdate dblStatus, "Setting up " &
objQuery.Name & ". . ." 'show the status
blnQueryLoop = True
ChangeObjectProperty
..CurrentDb.QueryDefs(objQuery.Name), strPropName, 10, strPropVal
NextQuery:
blnQueryLoop = False
End If
Next
Set dbCurrent = Nothing
End With
ProgressBarUpdate 100, "Setup complete!" 'show the status
'MsgBox "Successfully turned off datasubsheets!", vbInformation,
"Linked"
DoCmd.Hourglass False
If CurrentProject.AllForms(frmStatus.Name).IsLoaded Then
DoCmd.Close acForm, frmStatus.Name, acSaveNo 'close up the form
Exit Function
Err_Function:
errHandler Err.Number, Err.Description, "TurnOffSubDataSheets()",
bSilent
If blnTableLoop Then Resume NextTable
If blnQueryLoop Then Resume NextQuery
End Function

Private Sub ChangeObjectProperty(vObject, ByVal strPropertyName As
String, _
ByVal iPropertyType As Integer, ByVal
vPropertyValue)
'************************************************* ***********************************
' Author Daniel Tweddell
' Revision Date 01/28/04
'
' Changes the properties on an objects
'************************************************* ***********************************
If Not bShowDebug Then On Error GoTo Err_Function
Dim prpAdd As DAO.Property
Dim blnFound As Boolean 'Was the property found
For Each prpAdd In vObject.Properties 'look for the property
If prpAdd.Name = strPropertyName Then 'if it's there change it,
if not we'll add it
blnFound = True
If prpAdd.Value <> vPropertyValue Then prpAdd.Value =
vPropertyValue
Exit For
End If
Next
If Not blnFound Then 'the property was not found, add it
Set prpAdd = vObject.CreateProperty(strPropertyName)
With prpAdd
.Type = iPropertyType
.Value = vPropertyValue
End With
vObject.Properties.Append prpAdd
End If
Exit Sub
Err_Function:
errHandler Err.Number, Err.Description, "ChangeObjectProperty()",
bSilent
End Sub

[debbie]

Thanks for all the help and sorry for the delay in answering. I can
definately use the code for turning off subdatasheets for another
database and I need to bite the bullet and create the form that's
always linked for this one. What I finally did was leave the links in
the front end and my linking code comes up with the list it didn't
find...the user doesn't try to find another database and then I have
relinking code again at the end of the update. Not as seamless as I'd
like but it works with no errors and I had a deadline to get it out.
Thanks again for the quick responses.