can you put a backend on the net?

[David]

Hi There

Totally new to Access, and am required to make a db to record and
display stock levels of small number of items

This is an distance ed assignment, and I will need to submit it to the
school for evaluation and marking.

Using Access 97, I was thinking that I could (sort of) emulate it
running on a LAN by splitting the app and locating the file portion (the
backend) on a web site.

The idea is that this sort of application is only really useful if the
sales staff can access the records to see the current stock level of the
item in question, and enter the number sold so that the instock quantity
is updated immediatly.

Also, the store/receiving dock etc can enter the quantity received as
they are put into stock.

So, a number of people need to be able to enter data, read data, and
that data should be always up to date (yeh I know in real life that
isn't always the reality, just the theory)

As I do not know what systems they will have at the college, I thought
using the net would be a failsafe way of demonstrating how I would
provide access for all staff to the database.

Speed will not be an issue, as I think the college will have broadband,
and as long as it doesn't ages should show how the db would would work
on a LAN/network

I was planning to have two files - one for the items, and one for each
transaction - a one to many relationship. Then I presume Access would
be able to add and subtract the figures for the transactions so
calculating the stock level for each item.

I have written data bases in the past using Omnis 5, and this is how I
would approach the problem in that application - but am not sure
whether Access is as sophisticated

I am using Access 97 as I bought a copy at a garage sale, but wouldn't
wish to buy a later copy just for one assignment.

All advice and ideas greatly appreciated

David

 

[Stefan Hoffmann]

hi David,

Using Access 97, I was thinking that I could (sort of) emulate it
running on a LAN by splitting the app and locating the file portion (the
backend) on a web site.

After splitting an Access application, you have still a file-based
database system. So putting the back-end into the web is not possible as
long as your target system cannot provide file system services.

In this case you have to use a database server as back-end. As most web
sites are imho running on xamp so you have to port your tables to a
server like MySQL.

http://www.ucl.ac.uk/is/mysql/access/



mfG
--> stefan <--

 

[Albert D. Kallal]

message

Speed will not be an issue, as I think the college will have broadband,
and as long as it doesn't ages should show how the db would would work
on a LAN/network

On a LAN, access works very well. I deployed applications with 65 tables,
160 forms, and 30,000+ lines of vba code. With 5 users on the office
network, it runs very well indeed.

On the other hand Broadband is VERY VERY differ and is 100 times slower then
your cheap-o local office "LAN" network.

So on a broadband, speed is MOST certainly an issue.

We are not talking about 20% here or even 50%...but a WHOPPING 100 TIMES
slower.

I explain this issue of speed here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html


The above article assume you realize about a "split" application. In a
sense, the following article is a pre-requisite for the above. You can read
about splitting here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Paul Shapiro]

I agree completely with Albert. Performance is so bad as to be unusable over
a WAN, but even worse the probability of corrupting the database goes up
dramatically. Any interruption of the network connection or dropped network
packet can corrupt the database. Presumably you'd be working over a VPN so
the data was not exposed, and in that case performace drops even more.
Besides the bandwidth issues, network latency reduces performance. Latency
on a LAN is something like 1 milli-second or less. On the Internet, it's
typically 15-20 milli-seconds for another nearby site (like in the same
city), and 100 milliseconds across the USA.

Bottom line is that Access is not meant to be used over a WAN. This is the
kind of application that a true database server, like SQL Server or Oracle,
can handle very well, but a file-server based database like Access does not.
With SQL Server/Oracle your application sends a sql command and the
resulting data is retrieved by the server and just the requested data is
returned to your application. Only the server reads and writes the actual
data file(s). With Access, each user's computer has to directly read and
write the data file.

Try it with a test database, where you don't care what happens to it. I've
done that test as an example for a database class I used to teach, and even
a small database was unusable. I seem to remember 5 minutes to open a modest
table with 1000 rows.

 

[david]

This is NOT a situation that Oracle or SQL Server handle
'well'. This is a situation that Oracle or SQL Server
handle badly, and Access/Jet/Windows handles worse.

I have had clients using SQL Server, Oracle, and Sybase over
a WAN. This is a common, normal configuration for interstate
and state-wide organisations like health departments and
insurance companies. Hospitals connect using LAN-speed fibre
Insurance companies use TS/Citrix. But small independent
clients and unsupported applications have to connect using WAN
speed broadband. None of these WAN users would use a system
like that if they had any power to change it. Clients with sufficient
internal power lie and cheat to get a local server to avoid
connecting to SQL Server or Oracle over the WAN.


By the way, when using Access/Jet, the Windows database
API requests records from the server, and the server returns
records to the client. That part of the system not inherently less
efficient than any other client-server database query language.

