Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using MS Access database in VB6 application

Posted on 2004-09-16
9
Medium Priority
?
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Accepted Solution

by:
UncleMidriff earned 1000 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 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

688 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