Moving to SQL Server (1 Viewer)

[stormin_norm]

I need your opinion/comments -

We are looking to consolidate four copies of the same A2K db into one sql server db. Naturally the access db could use more normalization. Heck, two main queries are at the maximum number of fields allowed in access!
I tried importing into SQL server and the main table already had import problems. I can write some import/export routine for the problem tables or cleanse the tables in access then import.

After the data is imported, I plan on doing the following three steps:

1) reuse existing: create views/sp's on sql server so the power users can use their existing .mdb or new .adp (import forms+reports from .mdb). In the future the power users could use crystal reports or SQLreporting services after they have been trained.

2) create new queries, views, sp's for better reporting and thin client

3) develop a thin client for the data entry and read-only users. Most likely either asp.net or DAP. Environment is Win2000/2003 with IIS.

My main question is: should I try to re-use the existing .mdb to create the 'full' version or make it an asp.net c#.net or DAP?

 

[KenHigg]

Norm, The options you listed - .mdb, asp, c#, DAP all have strong points and weaknesses. Here's the way I see that that they break down:

1. .mdb - Doing the main tables in sql and using a .mdb as a front end is fine where all the users are on the local network. You can even deploy accross a wan with out much problem as long as you deploy the front end on the local machine and pull only the sql data across the network.

2. asp - Here your front end will suffer from the lack of robust form objects, etc. They can be done but this method requires substainial code. What happens is the web sever where the asp page resides will build dynamically each time a user request the page. There's a lot of processing going on. asp works well where you need to get to live data through a web browser. as opposed to deploying a fe on each machine

3. A c front end (with a sql be), is kind of like a mdb but requires a learning a new lang (c, c++, c#, etc) to build the fe. I would consider it about the top of the food chain when doing any programming.

4. DAP is kind of like a wtaered down version of an asp page as it is best suited for limited deployment for web browsers.

I guess the ultimate solution would be to do data (tables) in sql. Then c (or maybe a.mdb) for the front end for local users and then asp for long distance or limited access (or view only access) to your data...

Hope this helps.

 

[stormin_norm]

Ken-

Thanks for the ranking! I am extremely tempted to jump on the .net bandwagon and go for vb.net, asp.net or c#.net. I most likely will go via asp.net since my client likes the idea of a browser based solution.

Curious you have no preference of adp vs. mdb as a solution. Or does it plain 'ol not matter.

-norm.

 

[Pat Hartman]

Yes it matters quite a lot. .adp's have never been fully developed and it is unlikely that they will go further since they take away developers from the main thrust of the Access application and they have not been well received. I would not even consider an .adp solution.

The two big differences between client/server and web in my mind are how big a problem deployment is and whether or not users outside of your organization need access to your complete application. If the user environment is somewhat controlled so that there are few differences between desktops, deploying Access should not be a particular problem. If no users outside of the organization need access to the main application that also favors Access or some other rich client. The difference between Access and C# or VB.net is simply one of using a RAD tool (Access) or tripling the project development cycle to do it in long hand.

I would only use a web front end if I had to provide an interface for users outside my organization. Personally, I dislike web interfaces. I find them slow and clunky. Plus you need to write large amounts of code to provide even the simplest rich client experience such as cascading combos and subforms. It is certainly feasible to create a complete client/server application with Access/SQL server and tack on a few web pages to display information. But don't just use a particular technology for the sake of technology.

You sound anxious to move away from Access. But especially after my visit last month to Redmond, I see a renaissance for Access. Microsoft has had over 100 people working on this new version of Access which is more than they have employed since the release of A97. Good things are coming. I can't tell you about them since then I'd have to shoot you but I was seriously impressed by the things I saw.

 

[KenHigg]

Norm - All these solutions have major differences in how their insides work. Each one you have mentioned do one or more thing much better than the other and in my opinion, the best overall solution is a mix of several of them.

But, in order to make an argument as to were and how you use them, a high level look at the overall business process is really required. Can you elaborate on what your business is trying to do? Say like you want to track sevice calls and you need approx 30-40 users in one central location to have access to the information. And maybe if you need fld reps to access service history only from the customer sites from laptops. So on and so on.

And knowledge is needed of how tightly the computer systems (individual computers) are maintained. In our operation, the computers are basically locked down as far as not being able to do anything an admin should be doing. We can't load or install software that hasn't been approved by system engineers, etc. So that has a major bearing on what solutions we do.

A lot to consider to get the best mix...

 

[Uncle Gizmo]

Good things are coming

How do far away do you reckon Pat?

I have been thinking about upgrading to A2K3, but I would wait for the next version if it is not too far away.

 

[Uncle Gizmo]

or new .adp

You might find this blog useful:


And I personally like this book, Beginning VB.Net Databases by author Thearon Willis from Wrox press.

 

[stormin_norm]

Thanks VERYMUCH for all your guidance Uncle Gizmo, Ken and Pat. I think I will head for the the .mdb/mde solution to keep the users familiar with the existing access screens and make the handful of DAP screens for the 'light' users.

You have all convinced me it is not worth the time/effort to generate asp.net/vb.net/c#.net code & screens. Not for this app at least.

Hey Pat- Instead of a book, maybe you should start an access-rumor blog. You could be the Robert X. Cringely for access

 

[Pat Hartman]

I am trying not to start any rumors. I'm hoping that when the beta comes out the gag order will be lifted and I can talk about the new features. I am pretty excited about them.