?
Solved

VIsual Basic with Access

Posted on 2003-10-28
4
Medium Priority
?
183 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
[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
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 200 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

765 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