Add New in Jet, the auto increment column is available before the record is updated. The new value of the identity column new value is available only after saving the new record.Resolution: Run the following Visual Basic for Applications (VBA) code before accessing the identity field: Cause: When you add a record to a SQL Server or SQL Azure table by using VBA, if the table's unique index field has a default value and you do not assign a value to that field, the new record does not appear until you reopen the table in SQL Server or SQL Azure.
He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73.
The first one is NEVER use a combination of linked tables with local tables in your queries.
Instead download the server side tables as temp tables in your app or upsize your local tables to SQL Server and do the processing on the server in it’s entirety.
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.