access 2012 frontend not showing correct data from linked ODBC sql 2012 tables (1 Viewer)

starrcruise

Registered User.
Local time
Yesterday, 23:59
Joined
Mar 4, 2011
Messages
18
Good morning.
Was not sure where to post this. I have an issue that just began occurring. views, tables and queries have been working fine and now are not. Here are FAQs:
1. created sql server 2012 table and linked to access 2007-2010 db - call it dbo.224Ongoing
2. created sql server 2012 view and linked to access 2007-2010db - call it dbo.v224Ongoing.
3. have appendquery in access that pulls from dbo.v224Ongoing - data filtered on [created], a datetime field.
4. append is simple , no grouping, no aggregating. Data is underwriting conditions placed against loans within the correct [create] range. So a loan can have 1 to multiple conditions. Append should simply pull all conditions within filter. [create] filtered for one month.
5. Problem, in opening dbo.v224ongoing, dbo.224ongoing, and output of append, in access, data is showing duplicated. Rather than pulling each unique condition per loan, the above ALL show the latest condition information. So if a loan has five conditions, the above will list 5 rows, but all with the latest data showing rather than unique data.
6. the same query pulled in sql server against dbo.224going or dbo.v224ongoing shows the correct unique data.

Both dbo.224ongoing and dbo.v224ongoing have datetime datatype, and the dbo properties within access show them as being datetime datatype.

I have attached a word file that shows the output from access and sql server and the property tables. This problem just started and both myself and db administrator have tried to figure out the problem with no luck. Hope someone can help. Please let me know what other information is needed.

Thank you.
 

Attachments

  • Access-ODBC problem.doc
    62 KB · Views: 166

starrcruise

Registered User.
Local time
Yesterday, 23:59
Joined
Mar 4, 2011
Messages
18
The dbo.224Ongoing does have a unique identifier. I just added it and haven't altered the field yet to PK but it is autonumber. Now using the ID in queries allows me to actually pull the correct data in a query in access after the dbo.224ongoing table has been updated , but does not help in just looking at the view, append output, or opening linked table in access. The view and table are in a frontend that is used by our appraisal dept. Their process is to look at a report based on the view first, check the output, then on a monthly basis run the append. After the append is completed, they go into the dbo.224ongoing table and manually edit data, as the UW sometimes enter in wrong data. Then the dbo.224ongoing table is used in queries for various reporting. Most of the tables in SQL server are indexed fields, but few are PK as this is just big data pulled from everywhere and not relational. Hope not TMI.
 

Users who are viewing this thread

Top Bottom