Data Mysteriously Changes

  • Thread starter Thread starter rickw
  • Start date Start date
R

rickw

I have a db set up to provide our customers with information pertaining to
vehicle maintenance. I import data from another program into a table (VEH).
The only customer information this table has is coded. I have a query that
gathers data from this table and another table (ORG) giving cleartext
customer names to facilitate easier customer interaction. the ORG table has
the following fields:

ID NAME
j1 Johnson, Mark

In my import macro, I run a delete query to empty the VEH table, then import
from an ASCII file to repopulate the table. The macro only modifies the data
in the VEH table. However, whenever I open the nested form to view the data,
sometimes data is shown that doesn't belong. I check the ORG table, and
names have been moved around or duplicated under the wrong codes. This
doesn't appear to be a problem with the macro or any of the queries. Is
there something going on with Access 2007 I should be aware of? I used the
same file under 2003 and never had an issue.

Sorry for being so verbose, but wanted to get as much info as possible in to
help you help me. Thanks.
 
Do you have Cascading Updates and/or Cascading Deletes turned on for the
table relationships?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
No, I have no relationships set. I have a join in the query that returns the
NAME field from the ORG table where the ID fields are equal. It only returns
data where both sides are equal.
 
If you post the SQL statement, folks here might be able to offer their
observations.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here's the SQL of the query for the form I use to view data:

SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO],
DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC
DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME],
CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1
January 2008") AS 1stofYr
FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to
Org].OrgCode
WHERE (([OrgCode to Org].Org)<>"EXCESS")
ORDER BY CBF.[ASSGN ORG];

The macro I run first runs a delete query on the VEH table, then a standard
TransferText action to import new data.
 
MORE INFO...

I have opened my db a few times today, without running my import macro.
Each time, data had been rearranged, i.e., ID# BE contained the cleartext
for another ID#. I made the corrections and closed the db. Opened it up
later, and ID# BE referenced a different cleartext customer. This happens
with different ID #s each time the db is opened.

Now, each time the db is open, the form automatically displays, which means
the below query is run. Is there any way this query could be altering
individual fields in unrelated records? I'm no Access expert, but I've never
had this happen in any other db I've developed.


rickw said:
Here's the SQL of the query for the form I use to view data:

SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO],
DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC
DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME],
CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1
January 2008") AS 1stofYr
FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to
Org].OrgCode
WHERE (([OrgCode to Org].Org)<>"EXCESS")
ORDER BY CBF.[ASSGN ORG];

The macro I run first runs a delete query on the VEH table, then a standard
TransferText action to import new data.




Jeff Boyce said:
If you post the SQL statement, folks here might be able to offer their
observations.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
The query is a SELECT and therefore can not be changing the data. If
somethings actually changing the data in the table, it's not a Select query.

Is either the CBF.[ASSGN ORG] or [OrgCode to Org].OrgCode fields the primary
key field for their table? At least one should be. If you can't make at least
one of them the PK, then there's a chance that you have a Many to Many
relationship which can cause data anomolies.

If data is missing, try changing the INNER JOIN to a Left or Right join. If
there's not a matching record in both tables, the record won't return with an
Inner Join.

Have you made a backup copy of the database then do a Compact and Repair?
That could see if there's a corruption problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


rickw said:
MORE INFO...

I have opened my db a few times today, without running my import macro.
Each time, data had been rearranged, i.e., ID# BE contained the cleartext
for another ID#. I made the corrections and closed the db. Opened it up
later, and ID# BE referenced a different cleartext customer. This happens
with different ID #s each time the db is opened.

Now, each time the db is open, the form automatically displays, which means
the below query is run. Is there any way this query could be altering
individual fields in unrelated records? I'm no Access expert, but I've never
had this happen in any other db I've developed.


rickw said:
Here's the SQL of the query for the form I use to view data:

SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO],
DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC
DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME],
CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1
January 2008") AS 1stofYr
FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to
Org].OrgCode
WHERE (([OrgCode to Org].Org)<>"EXCESS")
ORDER BY CBF.[ASSGN ORG];

The macro I run first runs a delete query on the VEH table, then a standard
TransferText action to import new data.




Jeff Boyce said:
If you post the SQL statement, folks here might be able to offer their
observations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

