intermittent connect error (1 Viewer)

[idxyz]

hi,

my vbscript has a routine that opens a remote access database for querying records from a table.
One or two application(s) at other PCs do open the same remote database table for own purposes.

my routine looks like this code:

con = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\remote server\...\myDB.mdb"

set objc = createObject("ADODB.Connection")
objc.open con
dbqry="select ..."
set rs = objc.execute(dbqry)

go through the records, then

objc.close
set rs = nothing
set objc = nothing


after like several hundreds (it varies) times of continuous open/close i get these:

at the "objc.open con" line , i get error:

Error Description: Unrecognized database format '\\remote server\...\myDB.mdb'
Error number: - 2147467259
Error Source: Microsoft JET Database Engine

at the execute line I get:
Error Description: Operation is not allowed when the object is closed.

 

[arnelgp]

you can't do that with access when your remote connection (lined) is far beyond 100 meters.
or worst you are using a wifi to connect.
try using terminal service (ts) or citrix technology.

 

[The_Doc_Man]

That negative-number error when converted to hexadecimal is 80004005, which is actually a catch-all error that says "I tried something with a partner process but got back some error." Doesn't say what error code the partner returned. However, the secondary message (about "object is closed") suggests that the error is a closed connection, so your choice of titles was correct. This means I have to support arnelgp's analysis. Your connection is probably being made over an unsteady connection that exceeds the limits of Ethernet or WiFi stability. For Access, that just isn't going to work. The "Unrecognized database format" probably indicates corruption due to network instability leading to a failed update operation, and that usually kills a DB cold.

 

[idxyz]

what work i can use to get around it?. Like can I repeat the reconnect in case of the intermittent failure to open?.
I use a local PC to connect to remote server where the back end .mdb file resides.

 

[The_Doc_Man]

Once you receive the "Unrecognized database format" message you are done until you manually intervene to repair the database - if you can, which from your description, might not be possible. Once you get that message, you CAN'T open the database to do a programmed reconnect because you won't be able to open the database at all.

You say "remote database" but you have not stated the actual connection method from a network viewpoint. Are we talking local area network, local wi-fi network, wide area network, or something else?

To effectively use an Access database back-end (which you say you have - the .MDB file), you must either be local to it (in the local area network sense of "local") OR you must try to set up RDP-style connections where you web-connect via RDP (or the commercial product known as CITRIX). The RDP setup can be tricky since you need to keep the front-ends separate for each user and the default CITRIX or RDP setup doesn't necessarily do that. If you used CITRIX there is a licensing cost and a tricky setup if you aren't familiar with product installations.

Your problem is that Access uses a protocol called SMB (Server Message Block) which is the default Windows File Sharing and Printer Sharing protocol. But this is a TCP-class protocol that sequentially numbers messages to permit detection of drop-outs in a sequence. If you drop the network, usually for security reasons you cannot reconnect any dropped connections, so you start new connections if you DO reconnect. The system knows this is a different connection even if it is from the same user because the sequence number doesn't align properly.

The lost connection might have been lost in mid-update and your "drop" event thus leaves the DB half-updated. I think you can guess from that description that your DB is not going to be very useful after that point. That incomplete update can leave the DB structurally unsound - which is usually what the "Unrecognized Database Format" message is trying to tell you.

Bottom line: Access is not a long-haul utility. It works on a local area network only unless you use something like RDP or CITRIX. It was built for a small office environment, not a spread-out business with multiple remote offices. The first published version of Access was 1992 - over 30 years ago - and networking has moved on quite a bit since then. But with regard to networking, Access has NOT moved on, so its limitations stick around.

 

[CJ_London]

after like several hundreds (it varies) times of continuous open/close i get these:

Why not open, do your several hundred operations, then close

Just noticed this is in the macros forum - given you are providing vba code. Maybe this is an excel forum question ?

if this is an access question perhaps your several hundred operations can be accomplished in one query. And any reason why you cannot use a linked table?

 

[ebs17]

What bothers me about the code:
I would close a recordset that I open as quickly as possible. You do not do that.
On the other hand, I would create a connection as once as possible and maintain it for as long as I need it for all of my measures. In contrast, you are very active in canceling.

 

[idxyz]

could show me what you mean by:
"I would close a recordset that I open as quickly as possible".

 

[CJ_London]

what is being suggested is

open connection
while task not completed
   open recordset
   do something
   close recordset
next task
close connection

 

[idxyz]

thank you all.