Hello Fellow DBers,
Here’s a problem that has me vexed.
Environment:
Access 2003 SP-3.
The database is split Front End: ARB.mdb Back Ends: ARB_be.mdb & ARBReqs_be.mdb.
These files both reside on a P-P Lan.
Record level locking of the edited record is set on both the FE & BE.
On my test lan at home Windows 7 32-Bit on my desktop and Windows 7 64-Bit on my laptop. With this setup everything works just as it is supposed to..no problemo!
However, on the production lan in our Community Manager’s office also P-P Lan…
Access 2003 SP-3.
Same DB Files in same Directory structure. (I have code that automatically relinks the FE/BE when the FE is opened. {see below})
One PC (with FE/BE files running Windows 7 64-bit)
One PC (with FE only running Windows XP Pro 32-Bit)
The problem:
If either PC is in the database with a form open the other PC will not relink the tables (sorry I’m not there and forgot the error).
If either PC is in the database on the switchboard page the other PC can open and relink the tables no problem.
I also get errors when exiting the update forms about a query that is run automatically not being updateable? The query (an Action Query) deletes any Owner Table entry that no longer has a Lot Table entry associated with it.
So is this just a problem because of the different OSes? Or is there something more fundamental here that is going to bite me even if the one PC is upgraded to Windows 7?
Thanks,
RG
FYI: Here’s the Relink Code:
Function ReLinkTable()
Dim zDBPath As String
Dim zDBFullName As String
Dim zBEDBFN As String
Dim zCompName As String
Dim zUName As String
Dim zTableName(12) As String
Dim iTblCnt As Integer
GoTo StartLinking
FileDoesNotExist:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox "Error No: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
End If
StartLinking:
zTableName(0) = "Docks"
zTableName(1) = "Lots"
zTableName(2) = "Owners"
zTableName(3) = "PhoneDir"
zTableName(4) = "StorageLots"
zTableName(5) = "tblAuxNumbers" '*** Last table in ARB_be.mdb ***
zTableName(6) = "Builders" '*** Start of tables in ARBReqs.mdb use index in If iTblCnt = below***
zTableName(7) = "Letters"
zTableName(8) = "ARBMembers"
zTableName(9) = "ARBAssignments"
zTableName(10) = "Requests"
zTableName(11) = "RequestTypes"
zUName = Environ("USERNAME")
Select Case zUName
Case "Bruce"
zCompName = Environ("COMPUTERNAME")
If zCompName = "INSPIRON15-I5" Then '*** BEK Laptop ***
zDBPath = "\DELLQUADCOREbekdocsARB Files" '*** Network Path ***
Else '*** BEK Desktop ***
zDBPath = "G:BEKDocsARB Files" '*** Winows 7 Path ***
End If
Case "Wyboo Manager" '*** Harry ***
zDBPath = "C:UsersWyboo ManagerDocumentsARB Files" '*** Windows 7 Path ***
Case "PropertyManager" '*** Martin ***
zDBPath = "\Wyboomanager-pcARB Files" '*** Network Path ***
Case Else
MsgBox zUName & ": is not an authroized user!", _
vbOKOnly + vbCritical, "Error: User Not Authorized"
End Select
zBEDBFN = "ARB_be.mdb"
zDBFullName = zDBPath & zBEDBFN
For iTblCnt = 0 To UBound(zTableName) - 1
If iTblCnt = 6 Then '*** Switch back end DB files ***
zBEDBFN = "ARBReqs_be.mdb"
zDBFullName = zDBPath & zBEDBFN
End If
On Error GoTo FileDoesNotExist
'*** Delete TableDef from FRONT end DB
DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=zTableName(iTblCnt)
'*** Copy TableDef from BACK end DB to FRONT end DB - Keep in sync!
DoCmd.TransferDatabase TransferType:=acLink, _
DatabaseType:="Microsoft Access", _
DatabaseName:=zDBFullName, _
ObjectType:=acTable, _
Source:=zTableName(iTblCnt), _
Destination:=zTableName(iTblCnt)
On Error GoTo 0
Next iTblCnt
zStatusMsg = "Tables have been Re-Linked"
lTimerInterval = 3000 '*** 3 Seconds ***
DoCmd.OpenForm "frmStatusMsg", acNormal
StdMenuToggle "False"
End Function 'ReLinkTable()
