Results 1 to 4 of 4
  1. #1
    Tomlon is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7

    Inconsistent Data or Record Count

    I have a front end Access 2010 Database that my client uses for reporting. I have a script that attaches to a Microsoft 2010 Database to import data from. All the back-end database is is three linked tables via ODBC connection to a SQL database. The SQL database is on a different server than that of the back-end access database. The front end access database the the client uses is on her workstation (Windows 7 64bit). Here is the steps that ocurrs when they wish to update the front end:

    1.) The tables are dropped

    With DoCmd
    .RunSQL "Drop Table CustomerSegs"
    .RunSQL "Drop Table ItemTypes"
    .RunSQL "Drop Table SalesDet"
    End With



    2.) The tables are Imported

    With CurrentDb
    'Import Customer Segments
    .Execute "SELECT * INTO CustomerSegs FROM lnkCustomerSeg IN '\\Server1\Share1\CWReporting.accdb';"


    'Import Item Types
    .Execute "SELECT * INTO ItemTypes FROM lnkItemType IN '\\Server1\Share1\CWReporting.accdb';"

    'Import Sales Detail
    .Execute "SELECT * INTO SalesDet FROM lnkSalesDet IN '\\Server1\Share1\CWReporting.accdb';"

    ' Import Inventory Costs
    .Execute "SELECT * INTO InvCosts FROM dbo_CWInvCosttbl IN '\\Server1\Share1\CW_Reporting\CWReporting.accdb'; "

    .Execute "CREATE INDEX idxPostItemrec ON InvCosts (PostItemRec);"


    End With

    MsgBox " Data current as of midnight " & Format(Now, "MMM-DD-YYYY")


    The problem lies is that if I open up my Sales Detail Table, I have 67,270 rows. The interesting this is that if I Log into Server1 (where my back end database is) and copy the database there and run the update, my Sales Detail table has 69,851 records. If I open the back end database that has the linked tables, from my workstation or other remote server (using '\\Server1\Share1\CW_Reporting\CWReporting.accdb). I create a query to view the link table, I get 67,270 rows). However, if I log into Server1 (server where back end database is at), and open the DB with linked tables, I get 69,851 rows. How am I getting different number of rows from the same database, just accessing it from two separate servers / workstations?

    ODBC SQL Server driver on Server1 (Windows 2008) is 6.01.7601
    ODBC SQL Server on remote server (Windows Server 2003) was 3.86.3959. I installed SQL Native Client 10.0. Same result.

    A little help fro the frustrated will be wonderful.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Tomlon View Post
    ...view the link table, I get 67,270 rows...
    Your link is probably not retrieving all records because of the SQL it is based on. The SQL probably perceives these missing records as orphaned and or not there.

  3. #3
    Tomlon is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7
    That is just it. I can open a copy of the database on a remote server that is network accessible to the SQL DB that it connects to and I get a 67k rows. I can open the same db on the server the SQL DB is on (both servers connect to the SQL db using ODBC SQL connection) and I get 69K rows. Connecting to the same SQL DB.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I have limited exposure to this sort of thing. A wild guess would be an misinterpretation of a char that dictates criteria within the SQL string. So, for instance, Access says = "*" and SQL server does not recognize it as the character that was intended. Really just a wild guess but, I have heard of different driver versions not playing well with regards to ASC standards.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inconsistent results.. please help!
    By vikghai in forum Access
    Replies: 6
    Last Post: 02-09-2014, 05:37 PM
  2. Between And operator gives inconsistent result
    By Reaper in forum Programming
    Replies: 6
    Last Post: 02-09-2013, 09:46 AM
  3. Replies: 3
    Last Post: 09-12-2012, 11:48 AM
  4. Inconsistent??
    By bginhb in forum Programming
    Replies: 3
    Last Post: 09-07-2011, 03:10 PM
  5. Inconsistent Query Error...
    By DHavokD in forum Queries
    Replies: 8
    Last Post: 06-11-2009, 09:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums