Microsoft Access to Visual Basic

[AlexMidd]

I currently manage several databases in Microsoft Access and wish to migrate these to Visual Basic stand-alone applications. I wish to do this because there are currently compatibility problems between versions of Microsoft Access that we do not require to have in the future.

I would prefer to develop an application in Visual Basic that uses the data in Access tables as its data source but is stand-alone in that it can be installed on any computer (running Windows) whether or not the client has Microsoft Office and no matter which version of Office they have.

My question is, does anyone know of any problems with this approach and is it feasible to do what I propose? I have written small VB apps that use Access tables before and they worked fine, however I have never been able to get it to reproduce reports like the ones in Microsoft Access. I have heard that Crystal reports can do this but I have no experience of it.

Any advice would be appreciated. Have fun! )

Alex Middleton

 

[cheerio]

So you are still going to have the data in an MDB file which means there is still a risk of compatibility issues if the user has a different version of Access on the machine (eg a different version of dao may exist). You are also going to have to master housekeeping tasks that you could do through an Access front end such as compacting the database. However, what you are wanting to do is certainly possible.

Personally, I might be tempted to use MSDE wich comes as part of Access 2000 and is a cut down version of MS SQL Server. The advantage is that there is a natural upgrade path for the client if the project grows. Also there is much more robust security.

Crystal Reports is a very powerful reporting package which superficially is similar to Access reporting. However, you can more easily have sub-reports. One major advantage is that Crystal can work with most databases so you can standardize on one reporting tool.

Finally, if you are going down this route think carefully whether you should be using VB.NET rather than VB. Certainly if you are going to use VB be aware of the issues that arise on conversion. For example, .NET insists that all arrays start with an index of zero so you should avoid commands such as OPTION BASE 1.

Ken

 

[AlexMidd]

Thanks for the advice. Can you explain what MSDE is and will it run on systems with Office 97? Also, I thought that if you wrote an application in VB and added DAO to the project, then the user does not even have to have office on their system for it to run. Of course, I may just have got it wrong.

Basically I need an application that can be installed on any Windows system without compatibility issues. If Microsoft can't deliver this, then I will have to look elsewhere, but surely someone can do it. As it stands, having to develop all of my apps in multiple different versions of Office is not cost-effective. It is costing us money in lost time and licence fees to Mr Gates. Have fun! )

Alex Middleton

 

[petermeachem]

You don't need Access if you programme in VB. Reporting in Crystal is ok, not as easy as in Access, but not too much different. You may as well stick with VB6, no pint in spending time debugging MS code for them plus third party controls for .net are much more expensive (as far as I can see) than vb6 ones. Peter Meachem
peter@accuflight.com

 

[AlexMidd]

Hi, thanks Peter, that's what I wanted to hear. Just to make sure, if I write my app in VB6, then add DAO objects (latest version) and use these for my tables, then the client does not need Office or Access, and any versions of Office/Access that they happen to have on their systems will not interfere with the application.

Thanks
Have fun! )

Alex Middleton

 

[petermeachem]

That's correct as far as I have ever seen. You could use ADO instead. Peter Meachem
peter@accuflight.com

 

[bjd4jc]

I would recommend that you use ADO instead of DAO,,, especially if the user does not have your version of Access installed locally

 

[computerjin]

hello there

use ADO 2.5 or 2.7(that shipped with .NET) instead of DAO. DAO is deprecated technology. using ADO will make ur VB app independent of DBMS. it doesn't matter u used Access 95, Access 97, Access 2000 or Access 2002 (XP) or SQL server or ORacle server. you can handle all these and many more using ADO technology in ur VB apps. only u will have to do is to install MDAC 2.5 or MDAC 2.7 components along with ur VB app on the client machine.

have fun

thanXXX
ComputerJin

 

[CCLINT]

