Solved

Using MS Access database in VB6 application

Posted on 2004-09-16
9
404 Views
Last Modified: 2008-02-01
I need to access a MS Access 2000 database via my VB6 application and would like to know the best way of doing this.

1) Should I keep the database connection open throughout the lifetime of the application or is it best to open it when I access it?

2) What error handling should I implement, e.g. rollback?

3) I want to use the Jet 4.0 driver, what should I look out for, if anything, when installing my application on clients' PCs, e.g. OS versions, driver versions, etc.?

4) There are quite a few parameters on when opening a connection.  My database access is very simple, how should I implement the connection string?

5) Are there any good sample VB6 applications that access/write to/read from a MS Access database?

Thanks.
0
Comment
Question by:halfondj
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Accepted Solution

by:
UncleMidriff earned 250 total points
ID: 12074443
1)  
I would only open the connection when you need it, and then close when you are through.  I can't really tell you specifically why I think this way, it is just how I have always been taught.

Ex:  Dim Connect As ADODB.Connection
      Set Connect = New ADODB.Connection
      Connect.ConnectionString = "Blah"
      Connect.Open
      '
      '  Do stuff
      '
      Connect.Close

2)  
In my limited experience, I haven't needed to worry all that much about "rollback."  Using datasets (or recordsets or whatever), you connect to the database and pump all the data into a dataset.  Then you change stuff around in the dataset.  When you are done, you use the Update method of the dataset, which should apply the changes to the database for you.  If for some reason that method fails, it has been my experience that the database just doesn't get updated; existing data usually doesn't get screwed up.

3)
If you use the Package and Deployment Wizard that comes with VB6, it should take care of most of the dependency issues you might have when installing your application on a client's PC.  Abut the only thing I have ever had to look out for is making sure their version of MDAC (Microsoft Data Access Components) is up to date.

4)
Check out this:  http://www.visual-basic-data-mining.net/Forum/ShowPost.aspx?PostID=1808 .  A few post down, a poster named LadyRhoanne posts a fairly good example of what a simple connection string should look like.  I haven't tested it, but it looks about right to me.

5)
That same post I just mentioned also shows some basic things you can do with with Access in VB.  As for sample applications, I am sure a few google searches will turn up a plethora of example code and even a few complete programs.

I hope that helps you a little.  And if I am being stupid and wrong about anything, please let me know!
0
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 250 total points
ID: 12074538
<<1) Should I keep the database connection open throughout the lifetime of the application or is it best to open it when I access it?>>

It highely depends on your application.

A: In general it is the best practice to close the connection( opening a connection is resource intensive)
only when project is finished.

B:In rare cases when you have memory limitation closing and opennig connection might help.(this is rare )


2) What error handling should I implement, e.g. rollback?>

Exactly. use begintrans , committrans and when error happens rollback. This is industry standard to handle errors and will

save you from lots of headaches.


<<3) I want to use the Jet 4.0 driver, what should I look out for, if anything, when installing my application on clients' PCs, e.g. OS versions, driver versions, etc.?>>

Generally you dont have to do anything. Microsoft package and deplloyment wizard will pack all necessary dlls into an
setup.EXE file and then install it into the client.

<<4) There are quite a few parameters on when opening a connection.  My database access is very simple, how should I implement the connection string?>>

It depends on how your applcation will be used. if it in for reading data you can you Readonly and forward only parameters,.

<<) Are there any good sample VB6 applications that access/write to/read from a MS Access database?
>>

Start here:(ADO tutorail)
http://www.w3schools.com/ado/default.asp


0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 12074579
If you will workd withe different client OS's then I do not  recoomend package and deployment wizard .( lots of compatibalitiy headaches)


Get the Free Inno setup http://www.jrsoftware.org/isinfo.php, it is a far more stable setup uitlity and works much
better (less buggy) agiants dieffernt os's.

0
 

Author Comment

by:halfondj
ID: 12075068
To Uncle UncleMidriff:  Thanks for the link to the sample.  In the remarks, it says to add the ADO control, then to delete it.  Why does it have to be deleted?

To  unknown_routine:  I agree with you.  We are already using InnoSetup and it's great.

Thanks to both replies.  They were extremely helpful and I did find some good examples using Google.

I'm going to split the points :).
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:UncleMidriff
ID: 12075197
Thanks halfondj, glad to help.

And thank you, unknown_routine, for correcting some of the things I said; I always like being less wrong and more informed.
0
 
LVL 2

Expert Comment

by:UncleMidriff
ID: 12075283
Whoops...I forgot to answer your question.

I don't really know why it says to add a control and delete it.  I just created a new project, added a reference to "Microsoft Activex Data Objects 2.7 library" and I was able to use intellisense with ADO objects just fine.  If that won't work, you might try the adding and then deleted thing, but it doesn't make a whole lot of sense to me.
0
 

Author Comment

by:halfondj
ID: 12076174
I know, it doesn't make sense, but I wanted to ask the experts :).  I'll have to try it.

Thanks again.
0
 
LVL 2

Expert Comment

by:UncleMidriff
ID: 12076186
heh...me, an expert.  Fancy that.
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 12076460
Hi,

write has obviously little knowlege of ADO.



to work with the database (access or oracle or ...) you need a reference to ADO object.



You have 2 options:

1: go to project refernces and add microsoft activex data object

This is a clean good way to go.


2: goto project COMPONENTS then add Microsoft ActiveX ADO control ( this will created the reference mentioned in step 1)

and delete it( even when you delete the control the reference to activeX data objects mentioned in option 1 remains.)

it say delete it because  you do not need the actual control you only need the reference.


go for option 1. why go with the long way?




0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now