'MS Access can't open database because it's missing or opened exclusively by another user - error 7866

guys i'm really perplexed by this and i have done an exhaustive search of all the solutions.

i'm trying to run a VBScript which works perfect on my computer but when i do it on my colleague's computer it always gives me an error 7866

here's my code and here's what i tried

Option Explicit
dim objAccess


Dim objLocalFolder
Dim objRemoteFolder
Dim objLocalVersionFile
Dim objRemoteAppFile


Dim strRemoteFolder

Dim strVersionFileName

Dim strAppRemotePath
Dim strVersionFileLocalPath
Dim strLocalVersionFileContents

Dim strNameOfFolderVBScriptIsIn
strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, wscript.ScriptName, "")

Dim strAppName
strAppName = "WBR.mdb"

Dim strLocalFolder
strLocalFolder = strNameOfFolderVBScriptIsIn

Dim strAppLocalPath
strAppLocalPath = strLocalFolder & "\" & strAppName



' Create the File System Object.
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")


msgbox objFSO.FileExists(strAppLocalPath)
'THIS GIVES ME TRUE WHICH TELLS ME THAT THE PATH IS CORRECT AND THE FILE DOES EXIST


Set objAccess = CreateObject("Access.Application")
objAccess.Visible = true


objAccess.OpenCurrentDatabase strAppLocalPath
'error 7866 - file exists so that means exclusive access? but no ldb file

Open in new window


1) made sure there was no password protecting the access file. no password in the VBA code as well. when going to tools and references also no password.

2) ensure i had the appropriate references. i'm using Access 2007 whilst my colleague is using Access 2007.
my referencesmy colleague's references
3) there wasn't any pop up box for security settings e.g. run unsafe expressions. btw how do i set Access' security settings to the lowest? i can do it in excel but i don't know how to do it in Access.

4) i'm opening the database as shared

5) it's on a local drive NOT a network drive so there aren't any network drive mapping issues

6) i created an empty database with the exact same name just in case the DB was the one causing the problem but the same issue occured. can find the file name using FSO fileexist but cannot open in Access

7) both my colleague and my computer show file extensions in file explorer

i'm going crazy here guys. really need to solve this. helpppp!!!!!
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
0
developingprogrammerAuthor Commented:
hi rgonzo1971, thanks for your help! however my database has no password and is not locked. how can i use this article to solve the issue i'm facing? thanks!!

also, if that is the case how come i can open the database on my computer but not on my colleague's computer? thanks!!
0
Gustav BrockCIOCommented:
Perhaps his strNameOfFolderVBScriptIsIn contains spaces. If so you need to wrap strAppLocalPath in quotes, like:

objAccess.OpenCurrentDatabase Chr(34) & strAppLocalPath & Chr(34)

/gustav
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mbizupCommented:
Gustav,

You might be able to help out with this one:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28028512.html
0
developingprogrammerAuthor Commented:
thanks gustav!

and thanks mbizup for helping me out with the other question! = )

here are some results i've got. some of them i've got 1 slash some i've got 2 slashes, how come whether it's 1 or 2 slashes it doesn't matter and it still works if the 1 slash works as well? thanks guys!!

colleague with chr 1 slash - doesn't workcolleague with chr 2 slash - doesn't workmyself no chr 1 or 2 slash - this worksmyself with chr 1 or 2 slash - this doesn't work
0
developingprogrammerAuthor Commented:
when i try this vbs on my other colleagues computers they all can't work. not just 1 of them but all of them. they are using Windows XP whilst I'm using Windows 7 professional. is that a cause?
0
Gustav BrockCIOCommented:
Weird. But double backslashes must be avoided.

/gustav
0
Gustav BrockCIOCommented:
Could you test, please, how the old-fashioned method works using Shell:

Sub RunApp(ByVal strFile)

  Dim objShell

  Set objShell = CreateObject("WScript.Shell")
  objShell.Run Chr(34) & strFile & Chr(34), 1, False
  Set objShell = Nothing

End Sub

/gustav
0
Helen FeddemaCommented:
I see an Access 11.0 reference, which indicates that computer is running Access 2003.  That might be part of the problem.  Access 2007 uses the new Microsoft Office 12.0 Access database engine Object instead of the DAO reference.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
I see, yup that is definitely very insightful and I'm thinking that could be the problem. What is the solution then? Using shell as Gustav suggested?

