Solved

Different results sets when pulling SQL table to Access

Posted on 2013-12-27
29
456 Views
Last Modified: 2013-12-31
I have an issue when importing table from SQL 2008 R2 into Access 2010.  I perform the import using the "Get External Data" GUI within Access... build an ODBC connection.

On my machine, I am seeing the latest transactions/rows based on "Transacation Date".  On the user's machine they are not seeing the latest transactions, only up through 8/30/2013.  I am seeing through 11/30/2013.

Now, before we go any further, I checked myself to verify that the user had cleared all filters in Access, still no change.  I proceeded to delete that local access DB and recreate, same thing, I only see transactions/rows through 8/30/2013.  

I proceeded to go back to my machine and go through the GUI again and once more I can see the latest transactions..

I checked the size of the Access DB on the user box and it is far from the threshold...   going to do some more research, but I am at a loss...

I also verified the client pc, software versions and compared to mine... I do not see any differences.   I also verified that the user is using the correct ODBC SQL connection.  

Clue:  This was working correctly up until a few months ago.  That is why I commented above about a possible threshold... but at this time don't understand why this would apply to them in their environment and not mine.
0
Comment
Question by:66chawger
  • 13
  • 6
  • 3
  • +4
29 Comments
 
LVL 84
ID: 39741841
After the import into your database, how do the users view the data? Have you built a form, or are they viewing a query, table, report, etc?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39741851
Instead of importing from SQL Server, why not simply link to SQL Server?

If you do that, do you see all of the data?
0
 

Author Comment

by:66chawger
ID: 39741853
For this purpose, they right click on the DB in access and select open.   Then they proceed to sort or manipulate columns.   Again, I created a new DB connection on their machine, actually two different user machines and received the issue defined previously... when I do it on mine, no problems.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39741871
Is there a chance that there are two versions of the database (development and production) and they are linked to the wrong version?

How are you defining the DSN connection (File Data Source, Machine Data Source)?

Have you tried DSN-less connection to the SQL Server?
0
 

Author Comment

by:66chawger
ID: 39741898
Fred, I just read your comment from a previous post... this is a link not an import.. sorry for the mis-communication.

No, I personally verified they are linked to production DB as well as creating a new link myself.

DSN is Machine Data Source.

As far as DSN-less connection to SQL via access, no have not tried.  Would this be established through Access or external?  I am not an Access aficionado :)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39741912
If you are certain the Machine Data Source connections are defined the same, a DSN-less connection would not be necessary nor would it work any better or likely resolve the problem you are encountering.

I just prefer DSN-less connections as they allow me to adjust the connection programmatically, so that I can easily change between different data sources.

When the users view the data, are they viewing the Table directly? or via a query?

If you write a query against the table, and sort the data by descending data, what are they seeing?

If you create an ADO connection to the SQL Server and open a recordset using that connection, can they see the entire recordset for that table?
0
 

Author Comment

by:66chawger
ID: 39741941
They are viewing the table directly through the link.  They simply right click on the access object, and select open and proceed to manipulate the data by sorting and what have you.  

I can write a query through SSMS and sort descending.... if you are speaking about querying through Access I would need more info.  They don't have the capability to query the table through SSMS.

As far as ADO, are talking about through VS or other method programmatically?
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39741974
Is the table they are viewing exceptionally large?  If the tables are large, Access will pull some of the data across the network for immediate viewing and will continue to pull the data across the network until the entire table can be viewed.

If you write a query in Access, something like:

SELECT * FROM [yourTable]
WHERE [DateField] = #11/30/13#

That would attempt to pull all of the data for Nov 30th from the SQL table.  This would tell you whether they simply cannot see the most recent data for some reason or whether it truly is not available.

You would need to rename "yourTable" with the name of the table you have linked to, it probably looks something like "dbo_TableName" and "DateField" with the name of the date field

Creating a view in SSMS won't help from the Access end.  You need to sort out why your users that have Access cannot access the entire table.

ADO is a method for accessing ODBC data sources from within Access.  Lets not go that route quite yet.
0
 

Author Comment

by:66chawger
ID: 39742035
fyed,

That is a good idea... let me run a query using only a specific date.   Might take me a few as the users are in another location.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39742041
To expand on what Dale said - tables/queries are unordered sets and the only way you can ensure a predictable sequence is to use a query with an order by clause.  Access fools people into thinking that tables are always in PK order because whenever a database is compacted, Access rewrites all tables in PK sequence so at least the beginning of a table always looks like it is in the same order.  But,  due to the way updates happen, records can be moved from their original positions and rewritten elsewhere.  So, once you have updated even one record, the table might not be completely in PK order any more.  So, to restate the obvious, you must sort a recordset before making any assumptions regarding the range of the data it contains.

