Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VIsual Basic with Access

Posted on 2003-10-28
4
Medium Priority
?
184 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

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.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
Suggested Courses

618 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