VIsual Basic with Access

What's the best way to connect a VB6 app with a Microsoft Access 2000 DataBase?
ibabicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
col_jack_onealCommented:
use a data control(tenth control on the right) but convert the 2000 file to a 97


col_jack_oneal
0
TimCotteeHead of Software ServicesCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.