Also, check the record counts.

Select Count(*) as RecCount From YourTable;
0
 

Author Comment

by:66chawger
ID: 39742071
PatHartman,

Thanks for your input.  Yes, once the result set is open, the user selects "Transaction Date" and sorts newest-->oldest.  

Will run run the RecCount from the user's PC.  Moot from my pc as I am not having the issue.

FYI - ODBC connection established with user credentials and also tried with my admin credentials to see if that made a difference.  I did not think this would come into play, but wanted to make sure.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39742122
I'm sure someone already mentioned this but check the DSN and make sure you are all connecting to the same database.  The record count and the specific select that was mentioned earlier should make it clear whether or not you are all looking at the same database.
0
 

Author Comment

by:66chawger
ID: 39742164
Yes, all are connecting to the same DB.  Just waiting for results from user's pc for rec count and query on latest transaction date.
0
 

Author Comment

by:66chawger
ID: 39742556
fyed,

This is perplexing... I had them run the query

SELECT * FROM [yourTable]
WHERE [DateField] = #11/30/13#

This returned no results.  I had them do max(TransactionDate_MF) and this brought back 08/30/13... how is this???  There is a production DB and a test DB... they ARE linked to production as test only goes through 07/31/12.   This makes no sense.  Could it be MDAC, or any other driver/environment related variables?   What could possibly be limiting them to this range?  It's not like it is taking a while and times out, as soon as they open the Access DB object, BOOM, the results are there.   As stated before, I created a separate SQL link and get the same results on their machine to the same DB and get the same results.  

So, we have no issues on my PC, only at the other location.  To me this leads to something environmental... but I am not ready to put blinders on quite yet.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39742594
I'll raise the flag for some other experts to take a look at the thread.  They have probably backed off because someone else is already working it.
0
 

Author Comment

by:66chawger
ID: 39742621
Thanks Dale
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 250 total points
ID: 39742665
What are the machines? And are they 64 bit?

If so you need use the command-line to open "C:\Windows\syswow64\odbcad32.exe" to create the DSN.

Also is the field in the SQL DB a date field or a varchar? And if the SQL DB field is a date does it strip the time off it? If not then change the query to either:
SELECT * FROM [yourTable]
WHERE DateValue([DateField]) = #11/30/13#

Open in new window

or
SELECT * FROM [yourTable]
WHERE [DateField] > #11/30/13#

Open in new window

0
 
LVL 57
ID: 39742697
Since nothing seems to add up, I would start over and go step by step:

1. Using SSMS, get the record count.

From the users stations that is having the issue:

2. Create a new system DSN

3. Using Excel, execute a query and get the count

4. Using Access, create a new DB, a new linked table, and then get a rec count.

I would do all that without using any criteria.  I'd then go back and add criteria starting with Excel.

and since this involves dates, even though I don't think it's the problem, I'd verify the regional settings.

5. If in Access when criteria is applied, you still don't see all the dates, set the queries ODBCTimeout property to 0 and then try again.

 Somewhere along the way you should find your problem.

Jim.
0
 

Author Comment

by:66chawger
ID: 39742700
Yes, Windows 7 64 bit Dell Optiplex 790's.

I can use either  query and it works on my machine and the user's, difference is user is not seeing anything past a TransactionDate_MF of 08/30/13.   I can change the query above with #08/30/13# and it brings those back.  Does not find with #11/30/13#.  They ARE linked to the correct DB.  I even deleted the ODBC SQL links and added back.  

Not a problem creating the DSN through Access, I can do it that way or from command line, both work.  It is the result set that is different.   I am dumbfounded...   Tried get max on the date from user's machine and gives me 8/30/2013...  on mine it gives me 11/29/13.  This is not a multi-server issue either.. this DB only exists on one server.   I even changed the DSN to use admin credentials.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39742747
Jim,

Why using Excel?  

Didn't think of changing the ODBCTimeout in Access, since they were viewing the table, not a query.

Dale
0
 

Author Comment

by:66chawger
ID: 39742753
Are you asking why are we not using Excel instead of Access?

I thought about the ODBC timeout also.  Is there any possibility of a MS update, a KB possibly that changed MDAC?  Grasping at straws I know.  Let's leave it for today.. I am going over to the that building Monday morning and I can converse through EE from there.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39742769
Try updating a record from the user PC and look to see if you see the update from yours.

I know you've checked everything but it sure feels like you're linked to different BE's.
0
 
LVL 57
ID: 39742823
Dale,

 Just to use something other then Access.  In general, if Access is installed, Excel usually is, so it's always my first choice when I want to confirm something outside of Access.

 Access to some extent is the orphand child in Office.  It doesn't quite do things the same way as all the other Office apps (date handling for example).   So anytime anything weird comes up, I try to use another application in Office and better yet, an application totaly seperate from Office (which is also why I said check with SSMS first).

  Yet Access also does share some things with the rest of Office.   So between the three, it narrows the problem down to:

1. SQL Server/ODBC
2. Something in Office.
3. Something in Access.

Jim.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39742934
Hi,

did you compared the connection strings on both machines and the used drivers? Access usually uses SQLOLEDB but as far as I know that doesn't support the new date/time datatypes of SQL Server 2008. You could install the SQLNCLI10 driver (which is usually not installed on Windows) and create a connection with this one instead.
Moreover you should of course look into the definition of the table on the server to see which datatype is used. Access can work with smalldatetime and datetime datatypes, other datatypes like "date", "time" and so on will be converted to strings and not handled as dates anymore. So maybe the records are there but only not sorted like you expect. Use the find function to see if you can find the records in the list elsewhere.

If you want to go on using Access as frontend you should only use smalldatetime and datetime as datatypes.

You can test if that's the case if you look into the filters in datasheet view of a table, a field handled as date has a lot of special date filters like "last quarter" and so on, a field handled as text field has only text functions like "contains" and so on.

Cheers,

Christian
0
 

Author Comment

by:66chawger
ID: 39748729
Ok, finally!  Solved the problem.  It was two fold.  The building where this was occurring, the server name was resolving to 167.x.131.AA, in our building it resolves to 167.x.131.BB.  The [server name] was used when creating the SQL link in ACCESS.  But since it resolved to IP....AA and not ....BB, it was bringing up old data.  This would make sense, because I refreshed the link and still received the same results.    

I proceeded to delete the SQL Link from ACCESS and delete the .accdb and re-booted to make sure the updates took.   I then went through the process of creating the new link, only this time using the IP instead of server name.  Saved, tested and still received the old data (only transactions through 8/30/13)

I deleted everything again, went through the process and then noticed that when I specified the name for the System DSN, it said it already existed... I brought up ODBC admin and NO Machine DSN's were present.   I then proceeded to open the registry and search using the name for the previously created System DSN... and Voila!  Access stores its DSN's in a different location in the registry (windows 7 64bit).  I checked this on all the machines and it was consistent.   So even when I deleted the SQL link in Access, and created again with same name, but using IP... the previously created machine DSN was still present in the registry... Access had not cleaned it up, nor could I delete it from within Access.   So, I updated the registry entry and changed the server name to the correct IP and all was well.  

So, I found two problems and though I can fix it, I would still like to know either why Access is not cleaning up the registry when the SQL link is deleted/changed, why it is storing the DSN's in a different location for which the ODBC admin does not read from?   It's not like it was isolated to one machine, I found this consistently across the board.   There might be a logical explanation for this.

Again, Windows 7 64bit, MS office 2010.   Thanks everyone.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39748807
I sort of suggested it here.

The ODBC Administrator in the control panel is the 64 bit version. But most Office versions are 32 bit. So when you create it by going to the control panel, Access can't see it.

So you need use the command-line to open "C:\Windows\syswow64\odbcad32.exe" to create the DSN. That it is in the syswow64 is sort backwards to expectations, but that is how they did it.
0
 

Author Comment

by:66chawger
ID: 39748871
Jimpen, here is the article related to this: http://support.microsoft.com/kb/942976

I understand now, the issue was that until I found that the DSN's created in Access were not showing up in the 64bit odbc admin I did not have anything to go on.  I created the DSN through the Access GUI, which should be 32bit... but when I deleted the SQL LINK and DB and started over through the Access GUI using the same DSN name I used previously, Access says it already existed, which is fine... but I chose to use the IP address this time instead of the machine name... saved it, tested, thought everything was ok, but in all actuality, Access had not updated the DSN settings in the registry... this is simply a bug.  I verified by contacting MS as I have account and actually admitted that this was a flaw in a KB that was sent out previously.    

Anyway.... All of you had great input.  I am going to split the points up accordingly.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39748888
created the DSN through the Access GUI, which should be 32bit...

Both versions are named odbcad32.exe. The default location is C:\Windows\System32 for the 64 bit version. So when Access (or other Office App) fires it up using the ODBC Administrator  is using the default app name in the default directory.

But the 64 bit writes to a different part of the registry as you found out.

I went through the same issues way back when.
0
 

Author Closing Comment

by:66chawger
ID: 39748894
I appreciate the fast responses and quality of input and knowledge from all the Experts that contributed to this question.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now