Database connection over wireless network

[Peter Larsson]

Hi there,

I've recently developed a VBA-program in Excel that fetches and presents
data from a distant Access database over a wireless peer-to-peer network
(both computers running Win XP Pro). It has worked relatively well for some
time, but recently I'm often encountering errors when trying to fetch the
data. I am pretty sure that this is because of a poor wireless connection
since I'm also having some problems e.g. copying files to/from the remote
computer. On the other side, when executing a PING-command to the remote
computer from the command prompt, "only" about 10-15% of the packets are
lost (or perhaps that is too much?). And it also tends to work fine to
connect e.g. through a Remote Desktop Connection to control the remote
computer. Unfortunately, the manufacturer of my wireless router and access
point (D-Link) don't offer any good software to measure the quality of the
connection.

To connect and retrieve data through VBA, I use the following syntax
(DSN-connection):

Dim dbConn As New ADODB.Connection
Dim db As New ADODB.Recordset
dbConn.Open "DSN=DatasourceName"
db.Open "SELECT * FROM Table1", dbConn

Now I'm basically hoping that any of you experts out there can provide me
with some helpful comments to reduce my confusion and maybe confirm that my
problems are because of a poor connection, or maybe because of some other
reason? And maybe also suggest for how I can query the database more
successfully without having to change the hardware?

Thank you very much in advance!

// Peter Larsson

PS. If this is not the most appropriate newsgroup to ask for help in, maybe
somebody could suggest a better one? DS.

 

[Albert D. Kallal]

You can't really use a file share over ANY type of connection. That
connecting might be a VPN over the internet, or wireless connection like
yours. I would not risk it. You need to use either something like thin
client (the remote desktop you mentioned is great solution).

JET file share files don't take well to ANY type of break in the connection.
I would not even consider such a setup.

You can try a true client to server based system (ie: use the other database
engine that is include on the office cd).

You can read some of my thoughts about lans, wans and ms-access at:

http://www.attcanada.net/~kallal.msn/Wan/Wans.html

 

[Arvin Meyer]

The JET database engine, being a file server database engine does not
tolerate dropped packets without causing corruption. A Remote Desktop
Connection uses Terminal Services which works well in a wireless environment
because it doesn't transmit the actual data, it transmits an image of that
data. Your only other viable alternative is to use a thin-client (a browser)
which means a lot more work with a lot less functionality.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tony Toews]

I've recently developed a VBA-program in Excel that fetches and presents
data from a distant Access database over a wireless peer-to-peer network
(both computers running Win XP Pro). It has worked relatively well for some
time, but recently I'm often encountering errors when trying to fetch the
data. I am pretty sure that this is because of a poor wireless connection
since I'm also having some problems e.g. copying files to/from the remote
computer. On the other side, when executing a PING-command to the remote
computer from the command prompt, "only" about 10-15% of the packets are
lost (or perhaps that is too much?).

To add to Albert's and Arvin's replies Access requires a very solid
network connection unlike most other software. Losing 1% of the
packets is way too high.

And if you're having problems copying files that's also a sign of
troubles.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Peter Larsson]

Do you think using the other database on the Office CD (some light version
of SQL Server?) would be a better alternative? Would that database be less
sensitive to a packet loss?

How much work would it be to convert a database from Access to the SQL
Server "light" version?

Peter

 

[Albert D. Kallal]

Not a all. My working came out a bit wrong there!. (I was trying to say that
you can't run a file share on JUST ANY kind of network...there is some
limitations!).


If you have a good stable pc environment, then a multi-user ms-access in a
file share mode in a LAN is a real nice system. They are cheap to setup, and
are very low maintenance. There is always some risk of file damage, but the
risk is certainly low, and well worth the benefits you receive.

 

[Albert D. Kallal]

Do you think using the other database on the Office CD (some light version
of SQL Server?) would be a better alternative? Would that database be less
sensitive to a packet loss?

yes,. It can take incredible amounts of abuse and still run.

How much work would it be to convert a database from Access to the SQL
Server "light" version?

That is going to depending on your experience, and how large the existing
applications is.

You will be learning a new database system...that does have a learning
curve. Just like ms-access, you might want to grab some books. If you are
not familiar with sql server, then you might find creating a ADP project in
ms-access a good way to get your feet wet with a client to server setup.
However, for existing applications of any complexity, then you probably are
better off to transfer the table data to the database engine, and then link
your tables via ODBC. You then have to "tweak" the parts that don't work, or
parts where the performance too slow.

 

[Tony Toews]

Do you think using the other database on the Office CD (some light version
of SQL Server?) would be a better alternative? Would that database be less
sensitive to a packet loss?

Yes it would be a better solution.

How much work would it be to convert a database from Access to the SQL
Server "light" version?

The same as converting to SQL Server. <smile>

As Albert states this can be a lot of work.

Do a search at the Knowledge Base at support.microsoft.com using the
keywords "upsizing" to review the various white papers on upsizing
Access to SQL Server as well as to ensure you have any updates
required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at my website.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Larry Linson]

The same as converting to SQL Server. <smile>

Actually, it would probably be _more_ work than converting to full SQL
Server, because the "light version" (MSDE aka SQL Server Desktop) does not
include all the very nice adminstrative and development tools that Full SQL
Server or SQL Server Development does.

Larry Linson
Microsoft Access MVP

 

[Tony Toews]

Actually, it would probably be _more_ work than converting to full SQL
Server, because the "light version" (MSDE aka SQL Server Desktop) does not
include all the very nice adminstrative and development tools that Full SQL
Server or SQL Server Development does.

As far as administrative goes you have a point there. You'd have to
create your own set of tools to do backups and other mtce functions.

However as far as development goes you can purchase the SQL Server
Developers edition for $50 US.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm