We help IT Professionals succeed at work.

How to open and edit SQL database with VB6

HESUS
HESUS asked
on
Hello Experts.
I need to open and edit an SQL database using VB6.
Please show me how to start.
Thank you.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
that is a big/vaste question...

what database exactly?
what do you mean by "edit"?
Tech Lead Web-Development
Commented:
Maybe this will help:
http://www.programmersheaven.com/download/35539/download.aspx

And here you will find some mor connction string examples:
http://www.connectionstrings.com/

Author

Commented:
Thanks.

The Database I need to edit is "*.Sql".
EDIT is to be able to read and write to the Database.

DarthSonic , Thanks I am going to check it now.

Commented:
Here is an example for you.
    Dim conSQLServerDatabase As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    
    'conSQLServerDatabase = Server Name, Database Name, Windows Authentication
    conSQLServerDatabase.connectionstring = "Server=MyServer;Database=DatabaseName;Integrated Security=yes"
    
    'Opens the Connection
    conSQLServerDatabase.open
       
    With RS
        
        .open "INSERT Into YouTableGoesHere(Name, Status) VALUES('John', 'A')", conSQLServerDatabase, adOpenDynamic, adLockOptimistic
        .open "UPDATE YouTableGoesHere SET Status = 'B' Where Name = 'John'", conSQLServerDatabase, adOpenDynamic, adLockOptimistic
        .open "DELETE FROM YouTableGoesHere WHERE Status ='B'", conSQLServerDatabase, adOpenDynamic, adLockOptimistic
        .open "SELECT * FROM YouTableGoesHere WHERE Status = 'B'", conSQLServerDatabase, adOpenDynamic, adLockOptimistic
        
        .Close
    
    End With
    
    conSQLServerDatabase.Close
End Sub

Open in new window

Commented:
When you add, edit, remove data from your database you will normally do it by executing SQL commands.  Normally a RecordSet is what you will be working with.  You will send a request to the database and you will receive a collection of records that fall within the paramaters of what you specify.  This is your RecordSet.

When you see * you are asking for everything.
Select * From Customer (Asks for every field in the Customer Table)
Select LastName, FirstName, Status, City From Customer (Asks for only LastName, FirstName... from Customer Table.  If there are more fields in the database you will not see them in your RecordSet).

You can also specify a range.
Select * From Customer Wher LastName = "Smith" (Will give you a RecordSet containing every field of the table and a record for how ever many people in the table with a  Last Name of Smith).

After you have a connection to your database, send a request for data (SQL Statement), and receive an answer (RecordSet) it is up to you to do whatever you want with the data.

If you want a RecordCount you will need to execute two commands: rs.MoveLast and rs.MoveFirst rs is the reference to your RecordSet.  After you do that you can urse rs.RecordCount to know how many records fell within your criteria.

If you want to populate something or do something with each record normally people will create a loop:
Do while Not rs.EOF
    textbox1.text = textbox1.text & Trim(rs!Last) & vbCRLF
    rs.MoveNext
Loop

EOF is the End Of File marker.  When you reach this record you know that you are at the end of the database.  If the recordset only contains one record EOF, the do loop is skipped because there are no records.

There are different ways to Open the database (Dynaset, Static etc...) that will put your recordset in different modes.  ReadOnlly is faster but you can't update.  Read/Write will let you update but could be a little slower.

When you open your connection you can also specify if you want network locking of the data which is used when you have multiple users accessing the same database.

Thanks
Roger

I found a website with more detailed information:
http://www.classanytime.com/mis333k/sjdaoadorecordsets.html

Author

Commented:
Many thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.