VB6 to Access 2007 VBA

[syswizard]

I've been getting a lot of criticism for suggesting the above solution for a client.
Here are the key points:
1) Backend database is already in Access MDB format; 40k lines of VB6 code - ADO access method; this is a customized CRM application with many relationships.
2) data volume is small - nothing over 100,000 rows
3) Extensive use of Office automation required - Word, Excel, etc.
4) Sharepoint list migration required for remote access to tables via web browser
5) Conversion budget is below $100,000

Conversion to an ASP.NET app would be at least $100k, correct ?
Any other ideas ?
Feedback greatly appreciated !

 

[syswizard]

What is wrong with the current application? That should give us a better feel for what the new application should be.

An Access front end with a SQL Server or Jet back end is the fastest, cheapest solution but is only the best if there is no web updating required.

The current system is undocumented and the code is uncommented. It is essentially unmaintainable. Yes, the client has multiple locations around the WORLD. Collaboration is needed. Web updating is needed. Sharepoint via web is the perfect solution IMHO for the lowest cost. ASP.NET would be very expensive for this complex application.
The new application must be well-documented and easy to modify. That's why I think Access 2007 is perfect for this app.
I plan on clipping much of the VB6 legacy code into Access VBA modules and forms. Later, database improvements will be made.

 

[syswizard]

I very much appreciate your reply Pat. Regarding the volume issue, I've heard of other developers being able to access up to 60,000 rows in Sharepoint lists....and that is the max this application would ever have.
Tell me if I'm wrong on this, but I've heard of many problems in developing apps using SQL Server and Access 2007. Note: this is a very small company. They would need to hire or contract an SQL Server DBA, and this would add greatly to their annual IT budget IMHO.
I'd like some more information on your app's architecture....is it using Access 2003 ? Does the front end reside locally ? How large is the front-end (MBs) ? Is document sharing a part of the requirements ?

 

[Simon_MT]

I have been discouraged from using Sharepoint so I haven't lookerd into the ramifications of a Sharepoint deployment as opposed to using a Terminal Server. You could deploy a Terminal Server and get users to log on to the TS instead. This applies to any application. You still use the front end and back end solution. Yes, you would need hardware in the form of a an addtional server and a decent up speed and perhaps VPN but the avantage would be you could deploy now and later introduce ASP.NET.

Simon

 

[syswizard]

I have been discouraged from using Sharepoint

One might ask why ? Microsoft Online is now practically a free sharepoint hosting service.

but the advantage would be you could deploy now and later introduce ASP.NET.

Yes, but the ASP.NET rewrite is estimated to be 1+ man-years of effort....too expensive for this small client. This is a highly customized CRM for which there are no packaged CRM solutions, no frameworks even close to it's structure. Microsoft Dynamics 4 has a VBA layer for customization and that looks to be the best alternative to Access at this time; However, it's ASP.NET version HAS NO CUSTOMIZATION capability....so it's effectively worthless for this client. Also, this client's business is changing all of the time and everyone knows web-based apps are not that flexible and not cheap to maintain.
This is one of those situations where there is clearly no best solution. Indeed, the client has been sitting on rewrite proposals for year and did not pull the trigger. However, they are now ready to do so after I proposed AC2k7 with Sharepoint linked lists: no DBA required, no VPN required, no extra hardware, no extra servers or software licenses such as SQL Server or Terminal Services, etc. Effectively, this is the "no extras needed" solution.

 

[Banana]

FWIW, Terminal Server sounds to me the best solution in terms of not having to alter your Access application at all and is quite cheap and easy to set up compared to have a SQL Server, which you would have to have someone to be the DBA in some degree (whether as a full-time DBA, an IT manager, or outsourced).

The other option would be to use a network-capable RDBMS backend with Access. In my last project, I used MySQL to provide WAN connections to Access front-end clients while getting transaction/audit logs for free (doable in Access but requires manual coding and setup). That allows me to bypass the problems I would have had with web browsers and maintaining webpages/scripting and keep the 'rich' client features.

 

[Simon_MT]

The advantage of a Terminal Server is you are providing a homogenous service not only to data but email and documents. Yes of course, there are other solutions you could deploy but under TS you don't need to do any major reprogramming and it will run applications as low as Offfice 97 (although Access 97 is not very efficient). In essence with TS you are using a local service (LAN) with the WAN handling screen dumps. Local Printers can be mapped onto the Remote Desktop.

Personally, I would be tempted to use the majority of the budget to convert to ASP.NET and become totally web orientated and provide a fully collaborated and integrated solution.

Simon

 

[syswizard]

under TS you don't need to do any major reprogramming and it will run applications as low as Offfice 97 (although Access 97 is not very efficient).

Unfortunately, we DO have reprogramming....going from VB6 to VBA for one thing. However, this is minor compared to a total rewrite IMHO.

Personally, I would be tempted to use the majority of the budget to convert to ASP.NET and become totally web orientated and provide a fully collaborated and integrated solution.
Simon

Well, my sense is that Microsoft's new Azure platform is going to make ASP.NET coding look foolish within the next 1-2 years. Everyone's going for web dev generation tools right now.....and Microsoft is not going to miss that huge market.
I think I can come in at HALF the cost of an ASP.NET rewrite...maybe even more. The big issues as always are the subtle bugs in Access 2007 and the limitations of the Sharepoint list and form linkages.

 

