| re: To link or not to link
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
| 
