Network Error can't be resolved without restarting Access

[markphsd]

I'm using Access on a wireless network with unbound forms.

Access can't seem re-initialize a connection to the networked computer, When the wireless decides briefly cut out and reconnect. I get a disk or network error, both through code and going to the linked table to open it.

If anyone has any info on this, let me know. Thanks in advance.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[jsteph]

Is it just the linked tables? I think if you relink via code it'll work fine. Trap the Disk or Network Error (I forgot the number), and then do a relink, then resume.
--Jim

 

[markphsd]

No, it's both.

Once the wireless connection drops, and reastablishes i can't open the linked tables.

I guess i didn't think that my code was accessing the linked tables.

So I have do a refresh i'm assuming. I'll try that. But it seems like that might cause a lot of delay. How long does a refresh take?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[psemianonymous]

Wireless networking is the pits for exactly the reason you stated above. What you're doing when you 'reconnect' to wireless is in fact, connect to the server again.

This means that when you have a file open on the server (in your case, the Access backend), and your wireless cuts out, and then you 'reconnect', you are opening a separate file lock on the access backend.


As far as I know Access will never work properly over a flaky network connection (wireless or otherwise), not now or in the future.

There's no solution except to run a really long wire instead of continue to struggle with wireless.

 

[markphsd]

So maybe that's a better reason for SQL server. I never bothered because i considered the benefits to be in the sp's and other features.

Do you know if Access would relink automatically to a SQL Server?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[psemianonymous]

I couldn't tell you for sure. I say there will still be problems on any platform that doesn't use a totally "expect the connection to die at any point" connection scheme. Using a TCP connection instead of file locking helps, but ... yeah, I'm guessing you're still going to have problems.

 

[jsteph]

How long does a refresh take?

That depends mostly on your network bandwidth, partly on the speed of the server.

If you're refreshing, what you really want to do is relink. Below is some semi-pseudo code for relinking a sql-server table:

on error resume next
strTname = "SomeODBCTable" [COLOR=green]'(can do this in a loop)[/color]
strCn = ODBC;DSN=mydsn;UID=myuser;PWD=mypwd;APP=Mydbname_" & environ("computername") [COLOR=green]'give more info than the default "Microsoft Access"[/color]
docmd.close actable, strTname
docmd.deleteobject acTable, strTname
if err.number <> 0 then
    msgbox "Can't relink " & strTname & " because: " & err.description
    err.clear
else
    On Error Goto myhandler
    Set td = db.CreateTableDef(strTname)
    td.Connect = strCn
    td.Attributes = 131072 'dbAttachSavePWD
    td.SourceTableName = strTname
    db.TableDefs.Append td
end if 'endif/else error/noerror

--Jim

 

[markphsd]

Oh, i can relink fine. I just don't want to. That takes a long time to. I'm looking for something that will be instant, not seconds, I don't have time for seconds.

So i'm looking much closer at sql server now.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[MichaelRed]

from your "sig", I'm guessing that you are in the 'cash register' business? And, this is the primary reason for your "impatience"? ...

If YES to ALL (2) of hte above, I would suggest that you change the schema - a LOT.

The typical POS system does need to respond. You really can't afford to lose a sale because the customer woun't wait for joe network to be revived (or even woken up).

The schma I have used it to down load the tables NECESSARY for the sales to the local processor on start up. Typically, this is only a sma;; part of the overall data base, just products (with the current pricing, description, and locally NECESSARY information). Process hte sales LOCALLY, saving the sale info LOCALLY, including the receipt info which you usually retain. When hte register is "closed", copy the session info to the server and batch update the "real" system info.

Optionally, you may then delete the local (POS terminial), Server or both transaction sets. I usually retain the server set as an added audit record but delete the POS terminial info to keep the footprint down.

Approached this way, the network is only an issue for the start-up / shut-down transients and these can usually be handled with out much customer involvement / frustration. Added benefits are incurred with the drastic reduction in network traffic, smaller footprint on the local POS systems, redundancy of informatio storage / increased auditability, independence of operation of local POS terminials ...

Flip side is generally a LOT of rethinking, design and coding to implement.



MichaelRed

 

[markphsd]

Micheal,

Thanks, but you went into a lot of things that are already taken care of. My only concern for this post was wireless.

I actually use several databases, stored locally and on the file server. Al that stuff is coolio.



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.