office xp access

[ted medin]

Have a couple of problems:
We have 3 pc's using a common access db.
1. What are the ramifications for locking (records pages ...)
2. We have been getting some disk errors referencing the db thru a wireless
LAN. We even got a sever error warning from access. What are your
suggestions? TIA

 

[John W. Vinson]

Have a couple of problems:
We have 3 pc's using a common access db.
1. What are the ramifications for locking (records pages ...)
2. We have been getting some disk errors referencing the db thru a wireless
LAN. We even got a sever error warning from access. What are your
suggestions? TIA

As Joseph says, it's very important to use the Database Splitter Wizard to split the database into a "backend" (a shared .mdb file containing just the tables) and a "frontend" (everything else, a copy on each desktop).

Access is VERY demanding and sensitive to network lags and noise. In my experience it's risky to use Access over a wireless LAN unless it's very solid and stable - you're running a real risk of corrupting your database.

 

[ted medin]

Record locking keeps you and your users honest. There are choices to be
made, but the best choice depends on your situation.

Are you using a split database with the data on the server and each user
having a front end with all the forms queries reports etc?

No the complete db is on one of the pc's. All pc's have their own version of
office xp

Guess you will have to explain this to me :-(.

 

[ted medin]

As Joseph says, it's very important to use the Database Splitter Wizard to

split the database into a "backend" (a shared .mdb file containing just the
tables) and a "frontend" (everything else, a copy on each desktop).

Access is VERY demanding and sensitive to network lags and noise. In my

experience it's risky to use Access over a wireless LAN unless it's very
solid and stable - you're running a real risk of corrupting your database.

I really didnt want to hear this :-(. We are running 11g but could switch to
the double 11g. We are close enough that the signals are 95% excellent &
rarely get into the very good.

 

[ted medin]

That could explain your problems. The proper procedure is to split the
database. This is especially likely with the wireless system.

Where do i look for a how to? TIA

 

[ted medin]

to
split the database into a "backend" (a shared .mdb file containing just the
tables) and a "frontend" (everything else, a copy on each desktop).

We have a lot of vba code in the forms. Does that make any difference? TIA

 

[John Vinson]

We have a lot of vba code in the forms. Does that make any difference? TIA

The only thing that will make a big difference: if you open tables as
Table type recordsets, and use the SEEK method, the code won't work as
written. You'll need to either open a Database object pointing to the
backend, or modify the code to use a Dynaset type recordset (rather
than a table) and the FindFirst method instead of Seek.

 

[ted medin]

TIA

The only thing that will make a big difference: if you open tables as
Table type recordsets, and use the SEEK method, the code won't work as
written. You'll need to either open a Database object pointing to the
backend, or modify the code to use a Dynaset type recordset (rather

Ok familiar with the seek & think i understand the findfirst but unfamiliar
with 'db object pointing to the backend'. Could you give me an example,
where to find info ... . TIA

 

[John Vinson]

Ok familiar with the seek & think i understand the findfirst but unfamiliar
with 'db object pointing to the backend'. Could you give me an example,
where to find info ... . TIA

First solution, using Seek:

Instead of something like

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
....
Set rs = db.OpenRecordset("tablename", dbOpenTable)
<use the Seek method of rs>


use

Dim ws As Workspace
Dim db As Database
Dim rs As DAO.Recordset
Set ws = DBEngine(0) ' current workspace
Set db = ws.OpenDatabase("backend.mdb", <options>)
Set rs = db.OpenRecordset(...

Second method: don't use Seek. Instead open the (linked) database as a
Dynaset and use the FindFirst/FindNext etc. methods.