[Banana]

Not to be a wet blanket, but I want to point out that 'Everyone's going for web dev generation tools right now' isn't exactly a good reason/argument for any project decisions.

The real questions should be whether you really want to use it or not, answering questions such as:

1) Do we need to have the application run anywhere without installing it first? Is deployment important factor here?
2) Do we need to reference libraries or APIs to take advantage of native functionalities? Even if we aren't using libraries, will we ever to?
3) Does our validation routine, if one is needed at all, requires live feedback with the users or will the users be okay with filling a long form only to be told it is invalid or answer several short forms on several pages only to realize that they need to change their mind on the page #1?
4) Is protecting the source code and/or data a consideration? What is the threat model, and best answer to the threat model?


There's lot more questions, but doing it just because everybody's doing it is not one of the answer.

 

[syswizard]

The application I referenced uses SQL Server 2005 as the be and each user has his own copy of the fe installed in his Citrix environment. Terminal services works the same way.

Thanks Pat, but I was told that using Windows RDC (Remote Desktop Connection) can cause data corruption. Is remote access via terminal services or citrix completely reliable as far as database integrity goes ?
Again, the only reason I am recommending Accdb as the database right now is the Sharepoint list linkage feature. If SQL Server had that feature right now, I would recommend it. But I have a feeling it's part of the new Azure framework....and not yet available for commercial use.

 

[syswizard]

Not to be a wet blanket, but I want to point out that 'Everyone's going for web dev generation tools right now' isn't exactly a good reason/argument for any project decisions.

I would think twice about that statement....with all of the IT budget cuts going on right now and the scrutiny of green eye shaded accountants who are running the show now-a-days.
Wouldn't it be foolish if they spent $100k now for an ASP.NET CRM only to discover in a year they could have done it for half that amount with a new web dev framework ?

1) Do we need to have the application run anywhere without installing it first? Is deployment important factor here?
2) Do we need to reference libraries or APIs to take advantage of native functionalities? Even if we aren't using libraries, will we ever to?
3) Does our validation routine, if one is needed at all, requires live feedback with the users or will the users be okay with filling a long form only to be told it is invalid or answer several short forms on several pages only to realize that they need to change their mind on the page #1?
4) Is protecting the source code and/or data a consideration? What is the threat model, and best answer to the threat model?

Actually, NO to each of the above.
If anyone knows of any dev tool that would make the rewrite or conversion of this CRM inexpensive and still meet the requirements of having LAN-based and web-based access and updating, please let me know.
I have reviewed AWARE IM and it is quite impressive but when they told me their consulting services / tech support was $1600/day, I decided that was not going to result in a low-cost solution in the end.

 

[Simon_MT]

I have not encountered any data corruption using RDC, in fact, I was a little surprised at the resilence of the Terminal Server. Some users unbeknown to me simply swapped desks and then continue with the previous session that they had left. You have to remember with TS nothing is really remote, all that is happening is the flow of key strokes and screen dumps plus printing endis up at the remote site. In essence, the remote traffic is relatively light but as long as you have adequate RAM, processing power and broadband up speed the TS service is responsive.

Simon

 

[syswizard]

I have not encountered any data corruption using RDC, in fact, I was a little surprised at the resilence of the Terminal Server. Some users unbeknown to me simply swapped desks and then continue with the previous session that they had left. You have to remember with TS nothing is really remote, all that is happening is the flow of key strokes and screen dumps plus printing endis up at the remote site.
Simon

Thanks but interestingly one Access MVP and consultant told me that he thought this set-up was subject to MDB corruption. However, based on your explanation, I don't see the problem....at all.

 

[Banana]

That would be news. I'd sure like to know his reasoning for that.

 

[pbaldy]

I would too. I use RDC to connect to another office and I work on all their Access applications that way. I've never had a problem with it, and can't see offhand why there might be one.

 

[pbaldy]

A PS relating to Banana's Porsche. I suspect 9.81 m/s is the metric equivalent of 32'/s. That trip might not use any gas, but the suspension will be seriously stressed.

 

[syswizard]

A PS relating to Banana's Porsche. I suspect 9.81 m/s is the metric equivalent of 32'/s. That trip might not use any gas, but the suspension will be seriously stressed.

Not to mention the frame or body as well....

 

[Banana]

SysWizard,

Just had a thought. You may want to ask that consultant to elaborate on the corruption. I wonder if he may have been speaking of a specific condition. I understand from others posting (having never used TS myself) that a common mistake is to provide only one front-end on the TS when each user should have their own copy of FE on the TS. Maybe that was what he referred to?


WRT the PS:

Two minor corrections:

A PS relating to Banana's Porsche.

I don't have a porsche.

I suspect 9.81 m/s is the metric equivalent of 32'/s.

Just to point out that it's not speed being quoted, but actually acceleration, so the Porsche is increasing speed of 9.81 m/s (or 32 feet/sec) per second (thus m/s² {or ft/s²})

 

[pbaldy]

Sorry, I didn't realize a joke required that much precision, as I was aware of those points. I'll do better next time...or not.

 

[Banana]

No, I'm glad you got the joke. I'm just anal-retentive. Maybe we can laugh at my nitpicking instead?