Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: Lost in using Access form to display SQL server data

    I am new to reading records off a MS SQL server using MS Access.
    My PC is XP running Access 2000, connected to SQL 2000 server via a ethernet network. I need to read data from the server and display on the form under MS Access.

    I used to able to do it within MS Access alone using the following code with [catsearch] as a combo box. Selecting the field under [catsearch] will cause the form to fill up with the needed information.

    Appreciate if someone could advise me with the proper code how to do achieve the same result via SQL server.

    Thank you very much.
    Lost in Access

    Following is within Access only & works fine....

    Private Sub Command32_Click()
    Dim db As Database, Tb As Recordset
    Dim Check As Integer

    Set db = CurrentDb()
    Set Tb = db.OpenRecordset("f")
    Check = 0

    Do Until Tb.EOF

    If Me![catsearch] = Tb![Category] Then
    Me![Part Number] = Tb![Part Number]
    Me![Description] = Tb![Description]
    Me![Model] = Tb![Model]
    Me![Power Rating] = Tb![Power Rating]
    Me![Brandufacturer] = Tb![Brandufacturer]
    Me![Supplier] = Tb![Supplier]
    Me![Unit Price] = Tb![Unit Price]
    Me![Used With] = Tb![Used With]
    Me![Balance Qty] = Tb![Balance Qty]
    Me![Store Location] = Tb![Store Location]
    Me![Remark] = Tb![Remark]
    Check = 1
    Exit Do
    End If
    Tb.MoveNext

    Loop

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Luke

    Welcome to the forum.

    Check out ADO in help - you can do the same with this but it is better suited to working with SQL Server. Use this for easy-to-use connection info.

    Also - I would very strongly advise filtering the records (and the columns) to return only those you require rather than returning the entire table and looping through a recordset.

    e.g.
    Code:
     Set Tb = db.OpenRecordset("SELECT MyCol1, MyCol2 FROM f WHERE Category = '" & Me.catsearch & "'")
    assuming category is text rather than numeric. Knock out the apostrophes (') otherwise

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - you need to be very careful if you end up mixing DAO and ADO in a database - ideally you shouldn't at all.

    Before you reference the ADO library, try running a find and replace on all the code in your project. CHange "as Recordset" to "as DAO.Recordset". Same with DAO field and DAO property. I think that is it for objects that are named the same in DAO and ADO.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Fully agree with Pootle's suggestion that you minimise the stuff brought down from the server. DAO works well enough with MSSQL for me tho.

    DAO-how example - server database "myDB" fills unbound form fields from a one-record snapshot using an sp "mySP" on the server with one parameter plucked from myFormField.
    in-line SQL returning multiple records would be similar.

    replace myConnectionString, myDB, mySP, myFormField to suit your stuff.
    Code:
        dim dabs as dao.database
        dim recs as dao.recordset
        dim qdef as dao.querydef
        dim rfld as dao.field
        set dabs = currentdb
        set qdef = dabs.createquerydef("") 'using a temporary querydef
        qdef.connect = myConnectionString
        qdef.returnsrecords = true
        qdef.sql = "USE myDB EXEC mySP " & me.myFormField
        set recs = qdef.openrecordset(dbopensnapshot)
        with recs
                for each rfld in recs.fields
                    me(rfld.name) = rfld
                next
        end with
    you will also need error handling, a check for <>1 records, and something to close/Nothing the objects.

    izy
    Last edited by izyrider; 11-30-05 at 09:24. Reason: ooops: forgot to dim recs
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2005
    Posts
    3
    I am thankful to Pootle & Izyrider for the replies.
    I am trying them out. Thanks for the suggetsion to use a filter.

    After changing to DAO.database & the rest, there was a message :
    "compile error : User-defined type not defined"

    Under Access, I could see the linked tables but I simply could not read them into the form field.

    Any further help is appreciated.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Luke

    This might be a bit late now but I should have said make a copy before doing this so you can roll back.

    If you select Debug--> Compile the offending line will be highlighted. What does that line say (please copy and paste so that it is an exect copy)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Posts
    3

    Hello Pootle,

    Thanks Pootle.

    I did not manage to revert the project to take a snapshot of the full error message.

    I started afresh and managed to read the selected fields to the form using ADODB.

    These fields are read only.
    Wonder if anyone could advise how to assign a value to these fields so that i can do some calculation or compare it with a string to output a result via a text box.

Posting Permissions

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