"deprecated"? I wouldn't say that.
The questoner wants to stay with a MS db. Going DAO/Jet as opposed to ADO/Jet is the preferred way. Many more advantages - especially with speed. Don't forget - we are talking about an MDB here. Jet is the native language for an MDB whether under DAO or ADO - both use it. DAO was however specially made to work the best with JET. And whether using an MDB locally or on a Server, it still remains a Database file, with all of the dis-advantages that go along with a db of this type, esp. in multi-user scenerios.

However - some have no other choice.

Use MDAC 2.5 Jet 3 SP3 in the installation program and you shouldn't have any problems.

Important: In order to be as flexible as possible, taking into consideration future changes such as changing db types, and some customers only needing a local db and ones with multi-user env., do not use DAO specific functions. And create a data enviroment class to pass all database and recordset objects commands/methods to. Then, if you decide to change to another db type such as SQL server, you only need to swap out the data enviroment class. This will keep your program flexible for the future while at the same time allow the use of the best data engine at the moment. Plus, once created and thorougly tested, you need only to copy the data env. class to other projects to giving them also the same flexibility. And you can easily use the program then with some customers who only need a local MDB, and ones who use something like SQL Server for multi-use enviroments. I would consider using a Front-End MDB data base that can be linked to a Back-End Db, whether the BE is an MDB or a SQL Table.

While Crystal Reports is a more complicated to work with than ACCESS reports, it will do whatever you want just the same.
One option to using an MDB as a Front-End Db would be to create the reports with ACCESS and then use the SnapShot Viewer to display the reports. The you do not need to have ACCESS on the same PC. You can freely download and distribute this from the MS Site.

 

[AlexMidd]

Phew, this is getting complicated. Many thanks for all your advice folks. However, I am still not absolutely sure of the best way forward.

First of all, I would point out that I do not need multi-user apps - all are to be stand-alone, single-user, local (i.e. not server-based) apps. Second, I intend to use Access table as all the data is currently in that format, and I am familiar with the security features (which took ages to learn). However, if ADO would be better for the future, I would consider that.

In the long run, it needs to be kept simple, i.e. no servers, systems that require manual installation or esoteric software that has to bought in in addition to the cost of VB, if possible.

The nub of the situtation is, do all these tools come with VB, and if so, which version. Also, I wish to buy a book on crystal reports but nowhere does it say what version of Crystal Reports comes with VB6 or VB.NET. Any advice would be appreciated.

Anyway, thanks for all the help so far - it's given me some pointers to follow up. Have fun! )

Alex Middleton

 

[CCLINT]

Use VB 6.0.
Based on that, and if the Database is not going to be very large, then go ahead and use ADO as you will get plenty of help here and the future VB tools/updates/VB Net/etc/etc will be aimed at using ADO, and you will be learning the tools to use for the future.
If you haven't gone too far you might want to jump into VB Net.

VB 6.0 comes with ADO and DAO engines and tools/components, and the updates from the MS Site for these engines are FOC.

If the DB Is large and speed is important, then maybe the other considerations are worth while.
I was more responding to what I considered to be a narrow opinion. What I was trying to imply is that DAO is not a wrong choice under certain conditions, and, if the program is written right, it wouldn't matter so much as to which you would use now, because a change in the future would be easy. Plus it is easier to work with when accessing and manipulating an MDB - it is just a problem with future support from all sides.

But, then in your case, use ADO, only because of the reasons mentioned in the first paragragh of this comment.
I would still consider making a data class of your own and if the projects will be or get large then stay away from bound controls.

 

[AlexMidd]

Thanks for that, CCLINT. I agree with your opinion on DAO/ADO. Whilst ADO is newer and probably the way things are going, DAO is still very much there. I would probably use VB6, but I can't find out if it comes with Crystal Reports. I know VB5 did and VB.NET does, so I assume VB6 does - but knowing my luck ...

Thanks again to everyone - I am open to all views and suggestions. Have fun! )

Alex Middleton

 

[CCLINT]

VB6, comes with Data Reports. It is not near as good as Access Reporting or Crystal Reports full version (7.0 or 8.5). And the disadvantage here (DataReports) is that the reports are compiled with the Exe, making it larger - sometimes very large.