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.
Linked tables are a great way to leverage SQL Server in your app.
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.
If you are playing along at home, that’s the link above, though this tutorial will work with your own dataset. Access Forms are customisable design objects allowing you to provide an accessible database experience for yourself and your database users. Whilst we are here, feel free to drag and drop form fields to suit your database, or right-click for deletion options.