none
MS Access form with SQL Server Database RRS feed

  • Question

  • Can someone provide me with an example of a ms access form connecting to a SQL Server database that does Add/edit/delete/save from code and not linked tables or macros?

    Thank you

    Monday, November 4, 2019 8:27 PM

Answers

  • This would require an unbound form, and code behind the various buttons to run code against SQL Server to do the work of the CRUD operations. In such case one typically uses stored procedures, that accept the field values as arguments. Those sprocs would be invoked from ADO or DAO code. ADO may be slightly better in this case because you can set up a strongly typed Parameter for each argument, which prevents SQL injection.

    Using the keywords I used above it should not be hard to find online examples.

    Note that with a fully unbound system you lose a lot of the magic of rapid application development with Access.


    -Tom. Microsoft Access MVP

    • Marked as answer by JHarding08 Tuesday, November 19, 2019 6:34 AM
    Monday, November 4, 2019 8:51 PM

All replies

  • This would require an unbound form, and code behind the various buttons to run code against SQL Server to do the work of the CRUD operations. In such case one typically uses stored procedures, that accept the field values as arguments. Those sprocs would be invoked from ADO or DAO code. ADO may be slightly better in this case because you can set up a strongly typed Parameter for each argument, which prevents SQL injection.

    Using the keywords I used above it should not be hard to find online examples.

    Note that with a fully unbound system you lose a lot of the magic of rapid application development with Access.


    -Tom. Microsoft Access MVP

    • Marked as answer by JHarding08 Tuesday, November 19, 2019 6:34 AM
    Monday, November 4, 2019 8:51 PM
  • Would it be better to use linked tables to SQL Server?
    Monday, November 4, 2019 9:14 PM
  • Usually, I find it better to link to the table and create a form. The only time I use unbound forms and ADO to do the heavy lifting is when the data manipulation is complex or the number of rows involved is very big.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, November 4, 2019 9:42 PM
  • Can someone provide me with an example of a ms access form connecting to a SQL Server database that does Add/edit/delete/save from code and not linked tables or macros?

    Hi JHarding08,

    I am very interested in the question behind your question. Or in other words what have you in mind to realize?

    With VBA almost all is possible, so I guess also what you have in mind. Did you consider "late binding"?

    Imb.

    Monday, November 4, 2019 10:33 PM
  • Huh? The premise of your question was to NOT use linked tables. That's how I responded. I was assuming you had a REALLY GOOD reason for not using linked tables, and were not just looking for a recommendation or a comparison. Maybe you can clarify your actual intent?

    -Tom. Microsoft Access MVP

    Monday, November 4, 2019 11:56 PM