Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access on XP vs MS Access on Win7

Posted on 2012-08-16
28
Medium Priority
?
835 Views
Last Modified: 2012-08-27
I have a MS Access 2003 database that uses linked tables to a database on MS SQL Server 2000.  I have created an update query in Access that works perfectly when I open the Access database on a WinXP machine (all 100 rows are updated).  However, when I open the same Access db using a Windows 7 machine, I get the following error when I try to run the update query:

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 100 field(s) due to a type conversion error, 0 records due to key violations, 0 records due to lock violations, and 0 record(s) due to validation rule violations.

Here is the query:

UPDATE (dbo_PART INNER JOIN dbo_VENDOR_QUOTE ON dbo_PART.PREF_VENDOR_ID = dbo_VENDOR_QUOTE.VENDOR_ID) INNER JOIN dbo_VENDOR_PART ON (dbo_PART.ID = dbo_VENDOR_PART.PART_ID) AND (dbo_VENDOR_QUOTE.VENDOR_PART_ID = dbo_VENDOR_PART.VENDOR_PART_ID) AND (dbo_VENDOR_QUOTE.VENDOR_ID = dbo_VENDOR_PART.VENDOR_ID) SET dbo_PART.UNIT_MATERIAL_COST = Round((dbo_PART.UNIT_MATERIAL_COST*1.07),2), dbo_VENDOR_QUOTE.DEFAULT_UNIT_PRICE = Round((dbo_VENDOR_QUOTE.DEFAULT_UNIT_PRICE*1.07),2)
WHERE (((dbo_PART.PRODUCT_CODE)="FILM") AND ((dbo_PART.COMMODITY_CODE)="SHRINK") AND ((dbo_PART.PREF_VENDOR_ID)="10012"));

I'm at a loss.
0
Comment
Question by:regbit
26 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38302149
Are you running this query through code?

Dim strSQL as string
strSQL = "UPDATE ..."
CurrentDB.Execute strSQL ,dbfailOnerror

Many code segments need to be changed when running on a 64 bit system.
0
 

Author Comment

by:regbit
ID: 38302175
No, the query is not being run through code.
0
 
LVL 85
ID: 38302218
First thing to do is make sure that the machine is fully up to date in regard to Office and Windows updates.

Where is the database installed on the Win7 machine? If it's in Program Files, try moving it to the Users\<YourUserName>Documents folder. UAC can do funny things with read/write programs in the Program Files location.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:regbit
ID: 38302569
Updated Office and Windows, same error.

The database is located on a file server (Windows Server 2008).  However, I copied the database to the local machine.  Same error.
0
 
LVL 85
ID: 38303925
Can you upload the database? Be sure to remove any sensitive information first, and provide exact instructions on recreating the error.
0
 

Author Comment

by:regbit
ID: 38305946
Attached is an MS Access 2003 db.  

To try to recreate the error:
Import the database into a SQL Server 2000 db.
Create a new MS Access db with tables linked to the SQL Server db.
Run the update query in MS Access.
Try this on both an XP machine and a Win7 machine.

FYI:
The XP machine and Win7 machine each have a differently named System DSN for the same SQL DB.
testdb.mdb
0
 
LVL 85
ID: 38307553
Hmmm .... I can run this successfully on a Win7 box with Access 2010. Haven't tried 2003, will do that later.
0
 

Author Comment

by:regbit
ID: 38320083
I also tried creating a new MS Access 2003 db on the Win7 machine with tables linked to the SQL Server 2000 db to no avail, I get the same error when running the update query.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38320572
Is there a PK in the table you are trying to update on SQL Server?  If not, you need one.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38320627
Here's what I would try:

1. depending on how the tables are linked, review and compare the settings in ODBC element describing the database. Especially pay attention to using quoted identifiers.

2. Run Profiler on SQL server and find out what query gets sent. This is tell how the query was converted, and where the error is.

3. If the above does not help, rewrite the query to use SQL Server syntax, and make it passthrough, so you don't rely on ODBC driver translating your double-quotes.
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 38320628
Couple of ideas for possible things to check:  

1 - are there any regional settings in Windows ( e.g. number formatting and similar things) which may be different between the computers ?

2 - can you isolate and identify what is causing the error by creating other similar test queries, ( ie try the same kind of WHERE clause with a SELECT statement etc to see if the error is there, try a query that JOINS the same tables but has a different Where clause, etc)
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38320754
I'd check your security settings on the windows 7 machine.  It sounds like perhaps it's not a trusted document, or it's got some higher level of security.  Give that angle a shot.

-SA
0
 

Author Comment

by:regbit
ID: 38320845
I will update with new information as I try them:

Is there a PK in the table you are trying to update on SQL Server?  If not, you need one.

There are two tables I'm trying to update.  PART has a PK, VENDOR_QUOTE does not.  However, this has never been a problem on the XP machine.

can you isolate and identify what is causing the error by creating other similar test queries, ( ie try the same kind of WHERE clause with a SELECT statement etc to see if the error is there, try a query that JOINS the same tables but has a different Where clause, etc)

