Relational data in VB6 Without Database? 6

[jjlogan]

I would like to prepare a VB6 application where the user collects data and can later print reports. I normally use Access but want to do this in VB6. I would like to know if I can use some type of joining of data sets; i.e. keep data on separate objects in separate text files - probably Random Access files and then join the data for reporting. Can this be done in VB6, or is database software a necessity? I've searched my VB6 (Obsborne) reference and in both the Basic File Handling chapter and the Database Development chapter this question is not directly addressed!
Thanks for any help.
Jeff

 

[JoeAtWork]

Well, if the data is relational I would certainly recommend you store it in a database. It's important to understand that the Relational Database Management System is separate from your VB code. You use VB to connect to your database to retrieve data, manipulate it, then save back changes to the database.

You would most likely use ADO to make the connection to the database, as well as the in-memory data objects (i.e. recordsets). You could use pretty much any of the standard RDBMS systems, including SQL Server, Oracle, or even Access (i.e. an MDB file).

I guess you could create your own RDBMS system with text files - but why in the world would you want to? Even if you worked on it for years, I doubt you could create anything as efficient or stable as existing, and free, database systems, such as SQL Server Express or MDB files.

 

[johnwm]

You can use an MDB file straight from VB6 via ADO without any involvement from Access. If you need more users you could look at MySQL

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[tedsmith]

VB6 can create or modify tables that can be read by MSaccess later on and you can use VB6 to query them without having a copy of access in the computer.
Its a lot easier to compose the query using Msaccess then copy and paste the SQL string into VB (sometimes having to slightly modify the syntax when you do)
Plenty of examples in the MSND help disk and elsewhere

 

[jjlogan]

Joe, John, and Ted - thanks for the information. You made strong arguments for using outside database software, but I was trying to create a simple VB6 data collection system that does not require the user to have Access or other db's on their machines. I assumed it would be expensive for me to create with some form of "run-time" version of Access - if it exists?
Could you comment on this approach - run time? What would I have to purchase (cost, etc)?
Thanks again, Jeff

 

[Glasgow]

You could build your own ADO recordsets in memory and use the Save method to save them to disk and the Open method to retrieve them from the disk file. However, the usefulness of this approach might depend on the complexity of the data and I'm not sure how you would approach joins.

You may or may not have digested johnwm's point completely - as far as I'm aware you can still ship your application with an Access/Jet database and manipulate that database from within your application without any need for the user to have Access physically installed i.e. users will not need an Access licence.

 

[jjlogan]

Glasgow - Your second point "as far as I'm aware you can still ship your applicaton with an Access/Jet database..." gives me direction to check further on this approach with Microsoft site. I need to know how to go about this and how much cost to purchase this Access license for use with my VB6. Or, perhaps I could just use this version of Access/Jet without VB6.
Thanks,
Jeff

 

[strongm]

>ow much cost to purchase this Access license for use with my VB6.

There's no cost, no licence for distributing an mdb

 

[Glasgow]

Although I don't think I've ever attempted it myself, theoretically you shouldn't really need an Access licence at all. Think of Jet as the database and Access as just one of the tools that allows you to design, view and manipulate the database content.

You might want to start by looking at the CreateDatabase method of the WorkSpace object. E.g.

http://www.vbgold.com/vb-projects/locked-access-database.shtml

Probably lots of threads in this forum about it.

 

[jjlogan]

Glasgow. I looked at the site you provided and the code for creating new database with password, etc.. Although this code did not attempt to create and populate tables, I'm beginning to get the picture that an mdb can be created from VB6, and that the User does not need to have Access installed. I will have to look into this more - this is better news that working with strictly text files and arrays it seems.
Jeff

 

[JoeAtWork]

I've created a number of VB6 applications that used an MDB file for the backend. There is no licensing for this architecture (well, assuming you have a legitimate copy of Visual Basic itself).

You can use either the DAO or ADO libraries to work with the database. DAO is probably best if you know you will always be using an MDB file. ADO is a good choice if you think you will one day upgrade to a more powerful database system, such as SQL Server.

 

[jjlogan]

JoeAtWork,

Thanks for that input regarding "no licensing for this architecture."
I am assuming, based on above thread, that when you "created the VB6 applications that used an MDB file for the backend," that you created the mdb's from your VB6 code rather than creating them in Access. Is that true?
Or, can you create the Access database in Access and then use the mdb file with VB6 DAO or ADO and the User does not need to have Access on their machine?
Thanks,
Jeff

 

[Glasgow]

You can still create them in Access then ship them with the application.

 

[jjlogan]

Glasgow,
Being able to create the mdb file in Access and then ship that file with the VB files, I won't need to spend any more hours flowcharting Basic File Handling of VB for this basic relational db application. Thanks again for the help.
Jeff

 

[Glasgow]

Good luck!

 

[tedsmith]

Its quite easy to create or modify a table with indexes & everything from vb6 (but easier from access

If you know your sql code you can also write and query using recordsets, dynasets and snapshots.

If you have a copy of access it is often easier to creat the code then copy and paste it to access.

Make sure you have the correct jet engine, there are a number of them that are not compatible

 

[jjlogan]

tedsmith,
Thanks for the input.
1) When you said "Make sure you have the correct jet engine, there are a number of them that are not compatible", where do you "have the jet engine?" Does this software come with MS Office for example? I have Access 2002 version on my machine.
2) If I distribute a VB6 project along with an Access file (mdb), does my client need the correct jet engine on their machine to use my VB6/mdb project? If so, and they don't have MS Office, how do they get the jet engine? Can you give me some insight on this?
Thanks, Jeff

 

[dilettante]

Jet 4.0 has been a part of Windows for several releases and was a standard update even for very old Windows editions (NT 4.0, Me, 98, 95). If you are using a very old version of Windows you might want to update to Jet 4.0 Service Pack 8. However Windows XP SP2 contains an even newer version (a kind of SP8+).

You'll also want a late MDAC, and many systems can be updated to MDAC 2.8 SP 1. Windows XP SP2 already includes this.

In general you should not need to fuss with any of this if your applications target contemporary versions of Windows (Win2K or later). Updating to current Jet 4.0 and MDAC software is a good idea in general, but even more important if you are relying on specific features or bug fixes from later versions.

Jet is not Access. Access is not Jet. A long time ago Jet was hard-coded into Access. That was well over a decade ago.

 

[tedsmith]

When you make everything including the final exe with VB6 it all uses the same jet. This automatically gets loaded on the PC when you install your final exe.

Its only if you want to use Access to examine or modify the tables you have made in VB6 you have to make sure you have the same version. Eg Access97 was not always compatible with Access2000.
U soon find out when you try it.
Also you have to choose the right ADODC reference for the Access you are using in references (by trial & error)