• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

VIsual Basic with Access

What's the best way to connect a VB6 app with a Microsoft Access 2000 DataBase?
0
ibabic
Asked:
ibabic
  • 2
1 Solution
 
TimCotteeCommented:
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
 
col_jack_onealCommented:
use a data control(tenth control on the right) but convert the 2000 file to a 97


col_jack_oneal
0
 
TimCotteeCommented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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