Problem With Access DB over WAN with VB.NET front-end

[Clarkie]

Hello!

I have a VB.NET application accessing an Access DB over a 2 mbit
WAN-connection. The performance is terrible, simple SQL-statements resulting
in a few records can take 30 secs or more, even though there is just a
single user accessing the database. During the long wait both the client and
the fileserver remains idle, their processors are not busy at all. The same
app with the same database works fine when the database is placed on the
local network (not a possible solution in this case, though). The sysadmin
says the network is OK. The database is only 20 MB.

I've read that Access is weak in WAN, but is this valid also when not using
any forms and similar access objects? I use Access only as a database.

Best Regards

Clarkie

 

[Arvin Meyer [MVP]]

Proper indexing will improve performance, but it still will be poor. There
are only 2 ways to use Access/Jet over a WAN and get good (even great)
performance. 1. Use an asp or asp.net application to access the data. or 2.
Use Terminal Services. No other method will give acceptable performance.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Clarkie]

Some additional info that I omitted:
Clients are using Win XP Pro or Win 2000 Pro. The fileserver is running Win
Server 2003
I use ADO to connect to the database. I am quite confident that the VB.NET
code is not to blame here.

Some sample code, anyway:
dim sConn as string= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Z:\TA.mdb;Persist Security Info=False"

Public Shared Function Show(ByVal sSQLSelect As String) As DataSet
'****************************************************************
'Method returns a dataset (used for SELECT Statements)
'****************************************************************
Dim oDataset As New DataSet

Try
Dim oDataAdapter As New OleDb.OleDbDataAdapter(sSQLSelect,
sConn)
oDataAdapter.Fill(oDataset)
Catch ex As Exception
Throw ex
End Try

Return oDataset
End Function

 

[Alex Dybenko]

Hi,
yes, accessing Access DB via WAN is not a good idea. I would consider you
either using SQL server, or setup terminal services, to both database and
frontend run on the same server

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Larry Linson]

We really don't need any additional information to agree with you that a
2MPBS WAN is not a suitable environment for a Jet backend database (aka,
even by Microsoft sometimes, "Access" database). The reason is that a file
server database, of which Jet is an example, uses the server just as a data
repository, and all the work to access, manipulate, and save the data is
done on the user's machine.

You can use Access as a front end to server databases -- any ODBC-compliant
server works nicely; there are even two free ones from Microsoft itself
(MSDE, which comes on the CD with Access 97, 2000, 2002, and 2003, and is a
purposely limited version of full SQL Server; and SQL Server 2005 Express
which is limited in overall size and is missing some analytical tools in
full SQL Server, but is not, they say, limited in overall performance).

Larry Linson
Microsoft Access MVP