Msaccess.exe generated error with Replication

[ilikemyfto]

This is driving me bonkers.

I have a replicated database and when I create a replica from the Design Master I get a message saying Msaccess.exe has generated errors... when I open the replica, but only on the first load.

I have traced it to a point where I create a local table with the replica. If I run the code from the replica it gives a fail on the first attempt but subsequent runs are ok. Sure the code recognises if the table has been already created and if so drops out. I have tried including a brakepoint but the fail drops the code out of Access.

The jet engine is at the lastest level for Win 2000.

I'm stumped, anyone a bright idea!

KR



Source Code:-

Function CreateCurrentUserTable()
On Error GoTo CreateOrdersTable_Err
' From Access 97 Developer's Handbook
' by Litwin, Getz, and Gilbert (Sybex)
' Copyright 1997. All rights reserved.
'Note this table is not in the Replicated Set
Dim db As Database
Dim CurrentUser As TableDef
Dim fld1 As Field
Dim fld2 As Field
Dim fld3 As Field
Dim fld4 As Field
Dim fld5 As Field

Dim IntAnswer As Integer
Dim strmsg As String

Set db = CurrentDb()
Set CurrentUser = db.CreateTableDef()
CurrentUser.Name = "CurrentUser"


Set fld1 = CurrentUser.CreateField("CurrentUserID", dbLong)
Set fld2 = CurrentUser.CreateField("CurrentUser", dbText, 30)
Set fld3 = CurrentUser.CreateField("Username", dbText, 30)
Set fld4 = CurrentUser.CreateField("LastModTime", dbDate, 30)
Set fld5 = CurrentUser.CreateField("LastModUser", dbText, 30)

With CurrentUser.Fields
.Append fld1
.Append fld2
.Append fld3
.Append fld4
.Append fld5
End With

With db.TableDefs
.Append CurrentUser
.Refresh
End With
Application.RefreshDatabaseWindow
CreateOrdersTable_Exit:
Exit Function

CreateOrdersTable_Err:
'MsgBox Error$
If (Err = 3010) Then
Resume CreateOrdersTable_Exit
Else
strmsg = "An Unecpected Error has Occured " & Err
IntAnswer = MsgBox(strmsg, vbCritical + vbOKOnly)
Resume CreateOrdersTable_Exit
End If

End Function

 

[boblarson]

I'm not sure, but I believe that since it the replicated datbase cannot do design mode items, it wouldn't be able to "CREATE" a table. If you can, define the table structure and use a query to populate and to delete from it.

 

[ilikemyfto]

I get this error the first time the replica is used. If I load up the replica a second and subsequent time it's fine and the tables are created as local.
I am sure it worked ok when I first added the code but that was several iterations ago.

I have even tried unreplicating the Design Master to a new DB but it still does it. (Doh!)

I think maybe my best shot is to try inserting the code into a test DB with the just the tables and code required to replicate and produce the local tables in the replica. Then gradually add queries/forms/modules etc until it show errors.

Oh why do I need to create these tables? I use them to manage the user name and password etc. I found when they were replicated it created a conflict every time the DB was synchronised, so the code is to make them local and keep them out of the sync routine and avoid data conflicts.

 

[ilikemyfto]

Fix (I Hope!)

When all else fails – “Read the manual”

From the Access 97 Developer’s Handbook by Paul Litwin, Ken Getz & Mike Gilbert

Found a section on replication referencing setting the local / replicable property for objects. Sorry but the text rather left me cold but wondered if by creating the table and not establishing the property MS Access was throwing a tantrum. So I added a line in the table creation code to set the ReplicableBool property to false. And “Yippee” it worked. Take the line out and it fails.

Magic!

 

[ilikemyfto]

Update to previous statement:-

It's now intermittant, sometimes it fails others it's ok.