SQL Server / Access as FE (AXP/SQL2000)

Topic

New Reply

I am looking for the “right” solution for an interim front end to a SQL database. I converted an Access BE data file to SQL and it is running. I have the old Access front end that I would like to use but it is very slow to interact with the tables. I am not sure if something is set wrong or what. I have made a new Access Data Project today and now I can access the records with the speed I was expecting. Now I have to ask the gurus out there, what would be the best interim solution as a front end file for this database? We have 25+ concurrent users hitting this thing throughout the day so it has to be fast. After some development, we will use ASP pages to access the DB. Any ideas. Oh yeah, I forgot to mention that I have set up access using ODBC right now and I am not sure if this is best either.

Reply | Quote

Replies

Using an ADP will always be faster than using ODBC connections as you are running in native SQL mode. However, unless you have bunches of users pounding on it with data entry, you can usually get acceptable performance with ODBC. There are some things to consider however. Make sure all of your tables get pushed to SQL. If you start using some tables in SQL and some in Jet, things get very slow quickly. Also make sure that things got indexed properly in SQL, and where performance is key, consider making a view and connecting to the view via ODBC rather than joining to individual tables and then creating an Access query. Another thing to do, especially if you are running on a 10Mbit LAN, is to deploy the FE database to the individual workstations.

All that aside, we have a client who runs about 60 users on a 100Mbit LAN with a pretty complex system of about 150 tables and 800 queries running ODBC, and their typical response time to pull up a specific record with 15 to 20 tables behind the form is under 1 second. We are gradually converting from ODBC to ADP, but the interim solution has proven to be pretty stable and give acceptable performance. Hope this is useful.

Reply | Quote

Thanks Wendell,
The connection using the ADP is pretty fast. I have some learning to do about indexing in the tables. What is the best method of indexing? Also, I have to recreate all of the queries as they are not compatible I guess. I am able to open the tables, they are all resident in SQL by the way. I have imported everything that the ADP allows like forms, reports, etc. These items came from the original FE file.

The existing system had been using the FE on the local PC while the BE file was on the network. However this was a bit slow still (using 100mb). We then implemented a Citrix system where the entire app runs on the server. That is really fast in comparison as the data is not running back and forth across the network. With the addition of many more users I thought it prudent to convert to something scalable like SQL. I am tossing between using something like VB or an ADP to run the front end. In the end we will write ASP pages to run the database. I need a quick interim solution that will take advantage of SQL and not cost too much development time. Am I making any sense here? What are your suggestions? Also, if anybody else has anything to add, I would appreciate it.

Thanks,
Mark

Reply | Quote

Go with the ADP for now. It’s a window directly into SQL Server. With VB, you have to fumble around in the dark.

Reply | Quote

HI Charlotte,
Understood. Thanks!

Mark

Reply | Quote

I haven’t played with the conversion tools in XP, but when we tried it with 2000, it left lots of holes, and all but the most mundane of queries had to be recreated by hand as either SQL views or as Stored Procedures. That’s a good deal of work! I frankly think you’d be ahead to spend some time in performance tuning with ODBC connections. You get most of the benefits of SQL in that if your queries are well behaved, the ODBC driver converst the SQL into something SQL Server understands, sends it across the network, and SQL sends back just the data you want. A couple of questions:

How many users do you anticipate growing to?
How much of the usage is data entry as opposed to display and analysis?
Are your servers on a dedicated 100mbit LAN switch, or is it just a simple 100mbit hub?

As to doing the work with web pages, a couple of cautions. You will find it takes three times as long to write a comparable web page form using ASP as it does to do a VB form, which typically takes three or more times as long as doing an Access form. In addition, there are a number of limits to what you can do on a web page compared to what Access can do. Data validation before submiting to the server is one thing. Combo boxes don’t work nearly as well. There isn’t anything comparable to a subform. I know lots of people who are convinced it’s the best thing since sliced bread, but I’m yet to be convinced.

Reply | Quote

Hi Wendell,
We will have as many as 500+ users, Data Entry maybe a tenth of that, and yes they are on 100MB switches and not hubs.

When you say it takes three times as long to write, are you saying the initial creation of the form (design) or when it is being used by the end user? What ever the solution be it ASP, java, etc., it will be web based as this is the direction the company is going.

Thanks,
Mark

Reply | Quote

As to the three times, it can be both. Maybe .NET will change things – I’ve heard noises about it reducing the development overhead to about 1.5 times VB. The real challenge is to do the sort of things we do in Access routinely when you don’t have an event driven paradigm. As a result you often have to create several to many pages to do what you do in one form in Access. We are currently doing a web version of an address update form we created (actually, it’s a main form and two subforms), and it looks like it’s going to somewhere between 20 and 30 ASP pages to duplicate the functions. As a result, it will take the web user considerably longer than an Access user to make the changes. In this situation it will be acceptable, as the user is removing data entry tasks from an Access user, but some will try it and decide it’s too big of a pain, and send an email or fax saying please change my address to ….. I understand the corporate direction – we are seeing it with lots of clients. Good luck – hopefully these comments will give you some idea of the gottchas.

Reply | Quote

Does anyone know where I can find a reference of what the differences between DAO and ADO are? I have recently created and Access ADP with a connection to the SQL server. I can see all the data by opening the tables. However, when I import my forms they cease to function and I think the problem is that the DB is now running ADO instead of DAO. For example: When I try to “dim dbsperson as database” it doesn’t recognize “database”.

Thanks,
Mark Santos

Reply | Quote

Hi Mark,.

Be sure to use Explicit variable declarations:
Dim dbsperson as DAO.Database ‘OR
Dim dbsperson as ADO.Database

As for the reference of ADO vs. DAO, it’s included in the Office VBA Programming guide (it comes with the Developer Edition of Office).

You can also view this on the web at: Choosing ADO or DAO for working with Access Databases

HTH

Reply | Quote

Mark,

There’s no database object in ADO. Recordsets, Fields, Parameters, Properties, Errors, etc., but no Databases.

Reply | Quote

DOH!

As I clicked the “Post” button I knew something didn’t sound quite right. That’s what I get for not double-checking, especially when I’m somewhat green with ADO.

My apologies

Reply | Quote

Don’t worry about it. I still have ADO lapses. It is very different from working with DAO, despite the similarities in object names. I must have 6 books on ADO and still run into things that frustrate me, like trying to remember whether a particular object should be declared as an object or an AccessObject.

Reply | Quote

Along with explicitly declaring either ADO or DAO, make sure that you have the DAO library referenced in the project.

From the VBA Window, goto Tools –> References and check (select) the Microsoft DAO 2.6 reference.

Cheers

Jayden

Reply | Quote