Q: Can Local DAO & remote IP ODBC coexist?

[Greg Gursky]

Hello:

I'm doing some planning on a potential project with which I need
some help. The database at the focus of this question is a
MS Access database that is currently accessed by DAO from a local
webserver. I need to access the same database remotely through
TCP/IP. There will be potentially many transactions coming from
both the web server and remotely, so the solution needs to keep
the data safe in such an environment. However, most likely, the
remote data accesses will be read-only whereas the web server
data accesses will always be both read and write.

I have never worked with either DAO or Access before. I generally
use ODBC and full SQL databases.

What is the best way to accomplish this? I can't seem to find
much information on accessing Access through DAO through IP
networking, so I don't know if its feasible or efficient. If I
need to use ODBC, will the database server be able to safely
handle transactions from both local DAO and remote ODBC?

Additionally, once a course of action is determined, I need to make
the server side changes as secure and easy to implement as possible,
as the server belongs to someone else and I'll never have any sort
of direct control over it.

Thank you for any assistance you can provide,
Greg


--
Greg Gursky
Bitspring
ggursky@bSiPtsA priMng.com (remove SPAM)
http://www.bSiPtsApriMng.com (remove SPAM)

[Larry Linson]

Re: Can Local DAO & remote IP ODBC coexist?

Accessing Jet databases via DAO over the Internet is not feasible. It is a
file-server database and all the actual work is done on the user's machine,
with lots of data passed across the network. Even high-speed Internet
connections are just too slow.

One alternative is to use a virtual private network scheme so that your
actual work is run on the server itself, or a remote access application like
pcAnywhere or ReachOut (but those really aren't all that suitable for high
volumes of data transferred from the remote location).

If you'll clarify with more detail about what you need to do, perhaps
someone can make a suggestion. Please clarify here in the newsgroup, not by
e-mail. Thanks.

Larry Linson
Microsoft Access MVP


"Greg Gursky" <ggursky@see.si gnature> wrote in message
news:3f60f763$0 $231$45beb828@n ewscene.com...[color=blue]
> Hello:
>
> I'm doing some planning on a potential project with which I need
> some help. The database at the focus of this question is a
> MS Access database that is currently accessed by DAO from a local
> webserver. I need to access the same database remotely through
> TCP/IP. There will be potentially many transactions coming from
> both the web server and remotely, so the solution needs to keep
> the data safe in such an environment. However, most likely, the
> remote data accesses will be read-only whereas the web server
> data accesses will always be both read and write.
>
> I have never worked with either DAO or Access before. I generally
> use ODBC and full SQL databases.
>
> What is the best way to accomplish this? I can't seem to find
> much information on accessing Access through DAO through IP
> networking, so I don't know if its feasible or efficient. If I
> need to use ODBC, will the database server be able to safely
> handle transactions from both local DAO and remote ODBC?
>
> Additionally, once a course of action is determined, I need to make
> the server side changes as secure and easy to implement as possible,
> as the server belongs to someone else and I'll never have any sort
> of direct control over it.
>
> Thank you for any assistance you can provide,
> Greg
>
>
> --
> Greg Gursky
> Bitspring
> ggursky@bSiPtsA priMng.com (remove SPAM)
> http://www.bSiPtsApriMng.com (remove SPAM)
>[/color]

[Greg Gursky]

Re: Can Local DAO &amp; remote IP ODBC coexist?

>If you'll clarify with more detail about what you need to do, perhaps[color=blue]
>someone can make a suggestion. Please clarify here in the newsgroup, not by
>e-mail. Thanks.[/color]

Thank you for the response, Larry. Unfortunately remote
control and VPN solutions are not an option. In my case I'm
writing a front end application that needs to read data from
the database across the internet. I assume that since Access
is a relational database, one can make table queries as with
SQL databases. This is, at least initially, all I need to do, basic
SELECT and SHOW queries and the like. If possible, I'd like to
use ODBC to query his database but it must not conflict with
the DAO transactions being performed constantly by his web
server.

Honestly, I think he's using Access in a situation where a
full blown SQL server is called for, but I have no control
over that. I need to work completely within the boundaries
of his current setup. I will never have access to his server
and doubt I could convince him to change anything
significantly.

As I mentioned, I've never used Access, so I might be
completely off base here. I'm basing this all on my experiences
with ODBC and SQL servers like MSSQL, MySQL and postgreSQL.
Any further help would be greatly appreciated.

Thank you,
Greg


--
Greg Gursky
Bitspring
ggursky@bSiPtsA priMng.com (remove SPAM)
http://www.bSiPtsApriMng.com (remove SPAM)

[John Mishefske]

Re: Can Local DAO &amp; remote IP ODBC coexist?