Gustav sorry for the late reply, I left the office already and about to doze off, will try the code in like 7 hours time = ) your help and support on this is very much appreciated!! Thank you Helen too!!!!
0
Gustav BrockCIOCommented:
No problem. Anden Helen may have a point.

/gustav
0
developingprogrammerAuthor Commented:
great guys!!!! it works!!!!!!!!!!

so now the next question would be: for a shell object, how do i assign it to an access object variable so that i can run the following code? basically importing excel sheets into access and running access macros. from what i understand shell is the programmability of user interface to software, but that doesn't give me an object model that i can play with like the createobject("access.application") does. thanks guys!!!!

Set objAccess = CreateObject("Access.Application")
on error resume next
dim strCurrentProjectName
strCurrentProjectName = objaccess.currentproject.name
If err.Number=0 then
	if objaccess.currentproject.name <> "WBR.mdb" then
		objAccess.OpenCurrentDatabase strNameOfFolderVBScriptIsIn & "WBR.mdb"
	end if
Else
	objAccess.OpenCurrentDatabase strNameOfFolderVBScriptIsIn & "WBR.mdb"
End if

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,"tblWorkHoursProcessing", strNameOfFolderVBScriptIsIn & "Book1.xls", True

objAccess.DoCmd.RunMacro "ProcessImportedData"

objAccess.Quit

Set objAccess = Nothing

Open in new window

0
developingprogrammerAuthor Commented:
guys i found that this page is quite relevant to my issue BUT the asker didn't face the opencurrentdatabase problem 7866 error that i faced

http://stackoverflow.com/questions/12164924/how-to-move-excel-data-to-access-using-vba-and-late-binding-no-ado-no-dao-d

so they all talk about using late binding when working between different version of access so that i won't face this reference problem. how can i use late binding to use the transferspreadsheet method and the docmd run macro method? thanks guy!!!!
0
developingprogrammerAuthor Commented:
yup guys i'm trying to be independent and resourceful here so i modified my code and treated the objShell as the objAccess. however i realise it doesn't give me the same properties and methods that the objAccess gives me. or rather Access object - so it doesn't infer the type internally.

i tested using the transferspreadsheet code and this code too
msgbox objaccess.currentproject.name

both can't work. so how can i get the methods to work like an Access object? thanks guys!!
0
Gustav BrockCIOCommented:
Can't tell, sorry, I haven't worked with Access this way.

/gustav
0
developingprogrammerAuthor Commented:
i see. thanks gustav. hopefully someone has come across this Access 2007 vs Access 2003 issue so we can learn from them!! = ))
0
datAdrenalineCommented:
- Do all users who wish to implement this scrip have appropriate permissions in the folder that WBR.mdb reside?

- Do all users of this script have the full blown version of Access on their systems, or are any using the Run-Time version?

---

- The reference between MS Access objects should be self-healing. BUT ... it seems as though each user should have the WBR.mdb on their local box in order to avoid conficts.

--

Now ... the fact that you get a dbl slash (\\) in your strAppLocalPath is the source of your problem.  The 7866 error occurs any time the .OpenCurrentDatabase can't resolve the passed string into a file.  Please note that .FileExists() has internal code the strips the dbl-slash out of it.  So ....

This ...
strFile = "C:\Temp\\MyDb.mdb"
objFSO.FileExists(strFile)

Will return True if MyDb.mdb exists in the C:\Temp folder.

However, this ...
objAccess.OpenCurrentDatabase strFile

Will return the 7866 error because the dbl slash (\\) is not stripped from the path and the Access instance cannot resolve the literal string into a valid file.

So ... the solution is to ensure the dbl-slash does not wind up in your strAppLocalPath variable.  You can do that fairly easily.

One way -- In your original code you have this:

strAppLocalPath = strLocalFolder & "\" & strAppName

Open in new window


But strLocalFolder has a trailing slash on it, so your line of code should be this ...

strAppLocalPath = strLocalFolder & strAppName

Open in new window


--- Or ---

You could change this line ...

strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, wscript.ScriptName, "")

