Browser-based access to my Access DB

[PatK]

Hi, all!

I am looking for some guidance. I have an Access Database, that has some
very large tables. I have users world-wide, who need access to the database.
To date, I have been simply creating a "copy" of the DB that they download
once a month, to do reporting from data stored therein. However, this is
becoming unweildy, and I would like to change this to a web-accessed
database. I would like to be able to have users read, certainly, and in some
cases, update data in the tables.

My question: What is the best approach to do this using MS Access?

I have heard that Sharepoint can connect to Access 2007 DBs (I can go either
way, 2003, or 2007). I have also read something about ADP, but this does not
sound like what I am looking for...I cannot say. I also understand ASP pages
could be written that access the database, as well. There are probably other
ways.

I am up for "learning" whatever it takes to do this. I currently have been
using VBA and creating the needed scripts/code within my access db, to do the
work I need done. But now, it is time to put this on the web (intranet).

Suggestions on the least painful approach to accomplish this?

Thanks!

Patk

 

[Jeff Conrad [MSFT]]

Just out of curiosity, what does "very large tables" mean in your context?
How many records?

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Arvin Meyer [MVP]]

I don't know how many users you have, but have you considered using a
Terminal Server over a VPN? It is considerably more secure than an Internet
connection, but similar to an Intranet over a VPN. The biggest advantage is
that you will not need to rewrite anything for a massive speed gain. I would
caution against wasting much time with this if you have more than a dozen or
so concurrent users. Tables that big are slower in Access/Jet than in
SQL-Server, unless you are bound to them.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[PatK]

I have, in the past, used just that solution (TS) for an access database, and
it worked fine. In this case, however, I am trying not to expose "all" the
data to the users, and am trying to present just what I want them to see, via
the web (intranet). We have a darn good internal network, world-wide, so
that is not really an issue. The key is that if the web-browser centric
approach works, it will provide some other opportunities for us in the way of
"dashboards" etc that managers may use (that have no access skills whatever,
nor wish to).

But yeah...I had thought of that. ALso, initially, it will be running on a
small PC running XP pro, as part of a proof of concept, and I am "pretty"
certain I cannot run TS to a standalone, non-server PC (but I could be
mistaken). Even so, it is not a viable approach of this DB.

But I truly appreciate the thought/response!!!

patk

 

[Arvin Meyer [MVP]]

You can put the data back-end on another machine so that only the front-end
resides on the TS machine. Then hide the database container, and use the
AllowByPassKey to keep it that way if necessary. Now the front-end is only
accessible by a menu driven method. While Terminal Server requires a server
(and a beefy one at that) there are other RDP solutions that will work:

http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp.aspx

If you decide to go the web route, I have successfully used an ASP
front-end, and one could probably use an ASP.NET as well. I was never as
satisfied with using a web server since it isn't as reliable as a terminal
server and more prone to attack. YMMV.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[PatK]

Thanks Arvin...definitely a consideration. For the first several months, my
"proof of concept will be running on a notebook (ACK!) and typically will not
have more than 2 simultaneous users, but that whole "around the world user
base" issue is still a concern. I will likely never graduate this to a
server big enough to handle a TS server setup (although that would be
grand!!!).

Attacks are not really an issue, as this is all internal company users on
our intranet, and the system is set up to be backed up daily (data does not
change, frequently....a few times a month). So I think I am ok security
wise. Also, the other issue is the user base, high level execs who don't
want to know anything about Access.

You mentioned you had done this with ASP, which is what I am currently
dabbling in, but I have never done it before. I am having some sort of
problem with the scripting, possibly the connect string. Do you have any
code snippet that would show how to create a connect string to an Access
2007/ACE database, and then open a record set? I am not a vbscriptor, so am
stuck on this (getting a very nebulous error that tells me nothing). WOuld
you, or anyone, have a working script to open an Access 2007 DB, encoded in
an ASP page? Or is there a better forum for this question?

Patk

 

[Arvin Meyer [MVP]]

Here is the ASP include file that I used to connect to the database DSN
which was named prospect:

<%
dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
'oConn.connectionString=
oConn.open "DSN=prospect"
'oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=c:\inetpub\database\DataTables.mdb;" & _
' "User Id=admin;" & _
' "Password="

%>

This include page was named incConn.asp and a typical page that used it was
the login. Here is part of the login:

<!--#include virtual="/prospect/aspinclude/incConn.asp" -->
<%
Dim rstSalesRep
Dim rstSubs

'Session ("SalesRepID") = ""

strSQL = "SELECT * FROM tblSalesRep WHERE SalesRepCurrent = True"
Set rstSalesRep = oConn.Execute(strSQL)

%>

I am not a great asp coder, so you should direct your questions to an asp
newsgroup.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[PatK]

Thanks! I actually did post it to an ASP newsgroup yesterday, and got some
helps. The resolution was was pretty convoluted, as there was also a
permission issue (like, having to set my access db AND my windows\temp folder
to allow read/write permissions to be set to "EVERYONE." Also, of course,
the 2007 access DB had to have a different connection string.

I am attaching some code, here, lest anyone else have to spend days digging
into this, as I did, for both types of connections strings:

<%
Dim oConn
Dim filePath
Dim strConnect
'--------------------------------------------------------------------
' Set the file path to the DB to be opened
'--------------------------------------------------------------------

filePath = Server.MapPath("_private/Assets.accdb")'

'--------------------------------------------------------------------
' Use this connect string if the database is Access 2007
'--------------------------------------------------------------------

strConnect = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=" & filepath

'--------------------------------------------------------------------
' Use this code if the database is Access 2003
'--------------------------------------------------------------------

'strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath

'--------------------------------------------------------------------
' Open database using defined connection string
'--------------------------------------------------------------------

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.mode = 3 ' adModeReadWrite
oConn.Open strConnect
Response.Write "DB File Path: " & filepath & "<BR>"
%>

The above code I am saving in an ASP file that I will use as an INCLUDE file
in scripts that have to access the database. I have database of both 2003
and 2007 flavor, so I am doing this to help me remember (the pain!). Hope
this helps someone else, particularly the 2007 connection string. Note: The
assets.accdb is, obviously, 2007, in this case.

Patk

 

[Arvin Meyer [MVP]]

I suggest that you maintain the database as an MDB using the file format of
Access 2000 (which you can do in any version), That way all recent versions
will be able to read and write to it. Also, asp permissions only require
that I_USR (the default asp user) have read/write permissions to the
database folder, although you will want at least admin permissions as well.
Try it that way first. That way, you have the data somewhat protected in
case someone hacks into the server itself.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com