If I change the query to a SELECT query, it works just fine.  If I try to UPDATE with a different WHERE clause, I get the same error.

depending on how the tables are linked, review and compare the settings in ODBC element describing the database. Especially pay attention to using quoted identifiers.

As far as I can tell, the ODBC settings are identical.  It is not configured to use regional settings.

are there any regional settings in Windows ( e.g. number formatting and similar things) which may be different between the computers ?
I'd check your security settings on the windows 7 machine.  It sounds like perhaps it's not a trusted document, or it's got some higher level of security.  Give that angle a shot.

Where exactly can I view these settings?
0
 
LVL 44

Expert Comment

by:Davis McCarn
ID: 38320862
Are you running the same version of Access on both systems?
Which one? (2002, 2003, 2007, 2010)
I can tell you you will need to grant full permissions to the Access database folder for the Win7 users or they will be unable to create the temporary files needed.
0
 

Author Comment

by:regbit
ID: 38321012
Are you running the same version of Access on both systems?
Which one? (2002, 2003, 2007, 2010)
I can tell you you will need to grant full permissions to the Access database folder for the Win7 users or they will be unable to create the temporary files needed.


I am using Access 2003 on both systems.  I am currently logged into the Win7 machine as a domain admin.

Run Profiler on SQL server and find out what query gets sent. This is tell how the query was converted, and where the error is.

If we are using the Profiler correctly, when we run the update query on the XP machine, we are getting a lot of information from the Profiler.  However, when we run the query on the Win7 machine, nothing appears in the Profiler.  Strangely, this is also the case when running the SELECT query (which returns results) on the Win7 machine.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 1000 total points
ID: 38321049
>  Strangely, this is also the case when running the SELECT query (which returns results) on the Win7 machine.

This is absolutely impossible, and means that you somehow filter out the result. Review if there's any filter in place. If not, then double-check if you are running profiler on the correct server, including the instance. Maybe it's aliased to another server in the sql client configuration.
0
 
LVL 44

Expert Comment

by:Davis McCarn
ID: 38321122
After you install Office 2003 SP3, you encounter various problems in an Access project when you work with a Bit data type column in a linked SQL Server table
 http://support.microsoft.com/kb/943967
The functionality of an add-in, an ActiveX control, or a COM add-in is reduced, or the functionality is blocked after you install Office 2003 Service Pack 3
http://support.microsoft.com/kb/938814
More at the bottom of this page: http://support.microsoft.com/kb/923618
0
 
LVL 59
ID: 38321130
Still stuck on this?

Jim.
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 1000 total points
ID: 38321193
The first thing I would do is get rid of the DSN, if the DSN is indeed being used by your linked Table objects.  To verify what your linked Table objects are useing to connect, execute this command in the Immediate windows of the VBA editor ...

? CurrentDb.TableDefs("someLinkedTableName").Connect

Take a look at that string and see of the DSN is referenced, or is it a 'resolved' string. Typically when Access creates a linked Table object, the DSN information is "snap shotted" then used to build a resolved connection string.

Either way, when you indicate:
"The XP machine and Win7 machine each have a differently named System DSN for the same SQL DB."

That means you are likely changing the database objects, so be sure to check the .Connect property in both environments.  If the .Connect property in both environments makes sense, then ensure your ODBC drivers are up to date.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38321370
Have you tried detaching and reattaching the SQL Server tables?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38321498
Same user id logging into both systems?

Are they on the same domains?

mlmcc
0
 
LVL 59
ID: 38321660
Sounds like an ODBC driver issue.  Make sure your using the same driver on both machines.

Jim.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38321765
(retracted)
0
 

Accepted Solution

by:
regbit earned 0 total points
ID: 38322062
Review if there's any filter in place.

We did have a filter in place in the Profiler.  It was set to only catch a specific login.

? CurrentDb.TableDefs("someLinkedTableName").Connect

From XP machine:
ODBC;DSN=VM_RESTORE;Description=Restore;APP=Microsoft Office 2003;WSID=ISHPDESKTOP;DATABASE=RESTORE

From Win7 machine:
ODBC;DSN=dbrestore3;Description=RESTORE DB;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=MICHELLE-HP;DATABASE=RESTORE

I think when I first created the DSN on the Win7 machine, I first used Windows NT authentication, then later switched to SQL Server authentication when I realized that was what the XP machine was using.  However, I was still getting an error.  I removed the DSN and created a new one using SQL Server authentication from the beginning.  Now getting:

ODBC;DSN=dbrestore;Description=RESTORE DB;APP=Microsoft Office 2003;WSID=MICHELLE-HP;DATABASE=RESTORE

This appears to have worked.  I can now run the update query with no problems.
Thank you everyone for your help!
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38322210
Glad to here that you were able to resolve your issue with the information obtained in the responses!
0
 

Author Closing Comment

by:regbit
ID: 38335827
My comment is the solution because it contains what was done to correct the problem.  The other two comments I chose as the solution certainly led to fixing the problem.  I wouldn't be opposed the giving the best solution to datAdrenaline.  However, removing the DSN, then  creating a new one with SQL Server authentication from the beginning, does not appear in his comment.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

580 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