No, I have no relationships set. I have a join in the query that returns
the
NAME field from the ORG table where the ID fields are equal. It only
returns
data where both sides are equal.


:

Do you have Cascading Updates and/or Cascading Deletes turned on for the
table relationships?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a db set up to provide our customers with information pertaining
to
vehicle maintenance. I import data from another program into a table
(VEH).
The only customer information this table has is coded. I have a query
that
gathers data from this table and another table (ORG) giving cleartext
customer names to facilitate easier customer interaction. the ORG
table
has
the following fields:

ID NAME
j1 Johnson, Mark

In my import macro, I run a delete query to empty the VEH table, then
import
from an ASCII file to repopulate the table. The macro only modifies
the
data
in the VEH table. However, whenever I open the nested form to view the
data,
sometimes data is shown that doesn't belong. I check the ORG table,
and
names have been moved around or duplicated under the wrong codes. This
doesn't appear to be a problem with the macro or any of the queries.
Is
there something going on with Access 2007 I should be aware of? I used
the
same file under 2003 and never had an issue.

Sorry for being so verbose, but wanted to get as much info as possible
in
to
help you help me. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
The [OrgCode to Org].OrgCode field is a primary key. When I return the
records, I want all records from CBF and only those which match from [OrgCode
to Org]. That should be why Access designated an Inner Join.

I didn't think the Select query would alter my data, but something strange
is definitely happening. If it's not Access or my db, then I will need to
get with our network folks and see if one (or more) of their screwy security
measures is causing it. Of course, that's why I came here first...I doubt
our network/computer support folks have enough knowledge to help figure this
out.

Rick


Jerry Whittle said:
The query is a SELECT and therefore can not be changing the data. If
somethings actually changing the data in the table, it's not a Select query.

Is either the CBF.[ASSGN ORG] or [OrgCode to Org].OrgCode fields the primary
key field for their table? At least one should be. If you can't make at least
one of them the PK, then there's a chance that you have a Many to Many
relationship which can cause data anomolies.

If data is missing, try changing the INNER JOIN to a Left or Right join. If
there's not a matching record in both tables, the record won't return with an
Inner Join.

Have you made a backup copy of the database then do a Compact and Repair?
That could see if there's a corruption problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


rickw said:
MORE INFO...

I have opened my db a few times today, without running my import macro.
Each time, data had been rearranged, i.e., ID# BE contained the cleartext
for another ID#. I made the corrections and closed the db. Opened it up
later, and ID# BE referenced a different cleartext customer. This happens
with different ID #s each time the db is opened.

Now, each time the db is open, the form automatically displays, which means
the below query is run. Is there any way this query could be altering
individual fields in unrelated records? I'm no Access expert, but I've never
had this happen in any other db I've developed.


rickw said:
Here's the SQL of the query for the form I use to view data:

SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO],
DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC
DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME],
CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1
January 2008") AS 1stofYr
FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to
Org].OrgCode
WHERE (([OrgCode to Org].Org)<>"EXCESS")
ORDER BY CBF.[ASSGN ORG];

The macro I run first runs a delete query on the VEH table, then a standard
TransferText action to import new data.




:

If you post the SQL statement, folks here might be able to offer their
observations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

No, I have no relationships set. I have a join in the query that returns
the
NAME field from the ORG table where the ID fields are equal. It only
returns
data where both sides are equal.


:

Do you have Cascading Updates and/or Cascading Deletes turned on for the
table relationships?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a db set up to provide our customers with information pertaining
to
vehicle maintenance. I import data from another program into a table
(VEH).
The only customer information this table has is coded. I have a query
that
gathers data from this table and another table (ORG) giving cleartext
customer names to facilitate easier customer interaction. the ORG
table
has
the following fields:

ID NAME
j1 Johnson, Mark

In my import macro, I run a delete query to empty the VEH table, then
import
from an ASCII file to repopulate the table. The macro only modifies
the
data
in the VEH table. However, whenever I open the nested form to view the
data,
sometimes data is shown that doesn't belong. I check the ORG table,
and
names have been moved around or duplicated under the wrong codes. This
doesn't appear to be a problem with the macro or any of the queries.
Is
there something going on with Access 2007 I should be aware of? I used
the
same file under 2003 and never had an issue.

Sorry for being so verbose, but wanted to get as much info as possible
in
to
help you help me. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top