error 2147467259 - vbscript queries on Access DB, client-side

[ecompa]

HI,

I am working on a tool to hare data in my department.
The tool is working fine for me and some colleagues, but for others it is not working. The error code is -2147467259, and the description mentions that the file is already opened exclusively by another user (which is not the case).
The interface is in HTML, using VBscript connecting to Access 2003, working in a terminal server environment.
All users have a change access to the folder where code and data are located. Users that cannot use the tool don't have MS Access in their profile.

Here is one of the connectionstring I used. I tried ODBC connection, with or without password, among others. I didn't try to use a worgroup file but really not sure it would help anyway.

Set conDB = CreateObject("ADODB.Connection")
dbStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strReadPath & "Data\LastMonth.mdb; Jet Oledb:Database Password=dashboard;" 
conDB.Open dbStr

Does someone have an idea on what could be the cause of the problem ? Any suggestion is welcome... I am a bit lost...
Regards
E.

 

[tsuji]

ecompa,

It is most probably due to heavier load traffic for your colleagues. Give a max count for connect failure before giving up. Check out ms article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;307640

In vbs, it can be done something like this. (Design your allowance time according to your evaluation.)
[tt]
'total allowance time = 20*100 or 2 seconds
const MAX_RETRY=20
const INTERVAL_DELAY=100
errorcount=0

Set conDB = CreateObject("ADODB.Connection")
dbStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strReadPath & "Data\LastMonth.mdb; Jet Oledbatabase Password=dashboard;"
on error resume next
do while errorcount<MAX_RETRY
err.clear
conDB.Open dbStr
if err.number<>0 then
errorcount=errorcount+1
err.clear
wscript.sleep INTERVAL_DELAY
else
exit do
end if
loop
if err.number<>0 then
wscript.echo hex(err.number) & vbcrlf & err.description
set conDB=nothing
wscript.quit err.number
end if
on error goto 0
'continue with connection established
[/tt]
regards - tsuji

 

[tsuji]

Amendment:

Should have deleted this line, as I have moved it up to the top. Need to if the wscript.quit part works.
[tt]
if err.number<>0 then
errorcount=errorcount+1
[red]'err.clear[/red]
wscript.sleep INTERVAL_DELAY
else
exit do
end if
[/tt]
- tsuji

 

[ecompa]

Thanks for your proposal Tsuji,

It is not working for the moment unfortunately, but apparently because of the wscript.sleep command. The error message is 424 Object required. Do I have to define this object in the beginning of the script ? How ?
E.

 

[tsuji]

ecompa,

The script as such is intended to be hosted by wsh. If you understand the same, then you have to update your wsh to 5.6 (the latest dated back a couple of years, but for NT/9x you need to update as a separate action.)

- tsuji

 

[ecompa]

Tsuji,

sorry, you seems to have more technical skills than myself.
The fact is that I can't install whatever I want, as we are working in a Citrix environment (Windows 2000 advanced servers, Citrix XP metaframe).
The application has worked properly with the old, not supported, connectionstring

"Driver={Microsoft Access Driver (*.mdb)};..."

and Access 97 DB.
I thought i could just solve the problem testing different Connectionstring, and preferably using OLEDB, to avoid problems in the future, but without succes until now.

I am not sure either that the traffic load is sufficient to explain the difference between users, as I noticed that this problem is only happening with users not having Access 2003 in their TS profile, but having no clue I really ant to try it.
I tried to include

Set WScript = CreateObject("WSH.WScript")

in my code but this doesn't work. Once again sorry if this question seems silly, but I don't have the same background as you have apparently.
Thanks for your help.
E.

 

[tsuji]

ecompa,

You wrote:
>The interface is in HTML
I should have taken into account. The tentative solution I proposed would not work that way. I can propose a twist for script hosted by a browser in html, see if it is closer to a solution.

<script language="vbscript">
const MAX_RETRY=20
const INTERVAL_DELAY=100

dim conDB
dim dbStr
dim bconn
dim errorcount
dim thdl

'structure
'dbop_init : [1] initialize global settings for db operation; [2] periodic call for connection
'connect : Attempt for connection and if fails set counters. Success or failure, it sets global boolean flag.
'dbop : based on global updated variables, it determines [1] to continue db operation and clear setInterval,
'       or [2] to connect again, or [3] to give up and clear setInterval
'dbop_close : clean up global settings


sub dbop_init
	Set conDB = CreateObject("ADODB.Connection")
	bconn=false : 	errorcount=0
	dbStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strReadPath & "Data\LastMonth.mdb; Jet Oledb:Database Password=dashboard;"
	thdl=window.setInterval("connect",INTERVAL_DELAY)
end sub

sub connect
	on error resume next
	conDB dbStr
	if err.number<>0 then
		errorcount=errorcount+1
		bconn=false
	else
		bconn=true
	end if
	on error goto 0
	dbop
end sub

sub dbop
	if bconn=false and errorcount>MAX_RETRY then
		window.clearInterval thdl
		db_close
		exit function
	end if
	if bconn=false then
		exit function
	end if
	bconn=null : errorcount=null
	window.clearInterval thdl
	'continue with the db operation---main functionality here
	'etc etc
	dbop_close
end sub

sub dbop_close
	set conDB=nothing
	bconn=null : errorcount=null
end sub
</script>

You set up some global variables and when you want the db operation proper, you call up dbop_init. The rest the script explains itself.

As a note, the whole idea behind is bound to the mskb article's diagnostic. But the reason for the error being thrown up can be manifold. The whole biz of diagnostic can be quite involved and not addressed.

- tsuji

 

[ecompa]

HI Tsuji,

thanks for this. I'll give a trial. But I agree with you: surfing around on the internet, I've found quite a lot of different reasons for this error code...
I'll let you know.
E.