Opening Database & LAN Speed

[PBuller]

I have a multi-user database over a LAN, it runs fast on the host computer.
It is very slow over the LAN unless the Host computer has the Database open.
Then it runs like a dream.

I believe I have an issue with the way I open the database.

I use the following code in each of my forms: (that manipulates data)

Private Sub IBidder_AfterUpdate()
Dim wsp As Workspace
Dim Db As Database
Dim rsbid As Recordset
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabse (datalocation)
Set rsbid = db.OpenRecordset("bidders")
With rsbid
** Code to manipulate the data
End with
rsbid.Close
Set db = Nothing
End Sub

 

[Tom van Stiphout]

On Sun, 1 Feb 2009 09:22:02 -0800, PBuller

Better to make that database object global, and open it once at
startup time and close it once at shutdown time.

-Tom.
Microsoft Access MVP

 

[PBuller]

I could use your help with the specifics. I have tried the following code in
my startup form.

Option Compare Database
Option Explicit
Public wsp as Workspace
Public db as Database
public datalocation as string

I tried to set the following statements within my opening form: (private sub)
set datalocation = "m:\mccdata.mdb"
set wsp = dbengine.workspaces(0)
set db = wsp.OpenDatabase(datalocation)

and then in later forms I tried accessing these using the following
statements:

set rsinv = db.openrecordset("Inventory")

I get a missing variable db error

Do I need to place all of my openrecordsets earlier rather than in each
form? Or is the problem with my opendatabase statement?

Thanks

 

[Tom van Stiphout]

On Sun, 1 Feb 2009 10:40:00 -0800, PBuller

The reason your code fails is that the database object goes out of
scope when the form closes. I would want you to use a truly global
object. Put the declaration of the Database object in a standard
module:
global g_dbData as DAO.Database

To start an application, it may be better to use an AutoExec macro
rather than a startup form. In the macro write one line:
RunCode
Set the function argument to InitApplication (or any other name of
your choice.
Then in a standard module write:
public function InitApplication()
'TODO: Reattach BE database. Create g_dbData. Other initializations.

'Finally ready to show first form:
DoCmd.OpenForm "myFirstForm"
end function

-Tom.
Microsoft Access MVP

 

[Tom Wickerath]

I could use your help with the specifics.

Here is a method offered by Luke Chung, President of FMS, Inc.:

http://www.fmsinc.com/free/NewTips/Access/LinkedDatabase.asp


You might also want to look at an article I wrote:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[PBuller]

Thank you, what a difference a good brain can make.
It finally works like it should.