Net transfer rates

[benski]

This is probably a silly question, but I am just beginning inet programming.

Could you get acceptable performance from a web based database (MS Access). If so what protocols/transfer methods could I use to access it. I would want several users to be able to edit in realtime. I have got my data size down to the smallest and fastest possible using lookup tables/indexes. The database is only about 20Mb total.

[TheLeeMan]

Are you talking about multiple users opening the database on their computer in access or using ASP scripts on the web to create,edit, and delete records?


------------------
TheLeeMan
EMail: [email protected]

[benski]

I don't know.

I am quit familiar with Access, but a novice on anything to do with the internet.
Active Server Pages? I know what it stands for, but little else.

I've been considering using the CPU power of my server to do the actual querying and just return recordsets/info to the client apps. I hoping this would mean a big improvement for users with lower spec machines.

Really my main interest here is finding out the fastest, most efficient method of many users accessing the database via the web. All ideas welcome! Thanks.

[Clunietp]

Do you mean having the data on a server and clients access that data via TCP/IP or actual web pages displaying your data? If you want to base your Access data off of the web (with real HTML pages) and have server processing, sounds like you need ASP. If you know VB Script (stripped down version of vb) and HTML, ASP is a combination of the two. You could use ASP to query your access data and have processing done on the server.

SQL server is recommended for scalability, but only if you expect more than 5-10 concurrent connections to the access DB.

HTH

Tom

[benski]

I have a front end application and just want users to be able to use the database as though it was on their own machine/network.

I need to know which protocol/methods will give viable speed/security/data integrity.

Thanks.

[rod]

Tell me if this is your situation (or sort of):

You have a single machine File Server in which your MS Access Database resides. This database is being shared to your workstations or nodes in which a number of users are adding/updating/deleting (DML operation) records concurrently.

Now your problem is, your MS Access is 20MB in size or more, meaning if a user wants to access a single information in your database using your current setup, he'll have to deal with the whole 20MB file just to get one. Worst... your workstations are pre-historic and aging piece of junk, and therefore cannot support or will have trouble accessing large files such as this.

If this is the case... then welcome to the real world, pal!!!


The common solution to your problem is to change your MS Access database to a truly RDBMS dataserver (MS SQL Server, Oracle...).
If the situation doesnt permit you to, then there are some work arounds that you could try.

This might not be the best solution... but this surely works.

1. You'll have to create a SERVER application (using Visual Basic) to manage the MS Access Database. This application must reside in your File Server together with the database.

2. You'll have to create a CLIENT application (using Visual Basic) to access the database. This application must reside in your Workstations.

3. Use the Microsoft Winsock Control on both application to issue some DML commands. In this way, your Client applications are not directly accessing the database.

example:
Client-Application: in your Winsock1.Senddata method, send "Select * from MyTable"

Server-Application: capture data from Winsock1.Dataarrival


The problem with this setup is you'll have to set everything up meaning tons of codes... well goodluck!

Cheers,

Rod

[benski]

Nicely put Rod, especially the bit about age-ing pieces of junk!
Well - I am quite happy to ditch Access altogether if you reckon it's the best path. I am currently wading through the VB Data Access Guide.

I want a solution where the computing (SQL calculations/retrieval) can be done by the server, freeing up time/resources on the client side.

I presume this is what SQL Server does best? I've never used SQL Server, but if that is what it does then that's what I want.

I was considering writing the server/client software in the manner you described (with Winsock) and minimising traffic by using a command index (eg client software sends a query number and some data to the server) but then I wondered whether it would be possible to make recordsets from the returned data or not. My guess is that you couldn't, which makes the client's job harder.

The client software will quite often be using derived data - eg total number of X for a given Y, which can't be updated directly, so using data aware controls is not a must have.

Thanks for any advice!

[Clunietp]

Don't build your own DBMS, use SQL Server! You will save a ton of time setting up and learning SQL than trying to create your own protocols, which are a pain in themselves. SQL server is not extremely difficult, plus you get all the benefits of a true RDBMS like backups, stored procedures, security, etc. Processing is done at the server. You will be more valuable because of your new knowledge of SQL server. I don't recommend Oracle, it is more difficult to learn and manage (ever wonder why Oracle DBA's make so much?)

The moral of this story: Use SQL Server.

HTH

Tom

------------------
[email protected]
[qualifications and/or certifications here]

[benski]

I know this topic's pretty old now, but if anyone's following the story; I now think that MTS (Microsoft Transaction Server) is the way to go.

It can be daunting at first,but I'm getting exactly the result I want. The client apps just set the url of the server and MTS does the rest. The clients create mts objects (start dlls) on the server when required. You don't have to muck about with windows socket commands or worry about the state of transactions- its great!