none
Recordsource for an Access-Form with DAO? RRS feed

  • Question

  • Hi, Since now, i worked with MS-Access-ADP's against SQL-Server, actually 2012. This does not work anymore withr Acces-2013.

    I'm using DAO with the sql-server-native client 11.0

    1.

    How can i set a recordsource to a Access-Form, (with dao if possible)?

    If this does not work: I didi it with the following Code in ADP's with ADO, but this does not work anymore:

     Set rst = New ADODB.Recordset
        With rst
            Set .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenStatic
            .LockType = adLockOptimistic
            .Source = "dbo.mystoredprocedure"
            .Open
        End With
        Set Me.Recordset = rst

    2. Since now, iworked with .udl-files whih was very easy to use for my clients. (just dending the file instead of configuring by ODBC. But now, i can not change the provider to the sql-server-native-client...

    Ich changed to this driver because i heard that MS will disconnet the support for ODBC and ADO...Is that whriley true??

    Thanks for help

    Monday, May 2, 2016 8:53 AM

Answers

  • Right, but you using ADO + an oleDB connection. oleDB connections to SQL server are being depreciated. So while one can use ADO, using oleDB connections is a “bad” choice for the longer term.

    As noted, if ADO through ODBC supports read/write PT queries, then that’s what I recommend.

    However, if ADO via ODBC does NOT support read/write record sets from a PT query, then I don’t recommend this approach due to oleDB be deprecated for SQL server.

    The whole reason why ADP projects in Access are going away is due to SQL server dropping support for oleDB connections. ADO "useally" works with a oleDB provider, but ADO can also use ODBC as the connection. So I have to test or research, but if ADO via ODBC and a PT query is read only, then I don't recommend building applications "assuming" that a PT query is read/write.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, May 5, 2016 4:20 PM

All replies

  • Hello Peter,

    "does not work" means what exactly? Are you getting an error message or ... ?

    as far as I know there are some mayor changes in MS Access 2013 compared to 2010; you may also ask in a MS Access forum for support, because this is not directly a SQL Server issue => https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 2, 2016 9:08 AM
  • Hi Olaf,

    Yes. 91 Object not found...

    In this case, the behavior is the same in 2010 and 2013. But the Code runs in ADP's and 2010. In Access 2013, ADP's do not work anymore...

    Peter


    Tuesday, May 3, 2016 12:49 AM
  • Hi Peter,

    As the issue is more related to Access, we move this case to Access for Developers forum. It is appropriate and more experts will assist you.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Tuesday, May 3, 2016 7:44 AM
  • A few things:

    Your ADO code can and should work, but you need to create a connection object, since currentProject.Conneciton will create a connection to your accDB file – not the SQL server in question.

    So, you have to create and save a connection string some place.

    You can use dao to call a sproc, but the results will be “read” only.

    With DAO, you could use this code:

       With CurrentDb.QueryDefs("qryPassR")

          .SQL = "SpTable1"

          Set Me.Recordset = .OpenRecordset

       End With

    In above, I assume we have a pass-through query called qryPassR.  

    However, keep in mind that any pass-through query will be READ only – so this may, or may not suit your needs. I like above since then I don’t deal with nor have connection string in code.

    So your ADO example can work, but you have to setup a connection string.

    Also, it not clear if you really need to use a store procedure, and often I create a view server side, and then simply link to that view. This performs well, and you eliminate the store proc.

    If you used a s-proc due to wanting to pass parameters, then I suggest a view of the query (without criteria), and then use the form (or report) “where” clause of the openform/report command.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Wednesday, May 4, 2016 1:53 AM
  • Hi Albert,

    Many Thanks for this helpful information!

    1.

    For "Things" when read-only is enough, I use passthrough-queriers. i create the sql and overwrite the generated query overtime, because most of the time, i need them with parameters like this:

    -----------

     Set MyQuery = db.CreateQueryDef("qryfrmStudentsChangelog")
      MyQuery.Connect = ODBCString
      MyQuery.ReturnsRecords = True
       MyQuery.sql = "SELECT tblHistory.DatensatzID, tblHistory.Datum, tblHistory.Anwender, tblHistory.Feld, tblHistory.AlterWert, tblHistory.NeuerWert FROM tblHistory WHERE tblHistory.DatensatzID = " & Me!ID & " And tblHistory.TabellenID = 1 ORDER BY tblHistory.Datum DESC;"
      Me.Nr.RowSource = "qryfrmStudentsChangelog"
       Set MyQuery = db.CreateQueryDef("qryfrmStudentsRowsource")
       MyQuery.Connect = ODBCString
       MyQuery.ReturnsRecords = True
       MyQuery.sql = "SELECT tblStudents.ID, tblStudents.[Lastname] + Space(1) + [Firstname] AS SName, tblStudents.StudentsImage FROM tblMain LEFT JOIN tblStudents ON tblMain.IDStudent = tblStudents.ID  where Left([lastname], 1) = " + "'" & Anfangsbuchstaben + "'" & "ORDER BY tblStudents.[Lastname] + Space(1) + [Firstname];"
      Me.Selektion.RowSource = "qryfrmStudentsRowsource"
       MyQuery.Close
       db.Close
    Exit Function
    Proc_err:
    If Err.Number = 3012 Then
     DoCmd.DeleteObject acQuery, "qryfrmStudentsRowsource"
     Set MyQuery = db.CreateQueryDef("qryfrmStudentsRowsource")
      Resume Next

    ---------

    What i do not likes that i have to store this query to get the record source what means that i have do delete the previous query before...I do not think its a good way to do this, but i did not find another solution till now...?

    2.

    I wanted to go away from ADO, so that means i have to use Views on the Server instead of SP's, as far as i understood...

    Of course, i could use SQL instead in my code, but would prefer to let the Server to that work in a way you described..On the other hand, the handling of many views then does not make life easier...anyway.

    What is the most performant way to get the records with read-write-options as a Recordset for a form? Are the Views faster than use a stored procedure?

    Thanks

    Peter


    Wednesday, May 4, 2016 10:49 AM
  • Actually, what you have is fine.

    However, there is no need to re-create the querfydef each time.

    In fact, you best just re-use the existing one over and over.

    If you are assigning the results to a recordset, then you can use the same pass-through query over and over everywhere in the application. In fact I create two such pass-through query

    qryPass     - set to not return records (handy for executing store procs, or t-sql commands that don’t need to return records).

    qryPassR  - set to return records.

    So I use those two everywhere in the application.

    However, for reports, or forms, you might want to base that report (or form) directly on that pass-through query, and thus it makes sense to have a separate PT query (as you have outlined).

    So in your case, I would not “create” over and over the PT query. I would create the query once and save it.

    Then in code, you use this:

    With CurrentDb.QueryDefs("qryfrmStudentsRowsource")

       .SQL = strSQL = "SELECT DatensatzID, Datum, Anwender, Feld, AlterWert, NeuerWert FROM tblHistory " & _

                       " WHERE DatensatzID = " & Me!ID & " And TabellenID = 1 ORDER BY tblHistory.Datum DESC;"

    End With

    And the “save” of the SQL in above is automatic. (so no delete, no create required).

    The other reason for above is then you NEVER need nor want connection strings in your VBA code. Your table re-link code can (should) thus re-link linked tables and ALSO re-link any pass-through query. And you can link tables without having the user ID and password in that link (so you can have users logon without have to re-link the tables).

    > What is the most performant way to get the records with read-write-options as a Recordset for a form? Are the Views faster than use a stored procedure?

    Views are not faster (or slower!). Views perform JUST as well as sprocs. And most important is views can be read/write, were as store procs are read only. So views are the only choice we have for read/write datasets.

    Using a store-proc or view is ONLY an advantage over a plain Jane regular linked table when that view is comprised of “several” tables. If it is a single linked table, then you gain nothing by adopting a view or store proc – just use the Access link to the table. Critera for the report (or form) can be supplied via the where clause, and performance will be the same without any extra work of adoption a view or sproc).

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Wednesday, May 4, 2016 5:22 PM
  • Hi Albert,

    Many thanks for the very helpful inputs.......

    i decided to use the passthrough-queries with the save-option for readonly and stored-proc's for getting recordset for read/write. I have some strange issues with Server-side queries (string on textfields do not work) on one hand, on the other my code is more transparent with so's than having tons of views on the server. And there is no difference in performance, as you noticed.

    If i fire an sp by a passthrough query, the recordset is still read-only, as far as i understood...thats why im continuing to use ADO-Code for firing sp's..

    mi idea was to go away using the ado-library just for getting read/write recordset (no need to use an additional reference in the access-frontend). 

    But it seems there is no way to do it with DAO....

    Thanks again for the very useful help!!!

    Peter 

    Thursday, May 5, 2016 3:11 PM
  • In the article in the first link that I posted, there is a sub called PassThroughFixup that simplifies updating the SQL of passthrough queries on the fly.

    Also note that for many cases, Access queries work very well as long as you don't use Access specific function and don't join to other Access queries. You can use profiler to see what Access is sending to SQL Server. For many queries, where Access can easily convert the querydef to T-SQL syntax, you will find little difference in performance. E.G. Select * FROM <yourtable> WHERE <somefield> = Forms!yourform!yourtextbox. The where clause will be included in what is sent to SQL Server.

    Additionally, queries that would be expected to be updatable are, so you can use them for bound forms.
    • Edited by Alphonse G Thursday, May 5, 2016 3:57 PM
    Thursday, May 5, 2016 3:40 PM
  • You are correct (DAO pt query = read only) As noted, since “most” of your application code is ADO, I think it is “reasonable” to stick to using ADO. While oleDB for SQL server is being depreciated, ADO should remain intact for a longer period.

    About the only thing I don’t know and not have tried is when you use ADO (via ODBC), can a store-proc be read/write). If yes, then such a choice will work when SQL server depreciates oleDB (you be using ADO with a ODBC connection, not a ole provider). If this can produce a read/write recordset from a store proc, then I would invest and write code this way - but ONLY if this works. Since over time, as oleDB goes away from SQL SERVER, if this fails, then you be forced to develop the way DAO developers + SQL server are doing now.

    As for needing a read/write from a store proc? Then as noted, you likely be forced to stick to ADO. I rare missed this ability, since I started out using linked tables, and thus never used store procs much.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, May 5, 2016 3:51 PM
  • Hi Albert

    This works (SQL-Server 11.0 Native-driver) for read/write access:

     With cmd
         .ActiveConnection = Connectstring
         .CommandText = "Recordsource_Forms_ImmatriculationFilter_Anfangsbuchstaben"
         .CommandType = adCmdStoredProc
         .Parameters.Append .CreateParameter("@Anfangsbuchstaben", adChar, adParamInput, 1, Anfangsbuchstaben)
          Set rst = .Execute
        End With

    Connect string (set manually)

    Provider=SQLOLEDB.1;Password=XXXX;Persist Security Info=True;User ID=YYYY;Initial Catalog=ZZZZ;Data Source=xxx.dyndns.org\Serverinstamce

    ODBCString

    ODBC;DSN=XXX;Description=XXXX;UID=YYYY;PWD=XXXX;DATABASE=Databasename;Network=DBMSSOCN;Address=xxxt.dyndns.org\Srverinstance,1433

    note: access via dyndns, remote-access from evryware...

    Thursday, May 5, 2016 4:13 PM
  • Right, but you using ADO + an oleDB connection. oleDB connections to SQL server are being depreciated. So while one can use ADO, using oleDB connections is a “bad” choice for the longer term.

    As noted, if ADO through ODBC supports read/write PT queries, then that’s what I recommend.

    However, if ADO via ODBC does NOT support read/write record sets from a PT query, then I don’t recommend this approach due to oleDB be deprecated for SQL server.

    The whole reason why ADP projects in Access are going away is due to SQL server dropping support for oleDB connections. ADO "useally" works with a oleDB provider, but ADO can also use ODBC as the connection. So I have to test or research, but if ADO via ODBC and a PT query is read only, then I don't recommend building applications "assuming" that a PT query is read/write.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, May 5, 2016 4:20 PM
  • hi Albert,

    ADP's do not work anymore in Access 2013, and in Access 2010, you have to set some properties manually if you want to change settings which you could change in the general properties of the application (ex. Layout view..)

    The code posted here works and comes from a accdb...still working on the dfferences concerning OLEDB etc..:-)

    Peter


    Thursday, May 5, 2016 4:33 PM
  • Hi Peter,

    It seems your issue has been resolved. If you have, I suggest you mark the helpful reply as answer to closet this thread. If not, please feel free to let us know.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 6, 2016 1:45 AM
  • by marking as answer??
    Friday, May 6, 2016 9:10 AM
  • Excellent – thank you kindly for the follow up.

    Well aware ADP's don't work, but the "worry" here is oleDB - that's being dropped from SQL server.

    If the PT query is read/write when using ODBC with ADO then I think you are home free. As noted, over time, oleDB will not work with SQL server, but if your ADO + ODBC connection string as per above works, then you shared an answer to my question, and I think that is “long” term coding approach.

    You as noted “may well” start to convert code to DAO, but I recommend for such an existing application to REMAIN as ADO. Especially if the ADO+ODBC can create read/write PT queries.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Saturday, May 7, 2016 8:53 PM