(david)

 

[Paul Shapiro]

With any WAN-accessed database, performance will be poor if you use typical
Access development techniques. A form with a recordset like "Select * From
MyTable" will have to return all records from MyTable, and if it's a large
table that will be slow. But if you use appropriate techniques to minimize
the number of round-trips to the database and the quantity of data being
transferred, performance can be excellent with any client-server type
database. I've developed and supported applications with hundreds of users
on multiple continents, and they ran fine. In the old days, a well-developed
application ran adequately on a dialup connection at no more than
56kbits/sec. But not with a file-server type database like Access. Access
cannot request records from the file server. It can request byte ranges, but
that's still far more data and far more roundtrips than needed for a
client-server database. The local Access application has to retrieve all of
the database's metadata in order to know which bytes represent a particular
record. If it's using an index for data retrieval, the index has to come
across the WAN before the data can be retrieved. Etc. Since multiple users
are involved, Access has to keep checking locking and metadata changes. The
increased corruption risk with Access over a WAN cannot be avoided since
each user is directly writing into the data file.

The only appropriate way to use an Access database over a WAN is with a
Terminal Server. That results in all the data access staying on the LAN,
with just keystrokes, mouse movements and screens being transferred over the
WAN.

 

[David]

hi David,

After splitting an Access application, you have still a file-based
database system. So putting the back-end into the web is not possible as
long as your target system cannot provide file system services.

In this case you have to use a database server as back-end. As most web
sites are imho running on xamp so you have to port your tables to a
server like MySQL.

http://www.ucl.ac.uk/is/mysql/access/



mfG
--> stefan <--

Thanks, I appreciate your advice but I do not want to have to learn that
SQL stuff as well as Access just for one assignment.

I was thinking I could just upload the file to my website and have the
database read it - butif wishes were horses etc.

David

 

[David]

Thanks guys for the info - very much appreciated but it looks like a bad
idea so I will just forget that approach and design it as a one user DB

You can appreciate that as a Mac user I do not want to waste more time
than necessary learning an application that I will never use again.

David

 

[Danny J. Lesandrini]

David:

I saw this thread and hesistated to answer, but I did write an article on this,
and I do have a few databases that expose Access tables to users for adding
and editing data. It can certainly be done, but there are some limitations.

First, here's an article with a download that includes a form you can import
into any Access mdb and build a web page from a table. The results aren't
pretty, but if you know any HTML you can edit it to make it better.

http://www.databasejournal.com/feat.../Convert-Access-Tables-Into-ASP-Web-Pages.htm

Second, there are caveats. This approach works pretty well for apps where
there are a few distributed users who all need to get to the data but are not
on the same LAN. If there were hundreds of users, you'd end up with data
integrity issues when 2 users edit the same record at the same time. (This web
paradigm doesn't include any concept of locking the record for editing.)

Still, it's fun and very possible. For example, here are a few sites I run on
Access databases:

What kind of thinker are you, Survey
http://www.amazecreations.com/thinker/

Office Directory
http://www.amazecreations.com/officedirectory/

Nutrition Literacy Toolkit
http://www.amazecreations.com/Nltk/

 

[David W. Fenton]

it looks like a bad
idea so I will just forget that approach and design it as a one
user DB

You can appreciate that as a Mac user I do not want to waste more
time than necessary learning an application that I will never use
again.

Have you considered Filemaker? It's got many of the capabilities of
Access, but does allow hosting the Filemaker database on a web
server (as long as the web server is running the appropriate FM
extensions).

 

[david]

I wasn't just talking about Access applications: I was talking
about Oracle Forms and PowerBuilder. These run faster now
on modern equipment than they used to, but so does everything else.

For some of my clients, this is the only game in town. They have
no choice. But that doesn't mean they like it. Using a main-frame
type database application over a WAN sucks even worse than
it used to.

Even though the WAN is 5 times faster than the old tie line,
it seems worse, because in the old days clients compared
computer applications to paper applications.

This doesn't matter when you do one FX transaction per day,
but it's a painful fact of life when you have to do real-time
interaction.

Many data entry and retrieval users of these applications don't
see these problems because they are not using WAN speed
ADSL/SQL.

They use TS/Citrix or Fibre, because Oracle/SQL Server
/Postgres/Sybase don't really work very well over ADSL.


(david)


PS: By the way, as I alluded to before, the slowness and
unsuitability of Access isn't in the data retrieval time. That's
common to all client-server data applications. Why waste
time pretending that's what matters? Why mislead people
about the nature of the relationship when you know that just
confuses them? It's good to see that you mention some of
the factors that do matter.