Solved

VIsual Basic with Access

Posted on 2003-10-28
4
180 Views
Last Modified: 2013-11-25
What's the best way to connect a VB6 app with a Microsoft Access 2000 DataBase?
0
Comment
Question by:ibabic
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
ID: 9634330
Hi ibabic,

Using ADO, add a reference to Microsoft ActiveX Data Objects 2.x library to your Project then:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Myfolder\mydb.mdb;"
cnn.Open
'Now you have a connection so lets get some data
Set rst = New ADODB.Recordset
rst.Open "Select * From MyTable",cnn,adOpenStatic,adLockOptimistic
With rst
  .MoveFirst
  For intField = 0 To .Fields.Count - 1
    strMessage = strMessage & .Fields(intField).Name & " = " & .Fields(intField).Value & vbLf
  Next
End With
Msgbox strMessage 'Display the contents of the first record
'Close and destroy the objects used to free resources.
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Obviously this is a very basic starter, the ADO objects and methods give you wide access to the database and allow you to use access compatible SQL statements to retrieve/update/insert/delete data.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 

Expert Comment

by:col_jack_oneal
ID: 9679380
use a data control(tenth control on the right) but convert the 2000 file to a 97


col_jack_oneal
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9679562
I hate to say it but that solution is about the "worst" way rather than the "best". Firstly there is no need to use a control at all, using code is far more flexible. Secondly, reverting to access 97 just means that you have to re-engineer your whole project and also means that you cannot use later versions of access. Thirdly, there is an upgrade to the data control with VB SP4 or higher that allows you to use access 2000; as a source in a data control if you really have to use it. Fourthly, using the data control means you are using DAO which is an outdated method. ADO is much preferred especially if you want to be able to port the application to another database at a later time.

I don't wish to start a flame war here, however using a data control is not the way to go!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

839 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