Solved

A Microsoft JET Database Engine error '80004005' but with a surprise twist!

Posted on 2009-05-02
8
817 Views
Last Modified: 2013-11-27
Hello!

I recently migrated an ASP Classic site from a Windows 2003 32-bit server to a new Windows Server 2008 64-bit server. This site uses a jet database engine to connect to a Microsoft Access .mdb file located on the server hard drive. After setting up IIS 7.0 thusly:

1. Created a new AppPool to run 32-bit applications

2. Assigned write permissions to the directory where the database file is located for IUSR and NETWORK SERVICE users

3. Made sure the "Load User Profile" was set to False

I thought everything was working fine. I could read from the database and write to certain tables, but then I was greeted with this:

Microsoft JET Database Engine error '80004005'

You cannot add or change a record because a related record is required in table 'basket'.

/content/pages/change_basket.asp, line 240




Line 240 is the objConn.Execute(SQL) statement in the code snippet

The frustrating part is that on the Windows Server 2003 machine, this worked perfectly! I haven't modified any code aside from the database path in the connection string. Any help would be greatly appreciated as I've officially run full-speed into a wall here. Thank you!

Rs.Open SQL, objConn, 1, 3
  response.Write(sql&"<br>"&rs.recordcount&"<br>")
  If Rs.RecordCount > 0 Then
    SQL = "UPDATE basketitems SET quantity = "&Rs("quantity") + 1&" WHERE product_id = "&product_id&" AND product_title = '"&product_title&"' AND product_size_desc = '"&product_size_desc&"' AND product_size = "&product_size&" AND basket_id = "&Session("basket_id")&";"
  Else    
    SQL = "INSERT INTO basketitems(product_id,product_title,product_size,product_size_desc,product_variation,product_colour,product_weight,our_price,rrp_price,quantity,basket_id,product_code,kittbuilder, customise_beanie) VALUES("&product_id&",'"&product_title&"',"&product_size&",'"&product_size_desc&"','"&product_variation&"','"&product_colour&"',"&product_weight&","&our_price&","&rrp_price&","&quantity&","&Session("basket_id")&",'"&product_code&"', "&kitbuilder_flag_insert&", "&customise_beanie&");"
  
	  '::: add basket id to kit orders :::
	  SQL_KB = "UPDATE kit_orders set basket_id = " & Session("basket_id") & " where id = " & product_id
	  objConn.Execute(SQL_KB)
	  ':::
 
 
  End If
	'response.Write(sql)
   	'response.end
    objConn.Execute(SQL) 
 
End If

Open in new window

0
Comment
Question by:chaseds
8 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 100 total points
ID: 24288936
You can use Filemon from Sysinternals http://www.microsoft.com/technet/sysinternals/utilities/filemon.mspx to see what folders / files are getting access denied.  You can also look in your Security event log to see what failures (assuming you have auditing enabled) to see what user account is accessing the machine.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 24289459
Is this the ONLY SQL statement that gives trouble?
0
 

Author Comment

by:chaseds
ID: 24290513
cs97jjm3: I have used Process Monitor as you have described and I receive no ACCESS DENIED messages. I DO however, receive a few BUFFER OVERFLOW messages on that file.

LSMConsulting: Yes, so far this is the only SQL statement that gives me trouble. I have another statement that adds a record to a non-related table and that works fine. However, I fear that any other statement that has a one to many relation will also have this problem. This statement is integral to the entire site working.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 24293878
In the "ELSE" portion of your If-Then block, you have an "SQL" variable and an "SQL_KB" variable ... you're running the SQL_KB string BEFORE you run the SQL string ... I'm not sure, but could this be causing your issue? I'm not sure about what those do, or how they interact with each other, but this might be an area to explore.
0
 
LVL 41

Assisted Solution

by:graye
graye earned 100 total points
ID: 24296527
Hang on... before we go down that rabbit hole, let's make sure that you've actually got the 32-bit OleDB Jet drivers actually installed!   An easy way to find out would be to lauch the 32-bit version of the ODBC applet in the control panel... then pretend to setup a new connection just so you can see which providers are installed.
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en 
0
 

Author Closing Comment

by:chaseds
ID: 31577872
Graye: I have the JetDB drivers installed, as I'm told that there is no 64-bit version. Thus, the 32-bit enabled AppPool. Also, the site connects to and displays the database information so that works.

As for the rest of the comments, I really appreciate all your input, but I managed to get it working by going back to an older version of the file. Turns out it was a relation issue I had screwed up and just never caught until it blew up. Thank you all for your help!
0
 
LVL 84
ID: 24302978
<Turns out it was a relation issue I had screwed up and just never caught until it blew up. Thank you all for your help!>

What was the relation issue?
0
 

Author Comment

by:chaseds
ID: 24365707
Hey LSM, sorry about the long wait. It initially seemed like a relation issue, but what it really was was a combination of things:

For starters, the only way to edit the database as of now was to transfer the file, edit it locally, and re-transmit it to the server. Doing this resulted in the error.

(Note: the file was placed in a folder with the correct permissions so naturally, it inherited them upon being placed in said folder. I verified this.)

The issue came about whenever we had to write something to the file, and ultimately it ended up being resolved by simply deleting the cache and cookie from the end-user's web browser. Strange, but true.

Thanks for your continued interest and I look forward to picking all your brains next time!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
SQL Group on First occurrence 9 25
JS does not refresh 6 20
Diminish Pop-up  in 3 seconds 7 19
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

777 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