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.