Greg Gursky wrote:[color=blue]
> Thank you for the response, Larry. Unfortunately remote
> control and VPN solutions are not an option. In my case I'm
> writing a front end application that needs to read data from
> the database across the internet. I assume that since Access
> is a relational database, one can make table queries as with
> SQL databases. This is, at least initially, all I need to do, basic
> SELECT and SHOW queries and the like. If possible, I'd like to
> use ODBC to query his database but it must not conflict with
> the DAO transactions being performed constantly by his web
> server.[/color]
[color=blue]
> Honestly, I think he's using Access in a situation where a
> full blown SQL server is called for, but I have no control
> over that. I need to work completely within the boundaries
> of his current setup. I will never have access to his server
> and doubt I could convince him to change anything
> significantly.[/color]

Access is a file-based database. You need a server-based database
system like MySQL, SQL Server or Oracle to do this.

You could write an application that sits on each side of the Internet
connection, one that runs locally and queries the database and that
sends the results back to the other side. Something like a web
interface. But if you're going over the Internet you'll probably
want the encryption and authentication that a VPN connection offers.

--
'-------------------------------
' John Mishefske
'-------------------------------

[Peter Miller]

Re: Can Local DAO &amp; remote IP ODBC coexist?


Larry,

On Thu, 11 Sep 2003 21:45:31 -0500, "Larry Linson"
<larry.linson@n tpcug.org> wrote in comp.databases. ms-access:
[color=blue]
>One alternative is to use a virtual private network scheme so that your
>actual work is run on the server itself,[/color]

A vpn does not provide server-side processing. It is simply a secure
network connection. An Access database run over a vpn is no solution
to the problem outlined. Perhaps you meant that he should use
terminal server (either w/a vpn or w/o one).

Peter Miller
_______________ _______________ _______________ _______________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900

[Larry Linson]

Re: Can Local DAO &amp; remote IP ODBC coexist?

The SQL statements in Access execute on the user's machine, not on the
machine containing the remote database. Access is a file-server database,
not a server database. Further, you can't use ODBC to connect an Access
front end to a Jet database (which is what you have, but often called
"Access" because it is the default database engine that comes with Access).
You might be able to do that using some other Office software for the
front-end... Excel, perhaps (though I have not done so).

As a file-server database, it uses the remote database folder just as it
would if it were on the local hard drive. It doesn't "bring the whole
database across the network" but it does do "every I/O that it would do to
the local hard drive".

I have a colleague who "turned Jet into a server database" for very specific
use, by coding a VB app accessing Jet on the server, and a VB application on
the clients that only sends messages back and forth to the VB app on the
server. But this was on a LAN, too, not on the Internet. The same approach
might work over the Internet, I suppose.

But the fact is that it is just not feasible to use an Access front end with
an Access/Jet database containing the data via the Internet. I'm sorry but
that's the case... Access just is not a server database like the MSSQL,
MySQL, and PostgreSQL you cite.

Larry Linson
Microsoft Access MVP

"Greg Gursky" <ggursky@see.si gnature> wrote in message
news:3f614d52$0 $68323$45beb828 @newscene.com.. .[color=blue][color=green]
> >If you'll clarify with more detail about what you need to do, perhaps
> >someone can make a suggestion. Please clarify here in the newsgroup, not[/color][/color]
by[color=blue][color=green]
> >e-mail. Thanks.[/color]
>
> Thank you for the response, Larry. Unfortunately remote
> control and VPN solutions are not an option. In my case I'm
> writing a front end application that needs to read data from
> the database across the internet. I assume that since Access
> is a relational database, one can make table queries as with
> SQL databases. This is, at least initially, all I need to do, basic
> SELECT and SHOW queries and the like. If possible, I'd like to
> use ODBC to query his database but it must not conflict with
> the DAO transactions being performed constantly by his web
> server.
>
> Honestly, I think he's using Access in a situation where a
> full blown SQL server is called for, but I have no control
> over that. I need to work completely within the boundaries
> of his current setup. I will never have access to his server
> and doubt I could convince him to change anything
> significantly.
>
> As I mentioned, I've never used Access, so I might be
> completely off base here. I'm basing this all on my experiences
> with ODBC and SQL servers like MSSQL, MySQL and postgreSQL.
> Any further help would be greatly appreciated.
>
> Thank you,
> Greg
>
>
> --
> Greg Gursky
> Bitspring
> ggursky@bSiPtsA priMng.com (remove SPAM)
> http://www.bSiPtsApriMng.com (remove SPAM)
>[/color]

[Larry Linson]

Re: Can Local DAO &amp; remote IP ODBC coexist?

Thanks for the catch, Peter. Yes, that is, indeed what I meant. I didn't
draw the distinction that I should have.


