Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    10

    Unanswered: beginner - connection and sql query

    Hello,

    I am a complete beginner with vba but I do know databases fairly well (sql server and access). I have purchased a book to try and familiarise myself with vba and I am struggling a little. I have created a new form in the northwind database with a 'northwind' ODBC conection and I have checked the ActiveX Data Objects 2.7 library item. I have used the following code (from the book) which I hoped would limit my record selection to three rows that had Canada as the country and that it would display a message box for these three rows (thats what book says it does anyway!!) and the code does read like that. I have simply placed the code against the new form. When I open the form however, nothing happens. I am well aware that I am probably doing it all wrong but can someone tell me how I get code such as this to work. Can you even do this from within access or should it be from an external program? I understand the code and exactly what it is doing but I don't know where it should be placed etc. Should it be in a specific section or should it not be placed within a form?

    The code is as follows:

    Option Compare Database

    Sub recordsetdata()
    Dim rs As Recordset
    Dim strSELECT As String
    '
    'Create the Recordset object
    '
    Set rs = CreateObject("ADODB.Recordset")
    '
    ' Open it
    '
    strSELECT = "SELECT * FROM Customers WHERE Country = 'Canada' " & _
    "Order BY CompanyName;"
    rs.Open strSELECT, "Northwind", adOpenKeyset
    '
    'Display the Contact Name and Company Name
    '
    If rs.RecordCount > 0 Then

    MsgBox rs("ContactName") & ", " & rs("CompanyName")

    End If
    '
    'Close it
    '
    rs.Close
    Set rs = Nothing
    End Sub


    Please help!!
    Last edited by thommo67; 05-22-06 at 04:58.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Do a search in this forum for ADO Recordset (or for that matter DAO) ... Right now you're trying to mix and match them together which is a BIG no-no ... Go with one or the other. THere are plenty of code examples floating around here ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2005
    Posts
    333
    Look into M Owen's warning but it if this is the only code that you have, the function is never being called. You need to place a call to the recordsetdata() function in a form event.
    When I open the form however, nothing happens
    If this is what you want, open the properties for the form and go to the events tab and select the On Open event. Select the option to to use the Code builder and place a call to your function in that event.
    Code:
    Private Sub Form_Open(Cancel As Integer)
        recordsetdata()
    End Sub

  4. #4
    Join Date
    May 2006
    Posts
    10

    Unhappy

    Thanks for the advice guys.....

    What part is not ADO then? Think I should get a refund on 'absolute beginners guide to vba' by Paul McFedries as that is the exact code he states which 1. doesnt do anything and 2. is a mix between two connection methods!!

    Campster.....I tried your advise just to try and get something to work and it says that it expects an = sign after recordsetdata(). Any ideas?

    On another note.... when I compile I am told that the rs.open part of my code has a method or data member not found. What is wrong with that part...I recognise this from vba on sql server that I have worked with before and it looks the same - is this something to do with mixing connection methods?

    Good book this - it has confused me more than I was when I started

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Change your first few lines of code to this:
    Code:
    Dim rs As NEW ADODB.Recordset
    Dim strSELECT As String
    '
    'Create the Recordset object
    '
    'The recordset object is instantiated above so no nead for this
    'Set rs = CreateObject("ADODB.Recordset")
    '
    Debug-> Compile. If it errors then you have a DAO\ ADODB problem.

    Also - how are you connecting to the db?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2006
    Posts
    10

    Wink

    Thanks pootle flump....

    I have an ODBC called Northwind.

    Starting to make sense. I put my new code behind a command button and it finally brung up a message box. It now looks like this:

    Option Compare Database

    Private Sub Command3_Click()
    Dim rs As New ADODB.Recordset
    Dim strSELECT As String
    '
    ' Open it
    '
    strSELECT = "SELECT * FROM Customers WHERE Country = 'Canada' " & _
    "Order BY CompanyName;"
    rs.Open strSELECT, "Northwind", adOpenKeyset
    '
    'Display the Contact Name and Company Name
    '
    If rs.RecordCount > 0 Then

    MsgBox rs("ContactName") & ", " & rs("CompanyName")

    End If
    '
    'Close it
    '
    rs.Close
    Set rs = Nothing
    End Sub

    I have had my suspicions that this code would only bring back the first record that met the sql criteria and it does - no way it could have let you scroll through the 3 records in recordset. Is there any way now to populate a text box on the form with say the company name .... so replace msgbox part with..... 'Forms!form1!txtcompanyname = rs("companyname")'

    I have tried this and had to take out the adOpenKeyset part as error but it doesnt do anything.

    Really what I am looking to be able to do is........ connect to the database, get a recordset based on whatever criteria (my sql is fine) and populate fields on the form throgh vb. Is this the best way to do it (now have my doubts since book seems pretty crap!!)

    Thanks

    p.s can anyone recommend a good book for doing this sort of thing

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by thommo67
    p.s can anyone recommend a good book for doing this sort of thing
    t'interweb - e.g. http://www.google.co.uk/search?hl=en...onnection&meta=
    Also - Access help actually has a pretty good ADO section you can work through (XP and 2003 and I think - not sure re 2k).

    Try
    Code:
    Me.MyTextboxName.Value = rs.Fields("CompanyName").Value
    I always use Me instead of Forms!... when you are coding on the form you are writing to as it is more efficient to run and more efficient to write (intellisense for one).

    BTW - don't give up on your book yet. I don't know it but it could be fine. My commenting out CreateObject was not because it was wrong - just to help debug. Campster picked up on the error which was one of application rather than code as such.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In general: Check out www.connectionstrings.com

    This site/page has the common connection strings for popular databases and has common connection strings for the different methodologies within each database ...

    And a final note: I've posted this exact thing at least 3 times in the freaking past. Ted, this is one for the FAQs book ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    May 2006
    Posts
    10
    Thanks guys,

    Starting to get this a bit better now. The reason i bought this book was bcause this connection and use of sql staements looked quite easy to follow. Can anyone post a link to an easy to understand piece of vba that connects to the database (ado/odbc), uses a sql string to limit recordset and poplulates values on a form based on the results. This I think would provide me with a solid understanding (and data set) that I can use throughout my learning.

Posting Permissions

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