Newbie (Asking for too much?)

  • Thread starter Thread starter Armando Vargas
  • Start date Start date
A

Armando Vargas

Hello!!

I've been working with MsAccess since '97 and it is great for the work that
I do, but I have found some limitations that I might just end if I change to
SQL + ADP.

I need to work with a database over the Internet. (Maybe over a VPN).
As many as 20 users (maybe more) will be using the application at same time.
The solution that I use right now is a Terminal Server instaled, but the
licenses are a bit expensive.

I already change the tables to SQL using the Wizard, and Bind it to the
forms, but it seem to be as slow as linking the tables.

Could anybody help me, please, telling me, or sending me an example of how
to use an SQL database and an ADP, using code, but with the purpuse of using
it over internt. (Have read that is a good way).

Thank you very much for your time and answers.

A.V.
 
My thoughts on remote Access ADP

You can get a runtime installer for Access with MDSN universal. I would call MSDN subscriptions and ask them about the Access Packager Wizard that comes with the Microsoft Access Developer extensions. This way you would just lay out cash for the MSDN subscription and get all the other developments tools and product licenses. You can then freely distribute the runtime client. This way you could use your terminal server without all those fancy office terminal licensing issues.

I think terminal server might be your best option. My experience with communicating with SQL databases over the web is that it is slow. You could open up ports and allow assess via an IP. I don’t recommend it. Web applications normally coexist at a hosting facility where they have a high speed connection to the DB.

You have to clean up your code to handle all errors. The runtime addition won’t report them to the user. It doesn’t support those data access pages either, thank god.

Access tends to make forms that over use data. It does things like requery sub forms when the main form is edited. The server and client filters are implemented in inefficient ways as well. Often the filter is not immediately applied. If you force a record source the form loads up with the old data first. Locally theses things are a minor wait, over the web users will be waiting and confused about what is going on. You can get around this stuff by limiting what opens on pages, making good use of stored procedures and ditching filters.

You would also have the problem of remote upgrades and administration of the clients.

Opening up your SQL server makes for security concerns as well. No joke if you monitor ports you would be surprised how many people are tying to get in. If you do I think that you just need to use a system password (sa). Then specify the connection as an IP address. I think this is a bad idea. A VPN would allow you to have the security, by letting the users log on to a domain. It won’t help the speed issue.

If you do open up your sql server over the web, I would use stored procedures for everything. Even if you don’t open up your server, this is still a good idea. They transmit less text across the network. They allow you to centralize you queries in one place. I like this because it allows you to verify that field name changes haven’t broken you queries. I delete and recreate the queries to perform a sort of query level unit test.

If you know the tricks, Access and ADPs work great with stored procedures

1. Sub reports pick up a field name that is the same as the Stored Procedures Parameter name, off the parent form. Most often the parent child parameters for a sub report don’t work with stored procedures.
 
Terminal server rocks for this sort of thing. The licenses are cheaper than
the development cost of re-creating your MDB into an ADP. I speak from
experience. Unless you have dozens of users, I'd stick with terminal
server/remote desktop

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Thanks Drew, I already use ASP but for some on line querys, and think its
not te solution for the application that I use. But it´s great.

I use the asprunner for making the asp pages, its a good aplication.

Armando.
 
Thanks Kevin.

It works the Terminal server, but I think there´s ought to be a better way,
(Not that is wrong) but, a way that We as developers keep the money spent by
the user, on those licenses, insted of keeping it for our candies.

But, Theres is the problem again, the speed, and security.

Thanks and I'll inform everybody when I find if it is really worth the
chance. My app, is like 120 Forms and 150 Reports.
 
My app, is like 120 Forms and 150 Reports.<<

And the queries underlying the forms and the fact that some things in MDB do
not convert to SQL Server....like IIF must be converted to CASE...WHEN, etc.

Good luck :-)


--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Hate the Browser

When I was doing web programming I learned to hate the browser. Without plug-ins browsers have no support for reporting functions, like margins and page breaks. They have lousy controls, like a list box instead of combo box. Browsers are static as well, unless you use Java Script(not to be confused with JAVA). This wacky client side processing rarely works the same on all browsers and versions. Programming in this static framework is oppressive.

In Java these problems force you to adopt a framework, we used Apache Foundation’s Jakarta Struts. Visual studio provides a framework as well. The old ASP/JSP and data access pages didn’t provide a framework. For example, if you had a user fill out a form and one of the fields didn’t validate. You had to pass all the field information back to the form write new code to put it back in and tell the use witch filed wasn’t right. Within a framework the validation issue would be configurable.

If you move to SQL server/ADP and migrate to stored procedures, it would be a good first step in moving to ASP.net. Once you have the bulk of you business logic up on the SQL server. It will be essayer to migrate to ASP, Java or some other web programming language.

I don’t think you want to use any of the old VB6 ASP. I used it back in when it was new. It can’t really make the kind of user interfaces my current users want. I started using Access after all my heavy eCommerce Java experience. As an x programmer it was just the step down I was looking for. I used ASP MS SQL server and ADP to prototype our systems.

The new ASP in visual studio is much richer in terms of user experience. It has things like a combo box, instead of the basic html list box. However, the framework is much loser then Access. So, I guess I would move slowly in that direction if you have 150 forms/ reports. Microsoft is still tiring to make Visual Studio as efficient at producing a usable application as access, while incorporating web browsers.

If you have 20 users ASP is a viable solution. If you have any kind of load on the old VB6 ASPs it will use up all the CPU resources quickly. The new ASP is better. I have heard that under a truly heavy load you have to give up those Visual Studio components and go back to HTML components or the server gets overloaded. I wouldn’t write an eCommerce server with them.
 
Back
Top