# Access 2000 and VB 5.0



## Kristi (Aug 10, 1999)

I have existing databases created with VB 5.0. When I open them with Access 2000, I get a message that the database was created with an earlier version than 2000. I have the option of converting it and if I don't convert it I can't make any changes. Although I can open and view it.

My concern is that I want to create a new database and use both VB and Access. I realize I can't do that with my existing versions of the software. My questions are:

1] Can I develop a database using VB for some things and Access for others? My end goal is to create an executable that runs independantly of Access. 

2] If it's possible to use both programs for developement, then what version of VB is needed for Access 2000?

Thanks,
Kristi


----------



## jguarnieri (Nov 22, 2003)

I have VB 6.0 Pro and if I recall correctly "its been awhile" I installed Access 2000 on my PC and created a DB then I had to check in the VB enviorment "Project References" 
Microsoft DAO 2.5/3.51compatibillity library then I could load the DB from Access 2000. 

Are you useing DOA or ADO?
I program as a hobby and have only used DOA.

You should be ok if you do this and run the Package Deployment Wizard and create your exe.

HTH John G:up:


----------



## coderitr (Oct 12, 2003)

You should use ADO for your database connectivity. It's a little more complicated in the database work but once the initial connection is made, the program is written the same way regardless of what the back-end database provider is. I don't remember if VB5 supports ADO so if not you may be stuck with DAO in which case you can only use Access or ODBC.

You can use Access and VB at the same time. If your plan is to develop a stand-alone VB executable then you won't be able to execute forms and macros embedded in the database but the runtime machine does not need to have access installed for your application to run. If you use Access forms, the machine the application is running on must have access installed and therefore licensed. You can, however, move complex SQL procedures to "Saved Queries" in the database. What this buys you is performance and granularity in your code. Other database providers call these stored procedures but, as per usual, MS has their own way of doing things. 

Also, I would consider a more robust database provider than Access. MSDE2000 (SQL Server 2000 without the GUI) is free if you own a variety of other MS products. MySQL is also free for personal use and I've heard good things about it too. It's just that Access' performance breaks down considerably once the number of rows gets into the thousands. I wrote a Help Desk call tracking application for the call center staff that I supervise at work. Originally, it used Access but I started seeing performance and data integrity problems once the number of rows in the calls table got above about 20,000 (about 1 1/2 years worth.) I moved the database to SQL Server 2000 and haven't had any more problems. I wrote the code using ADO so all I had to change was the connection string to the database and a few lines of code that I had neglected in the initial release (cursor location.)

Please, if this project is important to you, feel free to call on me for additional advice. I have been writing database access software in VB for nearly seven years now.


----------



## jguarnieri (Nov 22, 2003)

Hello, I have a question for you. 

I program in VB 6.0 Pro not professionally but for myself as a hobby if you will. I used to have a restaurant business and at that time I wanted to have a program for a food delivery service that would not cost $10,000.00, which was the going rate for a top of the line POS program for restaurants at the time.

I taught myself programming from VB for DOS up to VB6 Pro and I even learned about classes and oop, it was a huge learning curve for me, but now I have a decent program that works. It uses Access as the back end. 

I am sure a pro such as yourself would find many thing to change in my program including my use of DOA. DOA was the only thing at the time, ADO came later.

My question: I have a generic class that I use to handle my database operations like add,edit,delete, search etc. If I switch to ADO I assume I would have to totally revamp my entire Class to accomodate ADO syntax. Am I correct? If so is it worth it to make the effort to change my program over to ADO. If I do it at all it would just be for myself as a learning exercise and I really don't want to kill myself like I did when I first started learning programming on my own. Thanks John G.


----------



## Kristi (Aug 10, 1999)

Oh John,

Your post must be intended for coderitr! I'm not even as experienced as you are. Hopefully he will be back to catch your question.

As for me, I _think_ I've decided to go with VB 5. One reason is that I already have 5.0 and don't want to invest in 6.0. Also, I've done some databases in 5.0 already, and I'm more familiar with it. Since my goal is a stand alone executable, I'll forgo the Access help.

