Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Complete Database n00b.

    Hey everyone!

    Im an experienced Visual Basic programmer but have never ventured into the world of Access Databasing. But I have a task that ive got to complete and would be great if you guys could help me out.
    What I have to do is Execute a Query and then save the database again. Thats the only bit I need help with as everything else I can do with ease. Is there anywhere I can read up on it? Thanks for your time shabba.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I suggest that you check out this tutorial at a sister-site: ADO Tutorial
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2006
    Posts
    2
    Yep - I have read it, but it doesnt explain anything on querying... Also I use SQL and Oracle so I have a ok knowledge on the two.

  4. #4
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    Hey Josh,

    you'd open the ADODB connection to your DB using the following (for example)
    Code:
    Dim DBConnection As ADODB.Connection
    Set DBConnection = New ADODB.Connection
    DBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y:\bills.mdb"
    DBConnection.Open
    Dim DBRecordSet As ADODB.Recordset
    The location of the database is in the connection string line (... Data source = y:\bills.mdb")

    you then create your SQL query and attach it to a string variable, the example below uses a variable from the VB script to limit the results returned
    Code:
    SQLcode = "Select * from Payments where [payment ID]=" & payment id & ";"
    Then set the recordset to the SQL query, which the ADODB connection can execute against the database
    Code:
    Set DBRecordSet = DBConnection.Execute(SQLcode)
    Hope that solves your problem.

    5p0ng3b0b

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If the query will return a set of data, (a SELECT query, for instance,) then pass to the query to the recordset.open method

    Code:
    ' Declare/Instanciate ADO connection and recordset, Open the ADO connection.  Then...
    strSQL = "Select Field1, Field2 from Table1 Where Field1 is not Null"
    rs.Open strSQL, Conn
    ' NOW, you can iterate through the recordset.
    Do WHile not rs.EOF
      debug.Print rs.Fields!Field1, rs.Fields!Field2
      rs.MoveNext
    Loop
    rs.Close
    set rs = nothing
    If the query will NOT return data (An UPDATE, INSERT, or DELETE query, for instance, or any DDL queries,) you don't need to (or want to) instanciate a recordset for this. You just need to execute the command. So, issue a command to the ado connection.

    assuming the open connection Conn,
    Code:
    strSQL = "DELETE FROM TestTable WHERE Field1 Is Null"
    Conn.Execute strSQL
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    I'm sure thats pretty much what I said never mind.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Was explaining when to use a recordset and when not. Plus, using the rs.open method.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    ah cool, cheers for expanding.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •