Linked tables are a great way to leverage SQL Server in your app.
To add the values to this field, create an Update query with the Field set to “ID Plus One” and the Update To line set to .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.Do design your SQL Server tables for optimum performance with Access Make sure you follow my guidelines for designing SQL Server tables with Access: If you store your password with your linked tables you’re just asking for it if it falls into the wrong hands.But when you import that data into Access, you need to add that duplication to each record so you can connect the data and summarize it.Figure 1 shows a copy of the Balance Sheet table adjusted so that it contains missing data similar to the situation described.Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.Ask yourself, "Would it be wrong if my field contained a value different from the calculation? If the difference would be meaningful, you do need the field.If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.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.For example, if you have fields named Quantity and Unit Price, how do you get Access to write Quantity * Unit Price to another field called Amount? " Calculated columns are part of life on a spreadsheet, but do not belong in a database table.Never store a value that is dependent on other fields: it's a basic rule of normalization.