Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    3

    Question Unanswered: Can't add new record to subform bound to ADO recordset

    I'm new designing DB's so please bare with me. I have Access 2010 (front end) and SQL Server 2008 Express Edition (back end). I'm using ADO recordsets to populate a form and subform that I need to use for updates in the data. The thing is that I need to be able to add new records to the subforms, but these subforms only have records created before, and I don't get the blank (new line) record. Is there a way to get this line or is it impossible to get with ADO recordset linked forms?

    Here is what I'm doing with the Open Event of the form:


    Dim rs As adodb.Recordset
    Dim cn As adodb.Connection
    Dim rsSF As adodb.Recordset
    Dim sql As String
    sql = "Select * from tblFacturacion where IDFactura = 'FNCF-000000000023'"
    Set cn = New adodb.Connection
    Set rs = New adodb.Recordset
    Set rsSF = New adodb.Recordset
    cn.Open strCon
    rs.CursorLocation = adUseClient
    rs.Open sql, cn, adOpenKeyset, adLockOptimistic
    Set Me.Recordset = rs
    Me.txtID.ControlSource = "IDFactura"
    sql = "Select * from tblFactAntip where NoFactura = 'FNCF-000000000023'"
    rsSF.Open sql, cn, adOpenKeyset, adLockOptimistic
    Set [Forms]![frmprueba]![sfrmPrueba].[Form].Recordset = rsSF
    Set rs = Nothing
    Set cn = Nothing

    Thank you for the help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Recordset object that's "natively" part of a form is a DAO.Recordset. Although you can assign an ADODB Recordset to a form, you lose several functionalities of this form while doing so.

    Moreover, an ADODB Recordset can or cannot be updatable, depending on several factors that will be too long to expose here.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I believe that when using SQL Server as a Back End the Table must have a Primary Key in order for Records to be added. Does your Table have a PK?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Missinglinq View Post
    I believe that when using SQL Server as a Back End the Table must have a Primary Key in order for Records to be added
    You're right, Linq: It does not have to be the PK in the SQL Server database, but there must be a column, or a combination of columns, that uniquely identify each row, i.e. an Identity column(s).

    Back to the original problem, consultoriasigt, I was wondering if you had specific reasons to use an ADODB recordset rather than connect the form to a pass-through query or a linked table, or use a DAO recordset ?
    Have a nice day!

  5. #5
    Join Date
    May 2012
    Posts
    3
    Thanks Linq, I do have the PK (or unique identifier). I've read some books where they specify the problems with network trafic if I just use linked tables, and I wanted to avoid this because my form has a lot of subforms. In the case of DAO, I read that for SQL Server, the correct thing to do was to use ADO, and that DAO was obsolete, am I misinformed? Like I said, I'm kind of new with the DB development, and I'm trying to improve my knowledge creating this DB.

    In any case, I'm not sure how to connect to the SQL Server using DAO, I'm not sure how DAO works exactly. I do know how to do the string connection for ADO, but DAO works differently.

    Sorry if my English is troublesome, I'm a little rusty.


    Quote Originally Posted by Sinndho View Post
    You're right, Linq: It does not have to be the PK in the SQL Server database, but there must be a column, or a combination of columns, that uniquely identify each row, i.e. an Identity column(s).

    Back to the original problem, consultoriasigt, I was wondering if you had specific reasons to use an ADODB recordset rather than connect the form to a pass-through query or a linked table, or use a DAO recordset ?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by consultoriasigt View Post
    In the case of DAO, I read that for SQL Server, the correct thing to do was to use ADO, and that DAO was obsolete, am I misinformed?
    As far as Access is concerned, you are (misinformed): MS changed its mind about DAO and ADO between 2000 and 2003. DAO was, is, and probably will remain, the "native" data access library for Access, at least until COM+ is dropped and everything turns into something .NET.

    Quote Originally Posted by consultoriasigt View Post
    In any case, I'm not sure how to connect to the SQL Server using DAO, I'm not sure how DAO works exactly. I do know how to do the string connection for ADO, but DAO works differently.
    It's not as different as it seems to be.

    ADO connection string:
    Code:
    driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;
    DAO connection string:
    Code:
    ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;
    Have a nice day!

  7. #7
    Join Date
    May 2012
    Posts
    3
    Thank you so much Sinndho, I will try this solution with my DB.

    Best regards!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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