VB and SQL

[winterslam]

Hi VB gurus out there,



How do I connect VB to my database?

I've used the instrinsic Data control. But that data control is simply loading from single table in the database. It does not seem to accept any SQL commands (maybe I'm wrong)


In fact, I'm creating the replica of my PHP application in VB. So I've converted the MySQL database to Ms Access. My database has many tables which need to be manipulated by SQL (such as table joins, sorting, sub-selects).

Does VB allow this?
Can you give the links to the tutorials?

Thanks in advance.

[Moneybucks]

Use a data environment to connect to your database. Right click on your available controls and press Add component, or something to that effect, and select data environment from one of the tabs at the top.

Then in this data environment right click the connection that it creates automatically and elect properties. Select Microsoft Jet4 and fill out the path of the database in the second tab.

In runtime, use DataEnvironment.ConnectionName.Open to establish the connection, then you can declare recordsets, using SQL to define the source. This allows you to select data from different tables, but assumes that the database will always be in the same place.

Before opening the connection you can change the connectionstring if you have a database that isn't in the place it is expected, I always give the user the option of moving the database, and it's current location is stored in a text file.

An example of using recordsets:

dsrDatabase.conDatabase.open

Dim rsAddrecord as New Recordset

With rsAddrecord
.ActiveConnection = dsrDatabase.conDatabase
.Source = "SELECT * FROM Records;"
.LockType = adLockBatchOptimistic
.CursorType = adOpenDynamic
.Open
End With
rsAddrecord.Add
-Fill in fields-
rsAddrecord.Updatebatch
rsAddrecord.close

Although, you can use the insert into SQL statement in the source to do most of that for you in much less lines but I was just using the above as a basic example.

[winterslam]

Thanks bro


Are there any tutorials?
Because i'm kinda blur with database in VB although I'm good at PHP/MySQL

[si_the_geek]

Moved to Database Development forum

I'd recommend not taking Moneybucks advice - it is an extra layer of complexity (things that can go wrong!) that you don't need. It also isn't used by many people here, so you are unlikely to get rapid answers to your questions.

What I think you should use is ADO code, as shown in the ADO Tutorial link in my signature (and further articles in our Database FAQs).

[Moneybucks]

I tried a google source for VB6 Data Environment, found this, http://abstractvb.com/code.asp?A=706

Seems to be what you want.