Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    13

    Angry Unanswered: Need some help w/ ADO conversion

    I am converting the backend (in Access 97) for an Access 97 interface to SQL Server 8. The existing app used DAO, and I'm converting the interface to use ADO.

    When I have a form opened through a switchboard, I'm using the following code to open up the main form:

    Case conCmdOpenFormAdd ' Open a form in Add mode.
    DoCmd.OpenForm rst![Argument], , , , acAdd

    Problem is IMMEDIATELY as it executes the .OpenForm line, the SQL Server Login box pops up wanting my password (the user ID is filled in). In the Open Event of the target form, I set the forms recordsource using a DSN-less connection string, but the SQL Server message box STILL is popping up.

    With rst
    .CursorLocation = adUseClient
    .Open strSQL, gCnx, adOpenKeyset, adLockOptimistic, adCmdText
    Me.RecordSource = strSQL
    End With

    (gcnx is the global SQL Server connection string and strSQL is the record source)

    Any ideas on how I can get this pesky login box to go away ? THe problem seems to be in the Switchboard form, as the login box pops up BEFORE the formOpen event.

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I know very little of SQL server, but what youre doing here, is opening a recordset with the connection string etc... which you don't use at all, in stead you assign the sql string to the form (as recordsource). In other words, the opening of the recordset doesn't do anything but open the recordset. You assign the sql query string to the recordsource.

    If you're working with a97, I doubt you're going to have much success with binding ADO recordsets to forms. It is possible (with some hassle) in 2000, and better in 2002+ versions. The mentioned versions also have the possibility of creating adp's which bind to SQL server. In 2000+ versions you can assign the recordset to a form

    set me.recordset = rst

    but the hassle in 2000 (probably because Microsoft hadn't finished the product at the time), the form recordset isn't updateable, though it can be tweaked using the DataShape provider, see
    http://support.microsoft.com/default...b;EN-US;227053

    From what I've read, I'd say you'd probably be better off linking the tables to SQL server, and continue using DAO.
    Roy-Vidar

  3. #3
    Join Date
    Mar 2005
    Posts
    13
    Quote Originally Posted by RoyVidar
    I know very little of SQL server, but what youre doing here, is opening a recordset with the connection string etc... which you don't use at all, in stead you assign the sql string to the form (as recordsource). In other words, the opening of the recordset doesn't do anything but open the recordset. You assign the sql query string to the recordsource.

    If you're working with a97, I doubt you're going to have much success with binding ADO recordsets to forms. It is possible (with some hassle) in 2000, and better in 2002+ versions. The mentioned versions also have the possibility of creating adp's which bind to SQL server. In 2000+ versions you can assign the recordset to a form

    set me.recordset = rst

    but the hassle in 2000 (probably because Microsoft hadn't finished the product at the time), the form recordset isn't updateable, though it can be tweaked using the DataShape provider, see
    http://support.microsoft.com/default...b;EN-US;227053

    From what I've read, I'd say you'd probably be better off linking the tables to SQL server, and continue using DAO.
    Thanks - If I used DAO, I'd have to go back to using a DSN ? Or if I stayed with ADO, use a form with unbound fields and programmatically update using rst.update ?

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Sorry, I don't know, but wouldn't be surprised if you're correct.
    Roy-Vidar

Posting Permissions

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