Phantom record using .adp?

[MikeR]

I first posted this in Access Database General Questions but then found this
more apporpriate newsgroup, so pls forgive cross-post:

We use MS Dynamics (Great Plains v 8.0) accounting system on SQL Server 2003
and I 've connected an .adp project (MS Access 2003) to the underlying
tables. When I run a simple query using .adp against a single table
(GL20000), the recordset contains an extra line that appears to be a
duplicate of a valid record.

If I repeat the query using SQL Query Analyzer, I get the correct results.
This is bizarre to me and I've not seen any mention of others having similar
problems.

SQL is as follows:

SELECT dbo.GL20000.*
FROM dbo.GL20000
WHERE (JRNENTRY = 46099)

Has anyone experienced this? This is amn adp show-stopper unless it can be
resolved. Thanks in advance.

 

[Sylvain Lafontaine]

Without any exemple of data and a description of the schema of the table,
it's hard to tell you which result is right. Also, there is no SQL-Server
2003; only either 2000 or 2005.

Also, by changing the data, make sure that you are connected to the same
database in both cases.

 

[MikeR]

hmmm. I don't know if we can post as HTML table but here's the table schema.
Just forgive alignment issues. Only one key field "DEX_ROW_ID" which is the
last one.

Column Name Data Type Length
OPENYEAR smallint 2
JRNENTRY int 4
RCTRXSEQ numeric 9
SOURCDOC char 11
REFRENCE char 31
DSCRIPTN char 31
TRXDATE datetime 8
TRXSORCE char 13
ACTINDX int 4
POLLDTRX tinyint 1
LASTUSER char 15
LSTDTEDT datetime 8
USWHPSTD char 15
ORGNTSRC char 15
ORGNATYP smallint 2
QKOFSET smallint 2
SERIES smallint 2
ORTRXTYP smallint 2
ORCTRNUM char 21
ORMSTRID char 31
Column Name Data Type Length
OPENYEAR smallint 2
JRNENTRY int 4
RCTRXSEQ numeric 9
SOURCDOC char 11
REFRENCE char 31
DSCRIPTN char 31
TRXDATE datetime 8
TRXSORCE char 13
ACTINDX int 4
POLLDTRX tinyint 1
LASTUSER char 15
LSTDTEDT datetime 8
USWHPSTD char 15
ORGNTSRC char 15
ORGNATYP smallint 2
QKOFSET smallint 2
SERIES smallint 2
ORTRXTYP smallint 2
ORCTRNUM char 21
ORMSTRID char 31
ORMSTRNM char 65
ORDOCNUM char 21
ORPSTDDT datetime 8
ORTRXSRC char 13
OrigDTASeries smallint 2
OrigSeqNum int 4
SEQNUMBR int 4
DTA_GL_Status smallint 2
DTA_Index numeric 9
CURNCYID char 15
CURRNIDX smallint 2
RATETPID char 15
EXGTBLID char 15
XCHGRATE numeric 9
EXCHDATE datetime 8
TIME1 datetime 8
RTCLCMTD smallint 2
NOTEINDX numeric 9
ICTRX tinyint 1
ORCOMID char 5
ORIGINJE int 4
PERIODID smallint 2
CRDTAMNT numeric 9
DEBITAMT numeric 9
ORCRDAMT numeric 9
ORDBTAMT numeric 9
DOCDATE datetime 8
PSTGNMBR int 4
PPSGNMBR int 4
DENXRATE numeric 9
MCTRXSTT smallint 2
CorrespondingUnitchar 5
VOIDED tinyint 1
Back_Out_JE int 4
Back_Out_JE_Yearsmallint 2
Correcting_JE int 4
Correcting_JE_Year smallint 2
Original_JE int 4
Original_JE_Seq_Num numeric 9
Original_JE_Year smallint 2
DEX_ROW_ID int 4

If i run the previous SQL statement via an adp stored procedure, I get 3
records, as follows. The 3rd record, with DEX_ROW_ID of 236416, does not
exist in database, so how could adp be pulling this? thanks.

