Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Using MS Access database in VB6 application

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
halfondj
Asked:
halfondj
  • 4
  • 3
  • 2
2 Solutions
 
UncleMidriffCommented:
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
 
unknown_routineCommented:
<<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
 
unknown_routineCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
halfondjAuthor Commented:
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
 
UncleMidriffCommented:
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
 
UncleMidriffCommented:
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
 
halfondjAuthor Commented:
I know, it doesn't make sense, but I wanted to ask the experts :).  I'll have to try it.

Thanks again.
0
 
UncleMidriffCommented:
heh...me, an expert.  Fancy that.
0
 
unknown_routineCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now