Updating sql server from access
I’ve been seeing a lot of talk lately about linked SQL Server tables…ever since I’ve started this blog I’ve address it several times, but there have been some changes since my last diatribe that warrant revisiting the issue again. In the old days some developers would use unbound forms and elaborate code to read/write to SQL Server, and for good reasons: Linked tables will allow you to bind your forms and make it easier to design and use them, run queries in Access and use lookup tables. There are several issues you need to be aware of when using linked tables.
Choose this option knowing you'll be using old technology that may not be fully supported in the future.Unlike SQL Server, Access also offers a variety of development tools and controls for building a flexible and easy-to-use end product. In this article, we'll discuss some of the advantages of using an Access Data Project as the front end to your SQL Server relational database.Lots of developers depend on Access to build their front-end applications. Over the years, Access has proven to be a useful front end for the big databases, even though you were limited to using pass-through queries or linked tables.In addition, linked tables return all records to the application, which kind of negates one of the main reasons for using a database server (returning only the data you request).Many developers mistake Access for a true server database because you often find an Access database split into two files: one file contains the tables and the other file contains the interface objects.However, be careful, because pass-through queries are typically read-only.
When necessary, you can link Access to server tables.
All the other objects—the tables, stored procedures, views, and so on—are stored on the database server.
ADPs are strictly a Microsoft solution and, as such, won't function with any other relational database server except SQL Server—at least not directly.
Don’t do massive update queries using Access If you ever need to update a lot of records at once, say, increase pricing on all items by 10%, you’re much better off doing the update via a pass-thru query or on the server directly. Limiting data to just a fraction of records at any given time.
Don’t load more data than you need to I’ve bounded forms to tables with several million records with no problem, how? Here’s how: Say you have a table of customers called tbl Customers and that the primary key is Customer ID, to load just one customer in your frm Customers form you would use the following syntax: Where lng Customer ID is a variable holding the Customer ID you wish to see.
Now you have the Access Data Project (ADP), which is much cleaner and easier to work with.