Open in new window


to this ...

strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, "\" & 

Open in new window

wscript.ScriptName, "")

-- Or --
You can ensure that strLocalFolder does not have a trailing slash by changing this ...
strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, wscript.ScriptName, "")

Open in new window

to this ...
strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, "\" & wscript.ScriptName, "")

Open in new window


-- Or --

If you never know what you are going to get, or you just want to be safe, you can manipulate the strAppLocalPath variable to ensure there are no dbl-slashes in it prior to your call to .OpenCurrentDatabase with something like this:

strAppLocalPath = strLocalFolder & "\" & strAppName
strAppLocalPath = Replace(strAppLocalPath, "\\", "\")

Open in new window


---

Here is a sample of your code from your original post with a couple of my recommendations implemented.

Dim objRemoteFolder
Dim objLocalVersionFile
Dim objRemoteAppFile


Dim strRemoteFolder

Dim strVersionFileName

Dim strAppRemotePath
Dim strVersionFileLocalPath
Dim strLocalVersionFileContents

Dim strNameOfFolderVBScriptIsIn
strNameOfFolderVBScriptIsIn = Replace(wscript.ScriptFullName, "\" & wscript.ScriptName, "")
msgbox strNameOfFolderVBScriptIsIn

Dim strAppName
strAppName = "MyBackup.mdb"

Dim strLocalFolder
strLocalFolder = strNameOfFolderVBScriptIsIn

Dim strAppLocalPath
strAppLocalPath = strLocalFolder & "\" & strAppName
strAppLocalPath = Replace(strAppLocalPath, "\\", "\")
msgBox strAppLocalPath


' Create the File System Object.
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")


msgbox objFSO.FileExists(strAppLocalPath)


Set objAccess = CreateObject("Access.Application")
objAccess.Visible = true


objAccess.OpenCurrentDatabase strAppLocalPath

Open in new window


I hope this helps you out!
0
developingprogrammerAuthor Commented:
thanks datAdrenaline for your lengthy response and taking the time to help me out! i ensured that i followed all your said in your post and in addition put a msgbox before running the opencurrentdatabase code to ensure that there are no double slashes. however it still can't run.

does anyone have access 2003 who can test this code out? i'm using access 2007 and when i used the access 2003 on my colleague's computer it can't work.

seems like the code doesn't like .mdp files! only .mdb files need apply ha! = (
0
Gustav BrockCIOCommented:
mdp-files ware just my idea for renaming the FE so the user can't double-click it but has to use a shortcut to launch the FE. However, mdp-files are not known by the OS, thus neither can a simple shell command open it.

Your may need the full path to Access to open it:

"d:\path\msaccess.exe" "yourFE.mdp"

/gustav
0
datAdrenalineCommented:
"i ensured that i followed all your said in your post and in addition put a msgbox before running the opencurrentdatabase code to ensure that there are no double slashes. however it still can't run."

May we see your implemented code and the results of the message boxes?

Also, the mdb in question is indeed in the same folder as the script right?  If so, with your previous screen shots, I'd ask the users to see if they can open their local mdb in the Access UI to ensure the file is indeed accessible.
0
developingprogrammerAuthor Commented:
hey guys, my sincere apologies for the late reply, the project was dragging on too long and needed a different way to solve things.

just to ensure i get back to yall, gustav, i ensured that i followed what you said but it still didn't work and datadrenaline yup it was in the same folder as the script as well.

i couldn't get to the bottom of this problem and (i haaaaaate to say this but in the corporate timeframe driven environment....... = (((((   ) i couldn't invest more time into this anymore so i scrapped this way of doing things. sigh, so sad.

but guys, i really appreciate all your help and thank you once again!!!!
0
Helen FeddemaCommented:
What is the current situation?  I have Access 2003 on one of my computers, so I can test a database there, if needed.
0
Gustav BrockCIOCommented:
>  in the corporate timeframe driven environment.......

Oh, that's life. Thanks for the feedback.

/gustav
0
developingprogrammerAuthor Commented:
= ) but thanks for your help gustav, you've got me out of NUMEROUS fixes!! = ))
0
developingprogrammerAuthor Commented:
thanks Helen, let me work through some stuff and get back to you = )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.