Announcement

Collapse
No announcement yet.

Accessing databases in VB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Accessing databases in VB

    Right guys - Ages ago I wrote a few programs which accessed a database (then, MS Access 2 files, but can be any ODBC source) in VB4. However, I can't find my old source code, and I can't get my head round Microsoft's help files for VB6 for simple data access.

    I can use .ASP files to access ODBC sources no problem, but I seem to have now got a mental block.

    So, could anyone help me here and give me a bit of code for either VB6 or even VB4 which will let me do simple data access?

    (in asp vbscript, I can do it in a few lines, as follows:

    Code:
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "dsn=DNSNAME;uid=sa;pwd="
    SQLQuery = " select * from BLAH where BLAH = BLAH order by BLAH desc"
    Set RS = Conn.Execute(SQLQuery)
    Do While Not RS.EOF
    data = RS("FIELDNAME")
    RS.MoveNext
    Loop
    (That's reading data, and it's not too hard to write it too)

    So, can someone give me a snippet of code which will help me before my head explodes?

    ------------------
    Cheers,
    Steve

    "Life is what we make of it, yet most of us just fake"

  • #2
    I have 4 demos in VB6 on my ftp that explain how to work with ADO.... i can make u a copy.

    All notes and functions names are written in french tho.

    Contact me by email if you're interested.

    Spazm
    P3-667@810 retail, Asus CUSL2-C, 2*128 mb PC-133(generic), G400DH 16mb, SBLive value, HollyWood+, 1*Realtek 8029(AS) and 1*Realtek 8039C, Quantum 30g, Pioneer DVD-115f

    Comment


    • #3
      Hi Steve.

      Detail your questions a bit more, 'cause the topic is a bit large to write...
      Sat on a pile of deads, I enjoy my oysters.

      Comment


      • #4
        Hi Steve.

        Detail your questions a bit more, 'cause the topic is a bit large to write...
        Sat on a pile of deads, I enjoy my oysters.

        Comment


        • #5
          <font face="Verdana, Arial, Helvetica" size="2">Originally posted by Drizzt:
          Hi Steve.

          Detail your questions a bit more, 'cause the topic is a bit large to write...
          </font>
          Hi Drizzt,

          I want to open a database (be it an access file or any ODBC source) and read data from it using SQL statements, and also write data to it. I have VB 4 and VB6.



          ------------------
          Cheers,
          Steve

          "Life is what we make of it, yet most of us just fake"

          Comment


          • #6
            Ok. Try to be not to tough with me for my english...


            Data formats
            There are a huge numbers of database formats. Most of them are even not database.
            You can consider Jet databases (Access ones) the "native" format for VB3, VB4 and VB5.
            VB6 has not a preferred or "native" db format.


            Data access
            There are a few methods of accessing databases in VB:
            ADO, DAO, RDO, ODBC API.
            The last two are pretty useless with VB6, while there was more advantage on them with VB5.
            Generally, VB6 prefers ADO.
            What's to say is that ADO is still an immature tecnology, somehow, but it's very powerful and it's the natural choice for a certain kind of applications.
            DAO is a bit older and it's no more supported from MS. It's power stands all in Jet databases access: it's the fastest on them.

            How to choose?
            ADO for accessing all the database that are not Jet, or for Jet if you think that your apps can migrate to other DB formats or if you live programming, 'cause it's a good rule to keep time with new tecnologies.
            DAO if you plan to write an app working only for JET, or if programming speed is a major concern, or if it's a simple program wrote for an exercise or a very small customer.

            Now, as I had choosen ADO when I had to do, ask someone else for DAO.
            If you need info on ADO, well:

            1) You need a connection to your database. You connect trough a Data Provider. You can use ODBC provider to create a connection string compatible with everything, but you loose a lot in performance.
            Instead, you can place an ADo data control on a form and use it to generate an appropriate connection string to your database. Better, to learn how a connection string should be made.

            To connect, once you have understood how to build a connection string:


            Dim conConnection as ADODB.Connection

            Set conConnection = New ADODB.Connection
            conConnection.Open gstrDBConnString



            Now, let's say you have a variable, strQuery, containing your SQL query. To open the data set:


            Dim recData As ADODB.Recordset

            recNodi.Open strQuery, conDB, adOpenKeyset, adLockOptimistic


            Now recNodi is an array of records. The shape of the record is the one you have built with your query:


            If Not (recData.BOF And recData.EOF) Then
            recData.MoveFirst
            While Not recData.EOF
            ' Data are stored in recData
            recNodi.MoveNext
            Wend
            End If



            Other?
            Sat on a pile of deads, I enjoy my oysters.

            Comment


            • #7
              Hi Steve,

              here's some DAO stuff - hope it makes some kind of sense...


              =============================================
              '* opening and reading data from an Access
              '* database using DAO and an SQL query

              Option Explicit
              Dim dbRecords As Database
              Dim rsUser As Recordset
              Dim mySQL As String

              '* open an instance of an Access database >>
              '* dbRecords

              Set dbRecords = OpenDatabase(database filename and/or path in here)

              '* search the Name field of the Student
              '* table for "Simon"

              mySQL = "SELECT * FROM Student As S WHERE S.Name = 'Simon'"

              '* opens the rsUser recordset as a read only
              '* snapshot (use dbOpenDynaset for updatable
              '* recordsets) according to the previously
              '* defined SQL query

              Set rsUser = dbRecords.OpenRecordset(mySQL,
              dbOpenSnapshot)

              '* navigates the rsUser recordset and looks
              '* for a value of 100200 matching the
              '* Student_ID field of the recordset

              With rsUser
              Do While Not .EOF
              .MoveLast
              .MoveFirst
              If .Fields("Student_ID").Value = "100200" Then
              MsgBox("Good news!")
              Else
              MsgBox("Student not found")
              End If
              .MoveNext
              Loop
              End With

              =============================================
              VB6 writing to Access database using SQL update query

              '* SQL update statement which sets the
              '* yes/no field SABcheck to yes where the
              '* Module_ID field = IC501

              mySQLcheck = "UPDATE Module " & _
              "SET SABcheck = Yes WHERE Module_ID = 'IC501'"

              dbScams.Execute (mySQLcheck)

              =============================================

              I should know all this having just done it all on my PGDip course but I still had to look it up!

              Here are some links to VB tutorial sites:

              http://www.programmingtutorials.com/tutorial.asp?id=vb

              http://pc13.virtualave.net/dbtutorial.htm

              http://www.citilink.com/~jgarrick/vb...abase/dao.html

              http://visualbasic.about.com/compute...base/index.htm

              - Simon

              Comment


              • #8
                Steve,
                I only do the fancy SQL stuff around our office.
                But our WebMaster uses some of my code that ODBC can understand on our web pages.
                If you haven't got your answer by tomorrow morning (US Central time) I'll see what I can dig up for you.
                I don't know much about VB, but our WM owes me several favors
                chuck
                Chuck
                秋音的爸爸

                Comment


                • #9
                  i do reports in asp all of the time but i cheat in 2 ways.

                  first i use dreamweaver ultradev
                  second if it is not a simple sql statement i make the query in access then do the sql view and cut and paste the sql into dreamweaver ultradev

                  i have since taken the time to learn asp myself but i still just use dreamweaver ultradev if i can because it is so much faster at doing the basic stuff.

                  doing the cool stuff like datashaping i have to do by hand.

                  Comment


                  • #10
                    Thanks guys! I got a few things off Spazm and with Drizzt's info I should be OK. If not, I'll be back here

                    ------------------
                    Cheers,
                    Steve

                    "Life is what we make of it, yet most of us just fake"

                    Comment

                    Working...
                    X