Mysterious table data changing

[RichO]

I have a table that contains all of our company pricing for services. You can see a screen shot of the table here:

http://www.x-raysband.com/table1.jpg

All of this data is locked and cannot be changed by the user. However, the seasons keep getting mixed up. What I mean by this is that suddenly there will be a record missing from one of the seasons and there is a duplicate record in one of the other seasons. See the spot-shadowed fields here:

http://www.x-raysband.com/table2.jpg

Off season and Spring are now missing entries for CDJ, INC but the records that belong in those places are now showing as duplicate entries in Prime and Regular. Simply put, the season name somehow got changed within the record, but there is nothing in the system that allows a user to do that.

This has been happening ever since I added the pricing feature to the DB and I am completely stumped. Our work DB has the tables stored in a SQL server back end.

If anyone can shed some light on why this might be happening, any help would be appreciated.

 

[Sammy B]

How many of these fields are linked? And do you know if the actual tables are changed or is it possible you just see it that way through the form.

If possible, upload a stripped down version of your db so we can have a look.


Sam

 

[RichO]

The data in the table is definitely being changed. I've had to go directly into the table a number of times to change it back.

The DB is 11 MB and that's without the SQL server tables. It's far to big and involved to be able to scale it down. And of course we all know that when any attempt is made to duplicate the problem it won't happen. It only happens about once or twice a month and this data is constantly being queried throughout the day when customers call in for pricing. The data is being accessed in a few different ways, sometimes by a query, sometimes by opening the recordset, and sometimes with DLookup.

I'm not sure what you mean about the fields being linked.

Thanks for the reply.

 

[Sammy B]

What I mean by linked fields is that usually you would (as an example) have a seperate table for your seasons linked to the main form with a seasonID. Normally you would not store the text of each season in the main table.

 

[RichO]

Everything is stored in the one table just as you see in the screen shot. Being that there were only 4 seasons and the table is rather small I didn't bother using a separate table for the text of the seasons.

 

[Sammy B]

I can not say what may be the problem but I can suggest a way of preventing changes assuming they are accidental. Remove the Autonumber and key if you have one in the table. Then make the 'Service Year', 'Service Name' and 'Service' all KEY fields. This is a unique key for your records and access will not allow any duplicate data between the 3 keys. This way you could not end up with 2 exact records. You must allow each of the keys fields to allow duplicates but access will not allow all 3 to be the same.

Sam

 

[jfgambit]

RichO

I had the same problem with a back-end SQL server linked table. After updating what I thought was missing or changed data, I came to find that the data was still there and was not being altered (I actually went into the SQL table through Enterprise Manager). The next time this happens check the SQL table, is the data correct in it? If so, it's the same table refresh problem I was having.

Here is some code for relinking SQL tables to a database. You can add this to your Start-Up form and it will relink the tables each time you open the database.

Public Function AttachTables(sUser As String, sPass As String) As Boolean
Dim recA As Recordset, setupset As Recordset, strConnect As String, db As Database

SysCmd acSysCmdSetStatus, "Connecting to Database...Stand-by"
DoEvents
On Error GoTo err_handler1
Set Wkspc = CreateWorkspace("ODBCWorkspace", CurrentUser, "", dbUseODBC)
DoCmd.SetWarnings False
Call DetachTables
Set db = CurrentDb

' Establish connections to databases
Set setupset = db.OpenRecordset("tblSetup", dbOpenForwardOnly)
strConnect = "ODBC;UID=" & sUser & ";PWD=" & sPass & ";DSN=" & setupset!BlueLightDSN

Set CN = Wkspc.OpenConnection("CONNECTION_1", dbDriverNoPrompt, True, strConnect)
CN.QueryTimeout = 0


' Link tables from databases
Set recA = db.OpenRecordset("tbllinkedtables", dbOpenDynaset)
Do While Not recA.EOF
If recA!Connect = True Then
Me.Mess = recA!DestinationTable
DoEvents
DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, recA!SourceTable, recA!DestinationTable
End If
recA.MoveNext
Loop
recA.Close
setupset.Close
Set recA = Nothing
Set setupset = Nothing
On Error GoTo 0

SysCmd acSysCmdSetStatus, "Ready"

AttachTables = True
Exit Function

err_handler1:
SysCmd acSysCmdSetStatus, "Disconnected"
AttachTables = False
End Function