If someone was to give me 6.0 for Christmas, I'd reconsider. ADO began with 6.0.

I'm developing a database for diabetic felines. The data would consist of patient profiles, medication management, blood sugar readings, and diet management. Believe it or not, diabetic cats are not rare. This is a humanitarian project, not a commercial one.

PS: my father was in the food industry all of his life.

coderitr,

Thanks for the offer of help. I'll most likely be back for lots more of it.

Kristi


----------



## jguarnieri (Nov 22, 2003)

Hi Kristi,


----------



## jguarnieri (Nov 22, 2003)

Hi Kristi,

Sorry, yes that post was for coderitr 

I'm developing a database for diabetic felines. The data would consist of patient profiles, medication management, blood sugar readings, and diet management. Believe it or not, diabetic cats are not rare. This is a humanitarian project, not a commercial one.


Are you doing this program on your own? I mean doing it at work as a volunteer or are you a professional programmer? Just curious. John


----------



## coderitr (Oct 12, 2003)

John

You're right. To use ADO, you will have to recode your class member functions to use the ADO syntax. The biggest difference between DAO and ADO is the abstraction from the data source that is built into ADO. The code need not care what the database provider is as long as it is either ODBC or OLEDB compliant. The highest level of instantiation in ADO is the connection object. This is where the programmer specifies the database connection parameters including data source type, user name, password, query timeout, etc. After that initial connection is established, the rest of the code is transparent to the database connection. You can even use SQL constructs that are specific to Oracle or SQL Server once you have that connection (assuming you're using OLEDB and not ODBC) (ODBC generally does not allow the proprietary stuff.) 

You have already done yourself a lot of good by exporting the database access code to your class. Now, the application that consumes the class doesn't have to change considerably (if at all.) My initial reaction to your "is it worth it" question was "of course" but it really depends on what you want to do with this code. Once you have a working class for database access, you can (obviously) re-use it in other applications. These other applications can then use whatever database provider you (or your customer) chooses.


----------



## coderitr (Oct 12, 2003)

One more thing. With the ADO methodology having been as successful as it has been, don't be surprised if MS drops DAO support from its products in the near future. It really is a dying breed as surely as Access is little more than a spreadsheet. Don't get me wrong, for small applications that don't manage a lot of data, Access works well. But throw a few thousand rows at it and see how unstable it can be.


----------



## jguarnieri (Nov 22, 2003)

Thanks Coderitr, very informative explanation. I will kick ADO around some day and see how deep I want to go. 

John


----------



## coderitr (Oct 12, 2003)

You're welcome.


----------



## Kristi (Aug 10, 1999)

John,

No professional here! I'm doing it because I want it for myself, and I know others may be interested also. And because I like doing things like this.

I guess I'm a jack of all trades and a master of none. My background in computers and programming goes back to ... uh, well ... unit record machines. Like the 402 Tabulator. *That* was "hard wiring." So I have a very broad background in data processing, but not in depth in any one area.

I'm about halfway through with a small VB program that plots data points on a line chart. It will be useful for people who don't have MS software to make charts. Again, for people who have diabetic pets to track their blood sugar levels. I'm hoping to release it on New Year's Eve because that's my cat's second anniversary of diabetes diagnosis.

Kristi


----------



## TimCottee (Oct 10, 2001)

Just to clarify some things that haven't been addressed here:

ADO can be used in VB5 as it is simply another project reference that needs to be added. The methods and properties in ADO can be used in both VB5 and VB6 in exactly the same way, the only difference is that the ADO aware controls are not available in VB5 in the same way. If you are coding everything and not making used of controls that are ADO capable there is absolutely no reason why you could not take advantage of ADO in VB5.

As for DAO, whilst I agree that there is a likelihood that Microsoft will retire this at some time, there is as yet no public plan for it. In order to use an access 2000 database, you need to ensure that you have the DAO 3.6 libraries available to you. This is available in Jet Service pack 4 and above, download this from Microsoft, install set the reference(s) and you can again use it in VB5 quite happily. Again the only problems that you may encounter are the available controls which may or may not work satisfactorily with DAO 3.6


----------

