How to remotely connect to Access database

[Softy Devel]

Is this possible? I have a database on a server which hosts my website. I
want to connect to it remotely and perform queries from a client application
in a similar manner as can be done from a web app located on the server. Do
I need to change permissions on the directory where the .mdb is located, or
will this only work with SQL Server?

I am using MFC CDatebase object to connect is VC 6.

Thanks in advance.

 

[Arvin Meyer [MVP]]

Decent performance with a Jet database (or any database for that matter)
over a WAN requires either using a web server or a terminal server. And yes,
you need the same permissions on the folder on the server as you would on a
LAN.

 

[Softy Devel]

For testing purposes, I have set the folder permissions to be as loose as
possible. However, I still cannot connect. I have tried both DSN and
DSNless connection strings, and they are as follows:

Driver={Microsoft Access Driver
(*.mdb)};Uid=admin;Pwd=pass;Server=http://www.mysite.com;DBQ=c:\\online.mdb

Driver={Microsoft Access Driver
(*.mdb)};Uid=admin;Pwd=pass;Server=http://www.mysite.com;DSN=MyDatabase

Any suggestions on how to modify these, or are they correct?

 

[Albert D. Kallal]

Your connection string MUST resolve to a disk drive + directory + file name

Or,
\\servername\shared folder

This file is a standard windows file, and not a web server.

So, a horse is a horse is a horse.

A file is a file is a file.

What this means that unless you can browse to the file via network
neighbourhood, you can't connect.

Hence, you would have to setup a Windows NETWORK file connection. not a http
connection.

This means you would need to setup a vpn. That server would then show in
network neighbourhood (my network places), and you would be able to browse
files on that directly.

http, or FTP for that matter are NOT a file protocol, and thus are of NO
use. If your server is running linix, then you would have to provide windows
file compatibility, and that would mean you need to run samba on that linux
box. Reglardss, you can't use http to open, and update a file over a link on
the internet.

http <> windows file system

you need:

your computer <---- windows network protocol ---> file on
server


I outline some solutions here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Softy Devel]

Thanks for your response. It confirms what I have been reading so far. You
cannot remotely connect to an access database unless you have some sort of
direct network connection, which would make it appear as a . Other
technologies do however support http:// connections such as SQL server and My
Sql, but they do so with server apps.

For the app I am working on, unless I can programmatically set up a VPN,
then this solution is just not portable enough.

Thanks again.

 

[Albert D. Kallal]

Other

technologies do however support http:// connections such as SQL server and
My
Sql, but they do so with server apps.

Yes, and ms-access also supports the above types of connections also.
The problem is not ms-access, it is your internet, and your providers.

You can most certainly use ms-access with sql server, or mysql. In fact, the
last 3 versions has included a desktop edition of sql server for free with
ms-access. This data engine, or sql server, or oracle, or mysql can ALL BE
USED without ms-access.

For the app I am working on, unless I can programmatically set up a VPN,
then this solution is just not portable enough.

And, why not use a server based system, and continue to use ms-access for
the interface? (that is what most of us do, and was one of my suggestions in
the link).

Ms-access is not the database system. You can't build a form, or interface
in MySql, but you most certainly can in ms-access!

So, ms-access is the interface building part, and the choice of a database
engine can range from a file share system (like a mdb file), or a server
based system, like sql server. As mentioned, a free copy of sql server been
included on the last 3 versions of office for use with ms-access.

Not only is the desktop edition free, but also now sql server express. So,
you have a number of free choices for the database part, and ms-access will
work over the internet with these types of data servers.

I mean, you could setup a VPN, but then again, it would be too slow (as my
article points out), and worse, your data file would be subject damage.
However, using a database server solves this problem, and ms-access works
just fine with a database server.

 

[Softy Devel]

Thanks again for the info.

I have developed custom forms and controls in vc6 for this app, and I was
using ms access for the database. I am unfamilar with sql server express?

Can I connect to it using http:? Can I link access to it?
Does Sql server express work with vc6?

 

[aaron.kempf]

yeah that's right.. not only does Access provide AWFUL performance; it
is a nightmare for real-world use.

if you COULD open the database across the network you would probbaly
have logistics problems-- because you would need to open it
exclusively.

..NET 2.0 supports using SQL Server databases in a TRULY portable
format.
you can ship a MDF / LDF via FTP.. and then connect ot it via code;
there is no reason to 'register' the database with the database server.

this concept is known as 'user connections'

I wish that you made better technology decisions.
MDB shouldn't be used in the real world-- for any reason whatsoever.

LOSE THE TRAINING WHEELS KIDS

-Aaron
Realist

 

[Albert D. Kallal]

Thanks again for the info.

I have developed custom forms and controls in vc6 for this app, and I was
using ms access for the database. I am unfamilar with sql server express?

Can I connect to it using http:? Can I link access to it?
Does Sql server express work with vc6?

Well, actually, you never did connect to sql sever, mysql, oracle etc using
http. However, you CAN use a ip address, or a standard based web URL that
resolves to a ip address. In general terms, we are talking about a socket
based connection (ip address + port number). So, just to be clear, we never
did use http here. However, you must certanly can use a IP address, be it a
ip number, or a URL that resolves to an IP address.

As metoned, you can't use a plane jane windows file share for the back end,
as then you would need *windows netowrking* OVER the internet. So, you cant
"connect" to a simple windows file like a mdb (you have to open it!!).

However, when using odbc socket connection DOES work over the internet.

As for sql server express? Yes, it is a sever based system, and if you
install it on your web server (or Oracle, or sql server, or the free MSDE
that shippied on the office cd, then yes).

Sql server express is also a server based system, and again would be a good
choice.

You can read about sql express here:

about:
http://www.microsoft.com/sql/express/

download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

 

[aaron.kempf]

VPN works GREAT for Access Data Projects (ADP)
I use it every day for a couple of different clients.

MDB sucks cock and it doesn't run well over ANY network.

-Aaron

 

[Softy Devel]

Thanks again.

I have downloaded sql server express and recreated my database using it. I
have uploaded it via restore to my webhost. What a snap!

Can u direct me to a similar group where I might get more information on
connection strings, and how to remotely connect using an ip? I know ths is
an Access board.