• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

How do I fix "Unable to open" error working with Access 2007 attachments

Windows XP SP3
Access 2007 Office SP1

I used code below to attach over 600 wav files of contemporaneous case progress dictation to records in my violence interruption database


Worked great for quite a while, except for occasional "Unable to Open" an attachment file, which a reboot would get things back on track.

Frequency of the unable to open increased over time, every once and a while I'd get one saying Windows media player (11) didn't have the correct codec, but still a reboot would fix.

Now the message comes up all the time and I can't summarize my field dictation responding to shootings, stabbings, etc.... so I can't bill for my consulting time without using other methods.  Since I have other workers on the street this gets cumbersome.

Tried:

Updating to XP SP3, no dice
Applying SP1 to the Office Suite
Rolling back Windows media player from 11 to 10 in case that was the problem
Compacting and repairing...

To simplify troubleshooting I created a very simple new Access 2007 database with just one table and a record with an attachment field.  Tried attaching Word, PDF and other types of files besides .wav, using the Manage Attachments dialog box, not VB code.  Same error message all the time.

Oh, also uninstalled my AVG free 8.5 virus checker, as some checkers are touchy about opening attachments.... no dice either.

I can use the Manage Attachments dialog to save an attachment back to a folder and it plays there.  I also noticed earlier in my troublshooting that sometimes when I did this the saved file was of zero length, but deleting the attachment field, re creating it and rerunning the code put the attachments back into the database for use, for a while, then they wouldn't open again, without reboot, then eventually not at all.

I tried putting some Locatelli Pecorino Romano grating cheese in a bag and swinging it around my head screaming like a cross between a chicken and Marlon Mumbles Brando in the Godfather but this only forced the squirrels living in my attic to meet to discuss if it was a safe to raise their children there anymore.

Any ideas?
Private Sub Command14_Click()
'select the currently open database
Set dbCurr = CurrentDb
'confirm it is the correct one
Debug.Print dbCurr.Name
'create a pointer to the recordings recordset
Dim rsRecordingz As Recordset
'open Recordingz recordset
Set rsRecordingz = dbCurr.OpenRecordset("Imported Recordings")
'loop through records and attach recordings
Do Until rsRecordingz.EOF
    Debug.Print rsRecordingz!WaveFileName & " "; rsRecordingz!TimeStamp
    'prepare parent record for operations
    rsRecordingz.Edit
    'instantiate child recordset
    Set rsRecord = rsRecordingz.Fields("Recording").Value
    'Add a new recording object
    rsRecord.AddNew
    'attach the .wav file in the folder to the record
    rsRecord.Fields("FileData").LoadFromFile "C:\Documents and Settings\Default\Desktop\YVIRC Recordings\" & rsRecordingz!WaveFileName
    'update child record
    rsRecord.Update
    'update the parent record
    rsRecordingz.Update
    'move to next parent record
    rsRecordingz.MoveNext
    Loop
End Sub

Open in new window

0
jcutro1
Asked:
jcutro1
  • 12
  • 6
1 Solution
 
TheVeeeCommented:
Our you committing and closing your recordset after this?  Additionally I would then null the object out...
0
 
jcutro1Author Commented:
Added a rsRecordingz.Close statement, deleted the attachment field, re created it and re ran code.  No difference.  I'm not sure how to null the object out.

I'm going to create a new access 2007 database,, create a table with an attachment field, attach a pdf, word, wav, etc manually via the Manage Attachments dialog and unless it works,, will attach it to this reply to see if it works with your copy of Access 2007 in your environment.  

Hope I got the (2) accdb I double clicked, not sure if possibly wrong database was uploaded.  I can't see a confirming filename yet in the box below attach file....  oops, accdb not on list,, will zip it and re attach.

Upload interface not working, still thinks I am trying to upload the accdb file.  will send this and try responding and attaching again in new session.
0
 
jcutro1Author Commented:
Trying to send the zipped accdb again...  Damn.  It still thinks the extension is accdb and not .zip.  I give up.  Let me know if you have other ideas to try.
0
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.

 
TheVeeeCommented:
You having a thread left open somewhere.  Ok do you open the document before hand to preview?  if so close that thread, next for grins, close your database connection, the reopen it.  I know it extra IO for now, but will help.

To null a variable out in Visual Basic (sorry to much Java lately) set the object to nothing.
http://msdn.microsoft.com/en-us/library/0x9tb07z.aspx


  http://www.astahost.com/info.php/how-connect-read-write-close-database_t6325.html
0
 
jcutro1Author Commented:
Does not appear to be at all related to my code as same intermittant problem ocurs with manually adding attachments via the Manage Atachments dialog.

Does appear to be thread conflict related, however the workaround that appears to work, not only for .wav attached files but also for other attached .doc files is to:

1. open the database, click the enable the disabled content message that first appears, though even if I don't it appears to work
2. double click a .wav attachment and instead of opening it, save it to disk
3. go to that file independent of double click it to launch windows media player 10 (I haven't yet reinstalled wm 11)
4. after the .wav plays, go back to access and then double click the attachment again and it will play, sort of priming the pump after which
5. any other attachment, .wav or .doc, appears to open ok, though I think it will probably stop working again at some point, haven't done more than about 4 or 5
6.  close that access database, open another with attachments, and that one appears to work also

This is not exactly a work around that would pass muster in a product that non technical people could be expected to follow to get their summaries of field work done.

What would you suggest to get more info on what is going on here and how to fix it permanantly.  Do you have access to more info on similar problems and their fixes, eg. microsoft developer network or other experts.

It doesn't appear to be a coding but an application interaction problem.  If it were Unix there are ways to trace these things but I don't have any access to tools for tracing complex application interactions in XP

Do you agree this has to do with application interactions and can a solid fix be found?

Also, can you replicate this behavior in your environment?   I would think not as if this were that general a problem with attachments in Access 2007 having bad relations with another Microsoft product like Windows Media Player both versi 10 and 11 it would have been addressed sooner and in the Office SP1 release.  

What is weird about my environment that causes this intermittant behavior?  Listing some things I run...

MS Activesync for iPAC handheld Windows CE
AVG 8.5 free  virus (was still problem with this uninstalled)
Acronis Backup
Sony Picture Utility
Sony digital voce editor
SierrWire 3G watcher with USB cellular data modem
Windows Search added to XP and indexes files in bkgnd
MightyFax (im going to uninstall this as don't use and will tell you if it fies)
Essential Fax (will also uninstall)
PGP encryption but not working with any applications, using standalone but will also uninstall)
Dragon Naturally Speaking 7.0 (may also uninstall then upgrade to current version)

I'll start with the most likely ones to have processes and threads running in background that could interfere wih applications launching or communicating with each other.

Please research this as I haven't found much on this via google searches or microsoft knowledgebase, you may have better resources via experts exchange....

Thanks, will not accept solution for now as the workaround doesn't really constitute a solution.

0
 
jcutro1Author Commented:
Also, just got
0xC00D1197: Cannot play the file
from windows media player 9 (not 10, my rollback wasn't to 10 but 9, i was in error previously)

got this after WMP 9 said it couldn't find the specified file and I clicked for more information

Did the save from Manage Attachments, then with database still open, doubleclicked the saved file and it gave me the same error message, closed the WM9 app then back to database and doubleclicked ANOTHER .wav file and it worked, so it doesn't have to be the same .wav file, it still corrects the problem until it just starts doing it again, very intermittant.

Hope this dialog helps you find something more solid for a solution.  Thanks for your persistence.

0
 
TheVeeeCommented:
Well to be perfectly honest, Microsoft Access is horrible database and IM using database lightly for it.  It was originally jjust a add on for home use and now many poeple that our managers have forced it onto the business world due to it be free add on to Microsoft suite when purchased.  

SQL Server was created to address the many issues Access has and it more of true database software, but of course, costs.

I migrated from Access over 10 years ago and now I use a free product called MySQL database.  Its a true database and actually works like the big boys... Oracle and DB2.

Your probably hitting one of Access limits or short comings unfortunely.

Sitting here its easy to say to switch to a real database, but I also know what that entails.  You may look at just doing a part of your appllication in more true database software.  We store PDFs and files all day in Oracle.  I hate to imagine doing in Access which really isnt a true database.
0
 
TheVeeeCommented:
I would try checking your software on operating system.   Bring up all the patches for your particuliar system up to date.  You may have a mix and match of items which arent playing good together.  Next I would ensure each software package I have is the correct version for you OS.  Something just is playing right.  Also check the task manager when starting stuff and pay close attention to Processes tab and sort by CPU so what ever is running will pop to the top.  This might help you in deteriming a possible issue with what software.

Next check your disk space.  Ensure your not almost full.  Sometimes when your reaching maximum disk size programs do some weird stuff.

Then Im taking it that your running directly from a machine and not crossing over the network?  If network, then other things will need to be checked.  Best way to solve this real quick on network on narrowing down issue, is to actually physically logon the server where the item is stored and try it there to see if works.  This way you have instantly took the network out of the picture.

Just some food for thought, take what you need and keep me informed... will try to keep helping...
0
 
jcutro1Author Commented:
Thanks.  Have monitored the processes sorted on CPU and just se Access and WMedia player at top.  Any patches besides auto upate, which is current and just applied the 200 plus Mb SP1 for Office.  No network  as I'm running off local hard drive but I am at 1 gig free of 18 gigs so will clear some space by archiving to my 250 mb external and see if that helps.   I did have many more problems when I had less RAM but i'm now up to the max for my laptop 2 GB so there is lots of swap space.  Will suspect the almost full drive next and clear space and report.  Re the real database issue no funds currently for this but as our Cease Fire Violence Interruption project just got some major funding from NYS and collaboration with University at Albany may be able to borrow some of their real tools.  Thanks.
0
 
TheVeeeCommented:
Also check your temp space on your hard drive and clean, system uses that for sorting and so forth...
0
 
jcutro1Author Commented:
OK.  
 
I have to start packing to go from NY to VT and will be working on this over the weekend along with other stuff.

Are you OK with leaving this ticket open until I try all of the things we talked about so we have clarity on what is causing the problem so others may benefit from the solution, or will that have a negative impact on you?

0
 
Anthony PerkinsCommented:
TheVeee,

>>SQL Server was created to address the many issues Access has and it more of true database software, but of course, costs.<<
Huh?  I think you need to get your facts straight. SQL Server was created long before MS Access. It has been around for around 20 years:
http://en.wikipedia.org/wiki/Microsoft_SQL_Server
http://en.wikipedia.org/wiki/Microsoft_Access

The SQL language has been around for over 30 years:
http://en.wikipedia.org/wiki/SQL
0
 
jcutro1Author Commented:
Clearing space on the hard drive from 1 Gb of 18 free to 3 plus free appears to have done the trick.  When you reply I will close out the ticket as I see no way to explicily do that that indicates that as the solution.   Thanks much.
0
 
jcutro1Author Commented:
I also ran chkdisk and de fragged the drive.
0
 
TheVeeeCommented:
Cool, good to hear it got going!    
0
 
jcutro1Author Commented:
Environment:  MS Access 2007, Windows XP SP3

Problem:  When attachments are double clickked or opened by the Manage Attachments dialog of Access 2007, an "Unable to open: filename" error occurs, or an error in Windows Media Player 9, and 11 indicates it is an unrecognized codec or other error.   Error is intermittant and opening and closing Access database sometimes stops the behavior, but it recurrs.  This also occurred after applying Office SP1.

Solution:  Several things were tried including removing virus protection software and other programs which may interfere with opening files with no significant change in the behavior.  Solution suggested by TheVeee was that some programs misbehave when the hard drive is near capacity.  Increasing the free space from 1 Mb free on 18 Mb drive to 3+ Mb free resolved the problem.  Thanks to TheVeee for the solution.
0
 
jcutro1Author Commented:
Award all points to TheVee, not sure what the total was to award.....  tried 500, probably should have just checked his most recent reply....
0
 
jcutro1Author Commented:
Thanks TheVeee.  The low amount of free disk space was causing the problem.  Once I cleared some space the attachments opened fine.
0
 
jcutro1Author Commented:
Environment:  MS Access 2007, Windows XP SP3

Problem:  When attachments are double clickked or opened by the Manage Attachments dialog of Access 2007, an "Unable to open: filename" error occurs, or an error in Windows Media Player 9, and 11 indicates it is an unrecognized codec or other error.   Error is intermittant and opening and closing Access database sometimes stops the behavior, but it recurrs.  This also occurred after applying Office SP1.

Solution:  Several things were tried including removing virus protection software and other programs which may interfere with opening files with no significant change in the behavior.  Solution suggested by TheVeee was that some programs misbehave when the hard drive is near capacity.  Increasing the free space from 1 Mb free on 18 Mb drive to 3+ Mb free resolved the problem.  Thanks to TheVeee for the solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now