Using MS Access is really slow in network, why?

  • Thread starter Thread starter Raymond
  • Start date Start date
R

Raymond

Hi.

I'm using Access-databases. See code below how I fill the
dataset (almost correct?).

On my client P4 2600Mhz HT this code is pretty fast, a
few hundred milliseconds. The problem is when the
database is on a server. When I open the database trough
the network this could take several seconds. This is way
to much!
What I wonder is if there are any better ways of filling
my dataset? Maby I can't use Access at all when I open
through a network? Must I use for example MSDE? What do
Microsoft generally say about Access, network and .NET?
Is there any special settings on the server I have to
make?

Thank you
/Raymond


Dim mcn As New OleDbConnection
(Provider=Microsoft.Jet.OLEDB.4.0;Data Source="xxx")

Dim sSQL As String
Dim da As New OleDbDataAdapter
Dim cmd As New OleDbCommand
Dim dtm As DataTableMapping = New DataTableMapping
Dim ds As New Dataset

sSQL = "SELECT * FROM x"
cmd.CommandText = sSQL
cmd.Connection = mcn
dtm.DataSetTable = "x"
dtm.SourceTable = "Table"
da.TableMappings.Add(dtm)
da.SelectCommand = cmd
da.Fill(ds)
 
Access is made to be a desktop database and it's old technology. It's not a
client server database even if you put it on a network. When you query the
DB, it has to push all the data over the network, not just a subset of it.
This is why you have a lot of trouble when you have multiple users over a
network.

IMHO, use MSDE. I'll admit I'm bigoted against Access for multi-user apps
because from everything I've seen, it's more trouble than it's worth. I
know many Access programmers claim they have 30 people banging away on it
over network drives and if the app is written correctly, it won't be a
problem. I've never seen this claim substantiated. MSDE is a much better
choice IMHO.

If your query is slow on the desktop, then indexes will probably help
(although they'll slow inserts somewhat), but there isn't much tuning I can
think of if everything works fine on a stand alone.

HTH,

Bill
 
Thank you for your awnser. I will talk to my team members
and dive into the world of MSDE.

/Raymond
 
Back
Top