Column Name Record 1 Record 2 Record 3 Phantom!
OPENYEAR 2007 2007 2007
JRNENTRY 46099 46099 46099
RCTRXSEQ 0 0 0
SOURCDOC PMTRX PMTRX PMTRX
REFRENCE
DSCRIPTN Purchases Accounts Payable Accounts Payable
TRXDATE 39106 39106 39106
TRXSORCE GLTRX00005317 GLTRX00005317 GLTRX00005317
ACTINDX 69612 1385 1385
POLLDTRX 0 0 0
LASTUSER
LSTDTEDT 2 2 2
USWHPSTD ee01 ee01 ee01
ORGNTSRC PMTRX00001845 PMTRX00001845 PMTRX00001845
ORGNATYP 1 1 1
QKOFSET 1 1 1
SERIES 4 4 4
ORTRXTYP 1 1 1
ORCTRNUM 000029542 000029542 000029542
ORMSTRID GRAPHIC GRAPHIC GRAPHIC
ORMSTRNM
ORDOCNUM 72719 72719 72719
ORPSTDDT 39106 39106 39106
ORTRXSRC PMTRX00001845 PMTRX00001845 PMTRX00001845
OrigDTASeries 0 0 0
OrigSeqNum 16384 32768 32768
SEQNUMBR 16384 32768 32768
DTA_GL_Status 40 40 40
DTA_Index 0 0 0
CURNCYID USD USD USD
CURRNIDX 1008 1008 1008
RATETPID
EXGTBLID
XCHGRATE 0 0 0
EXCHDATE 2 2 2
TIME1 2 2 2
RTCLCMTD 0 0 0
NOTEINDX 369103 369103 369103
ICTRX 0 0 0
ORCOMID
ORIGINJE 0 0 0
PERIODID 0 0 0
CRDTAMNT 0 3090.54 3090.54
DEBITAMT 3090.54 0 0
ORCRDAMT 0 3090.54 3090.54
ORDBTAMT 3090.54 0 0
DOCDATE 2 2 2
PSTGNMBR 0 0 0
PPSGNMBR 0 0 0
DENXRATE 0 0 0
MCTRXSTT 0 0 0
CorrespondingUnit
VOIDED 0 0 0
Back_Out_JE 0 0 0
Back_Out_JE_Year 0 0 0
Correcting_JE 0 0 0
Correcting_JE_Year 0 0 0
Original_JE 0 0 0
Original_JE_Seq_Num 0 0 0
Original_JE_Year 0 0 0
DEX_ROW_ID 178322 178323 236416
This DEX_ROW_ID does not exist in Database!

 

[Sylvain Lafontaine]

And how are you retrieving these records from ADP and displaying them in
three columns? Is this result straightforward from the database window or
if it comes after a lot of VBA code has executed? Any use of RecordsetClone
instead of Recordset.Clone in your ADP code? Did you take a look with the
SQL-Server Profiler to see if there is nothing different between the request
sent by ADP and the one sent by QA?

And finally, how can you be sure that the record 236416 doesn't exist in the
database?

 

[MikeR]

And how are you retrieving these records from ADP and displaying them in
three columns?

I just ran to Datasheet view in Access, and paste special/transpose in Excel
so that results would fit in web window.

Is this result straightforward from the database window or

if it comes after a lot of VBA code has executed?

No VBA cose is being used.

Any use of RecordsetClone instead of Recordset.Clone in your ADP code?

No coding, just simple SQL select statement.

Did you take a look with the

SQL-Server Profiler to see if there is nothing different between the request
sent by ADP and the one sent by QA?

I've not, something for me to explore. thks

And finally, how can you be sure that the record 236416 doesn't exist in the
database?

I ran following SQL statement through Query Analyzer. No records were
returned.
SELECT dbo.GL20000.*
FROM dbo.GL20000
WHERE (DEX_ROW_ID = 236416)

 

[Sylvain Lafontaine]

Maybe a transaction problem. What does it gives if you add the WITH
(NOLOCK) hint? Also, is GL20000 a table or a view?

 

[MikeR]

GL20000 is a table.
Running the SQL WITH (NOLOCK) produced results as before.

I've a request into our Tech folks re: SQL-Server Profiler to do what you
suggested earlier. May not happen until tomorrow, so i'll update case with
those results. Thanks for help so far.

 

[Sylvain Lafontaine]

It's possible that you have depassed the limit of the Datasheet view. Try
with displaying only a few fields by creating a query and also try running
this query from VBA code to see if this problem is coming from the dataview
or from ADO.

Also, refresh the Tables views using the F5 or the Refresh Command from the
View menu to make sure that the metadata are OK.

 

[Vadim Rapp]

I would explore two possibilities:

1. Access is taking the data from one table while you are looking at
another. For example, two tables with different owners. It does not look
like this might be the case from your examples, but just verify to be sure.

2. 236416 that you see in the result is not the same 236416 that you put in
WHERE statement. For example, if DEX_ROW_ID is a character column with an
extra space or even some binary character before or after. Try this:

select * from dbo.GL20000 where DEX_ROW_ID = (select DEX_ROW_ID from
dbo.GL20000 where JRNENTRY = 46099 and DEX_ROW_ID NOT IN (178322, 178323 ) )

or, in Access explore the true hex contents of the returned alleged 236416.

Vadim Rapp

 

[MikeR]

Sylvain and Vadim:
Thank you both for your replies and ideas. After further inquiry, I had
relied on another's assurance that that the Query Analyzer results were being
run against the same DB as the Acess Project. If fact, the Query Analyzer
was being run against Production and Access was pointed towards Test. Thanks
again.