"Peter Miller" <pmiller@pksolu tions.com> wrote in message
news:8ip3mvcn5h 3ddr85qmk3k57ej lb7f9lf1e@4ax.c om...[color=blue]
>
> Larry,
>
> On Thu, 11 Sep 2003 21:45:31 -0500, "Larry Linson"
> <larry.linson@n tpcug.org> wrote in comp.databases. ms-access:
>[color=green]
> >One alternative is to use a virtual private network scheme so that your
> >actual work is run on the server itself,[/color]
>
> A vpn does not provide server-side processing. It is simply a secure
> network connection. An Access database run over a vpn is no solution
> to the problem outlined. Perhaps you meant that he should use
> terminal server (either w/a vpn or w/o one).
>
> Peter Miller
> _______________ _______________ _______________ _______________
> PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
> Free quotes, Guaranteed lowest prices and best results
> www.pksolutions.com 1.800.987.7716 1.619.839.3900[/color]

[Kyle Bitner]

Re: Can Local DAO &amp; remote IP ODBC coexist?


"Greg Gursky" <ggursky@see.si gnature> wrote in message
news:3f60f763$0 $231$45beb828@n ewscene.com...[color=blue]
> Hello:
>
> I'm doing some planning on a potential project with which I need
> some help. The database at the focus of this question is a
> MS Access database that is currently accessed by DAO from a local
> webserver. I need to access the same database remotely through
> TCP/IP. There will be potentially many transactions coming from
> both the web server and remotely, so the solution needs to keep
> the data safe in such an environment. However, most likely, the
> remote data accesses will be read-only whereas the web server
> data accesses will always be both read and write.
>
> I have never worked with either DAO or Access before. I generally
> use ODBC and full SQL databases.
>
> What is the best way to accomplish this? I can't seem to find
> much information on accessing Access through DAO through IP
> networking, so I don't know if its feasible or efficient. If I
> need to use ODBC, will the database server be able to safely
> handle transactions from both local DAO and remote ODBC?
>
> Additionally, once a course of action is determined, I need to make
> the server side changes as secure and easy to implement as possible,
> as the server belongs to someone else and I'll never have any sort
> of direct control over it.
>
> Thank you for any assistance you can provide,
> Greg
>
>
> --
> Greg Gursky
> Bitspring
> ggursky@bSiPtsA priMng.com (remove SPAM)
> http://www.bSiPtsApriMng.com (remove SPAM)[/color]



Go to: http://www.mvps.org/access/
Best Regards
Kyle Bitner, MVP


[color=blue]
>[/color]

[Greg Gursky]

Re: Can Local DAO &amp; remote IP ODBC coexist?

Ah, very good explanation. Thanks to all of you, that really clears
things up for me. I'll either have to write an app to run local to the
server to serve the data to the clients, or, convince him to write
some CGIs to serve up the information off the web server. Luckily,
none of the data requires any security whatsoever so that
simplifies things a good bit.

Thanks again to all of you for clearing this up for me.

Sincerely,
Greg


--
Greg Gursky
Bitspring
ggursky@bSiPtsA priMng.com (remove SPAM)
http://www.bSiPtsApriMng.com (remove SPAM)

[Joe Fallon]

Re: Can Local DAO &amp; remote IP ODBC coexist?

Just to add one last note to a fine discussion:

MSDE is free.
It *is* SQL Server.
It can be connected to over the Internet.
It has 2 limitations:
2GB space
5 concurrent processes before a governor kicks in to slow it down.
(5 concurrent processes could still support dozens of simultaneous users.)

--
Joe Fallon
Access MVP



"Greg Gursky" <ggursky@see.si gnature> wrote in message
news:3f60f763$0 $231$45beb828@n ewscene.com...[color=blue]
> Hello:
>
> I'm doing some planning on a potential project with which I need
> some help. The database at the focus of this question is a
> MS Access database that is currently accessed by DAO from a local
> webserver. I need to access the same database remotely through
> TCP/IP. There will be potentially many transactions coming from
> both the web server and remotely, so the solution needs to keep
> the data safe in such an environment. However, most likely, the
> remote data accesses will be read-only whereas the web server
> data accesses will always be both read and write.
>
> I have never worked with either DAO or Access before. I generally
> use ODBC and full SQL databases.
>
> What is the best way to accomplish this? I can't seem to find
> much information on accessing Access through DAO through IP
> networking, so I don't know if its feasible or efficient. If I
> need to use ODBC, will the database server be able to safely
> handle transactions from both local DAO and remote ODBC?
>
> Additionally, once a course of action is determined, I need to make
> the server side changes as secure and easy to implement as possible,
> as the server belongs to someone else and I'll never have any sort
> of direct control over it.
>
> Thank you for any assistance you can provide,
> Greg
>
>
> --
> Greg Gursky
> Bitspring
> ggursky@bSiPtsA priMng.com (remove SPAM)
> http://www.bSiPtsApriMng.com (remove SPAM)
>[/color]