Solved

Using MS Access database in VB6 application

Posted on 2004-09-16
9
405 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

911 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

24 Experts available now in Live!

Get 1:1 Help Now