ADODB Connection failing to open for some users

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

ADODB Connection failing to open for some users

Post by Becks »

Background:
I have an Access database residing on a network server and an Excel workbook in the same folder. The Excel workbook is used by my department to book equipment and rooms, with the booking information stored in the database.
For some users (me included) it always works as intended, whether I'm directly connected to the network or logged in remotely. For others, it fails to open a connection to the database.

The code references Microsoft ActiveX Data Objects 6.1 Library

The code at issue is as follows , shortened to remove attempts at error-catching and retries

Code: Select all

Sub OpenDatabase(adMode as ConnectModeEnum)
 set bookings = New ADODB.Connection	'bookings defined globally
 
  with bookings
  	.Provider = "Microsoft.ACE.OLEDB.12"
  	.Properties("Data Source") = Thisworkbook.Path & "\dbname.accdb"
  	.Mode = adMode 		'either adModeRead  or adModeShareDenyNone, depending whether reading or writing
  	.Open
  end with
 end sub 
  
For those users having issues, it fails on the .Open statement.

The questions are:
has anyone else seen this behaviour or know of it?
is there a solution?
is there a work-around?

Thanks in advance
Kevin

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB Connection failing to open for some users

Post by HansV »

Do you know the error number or error message?
Best wishes,
Hans

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

Re: ADODB Connection failing to open for some users

Post by Becks »

the users report it as 80004005 Could not lock file
When one of the affected users copied the database and workbook to his Desktop, it wouldn't run but did work when he copied both into his c:\temp folder.
He has write permission in the network folder.
We both have the same version of Windows Defender
Another item that may be relevant is that the affected users have their network paths mapped to their "local" U: drive while my path is the full network path //...

TIA
Kevin

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB Connection failing to open for some users

Post by HansV »

They could try using the UNC path instead of the =drive mapping...
Best wishes,
Hans

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

Re: ADODB Connection failing to open for some users

Post by Becks »

i tried one version where the path was hard-coded as the UNC path without success

TIA
Kevin

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB Connection failing to open for some users

Post by HansV »

I'm very sorry, but I don't have a solution or workaround for you.
(If it's just for reading, you could copy a copy of the workbook to C:\Temp temporarily; that's not a good idea if you want to write to the workbook though)
Best wishes,
Hans

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

Re: ADODB Connection failing to open for some users

Post by Becks »

After having it decide to fail for me as well, I used the "copy to C: drive" approach as it is the easiest to implement. i'm using a text file on the server to denote whether the database is in the process of being copied or written to. Off to the guinea pigs for testing ..

Probably on a related note, in my version of Excel, the Get Data | From Database | From Microsoft Access Database procedure fails no matter where the database is located.

Thanks
Kevin

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB Connection failing to open for some users

Post by HansV »

What is the error message?
Best wishes,
Hans

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

Re: ADODB Connection failing to open for some users

Post by Becks »

And today would typify how it is going - it's working today, so I can't capture the screen message, except there wasn't an error code as such, just a copy link to the error dump - attached

Regards
Kevin
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB Connection failing to open for some users

Post by HansV »

I hope it'll keep working - that error dump doesn't tell me anything useful.
Best wishes,
Hans