Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>About your question:</p> <blockquote> <p><em>I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key. This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.</em></p> </blockquote> <p>If you <strong>are</strong> using an AutoIncrement for your primary key, then you have uniqueness and you could use <code>SELECT @@Identity;</code> to get the value of the last autogenerated ID (see caveats below).</p> <p>If you are <strong>not</strong> using autoincrement, and you are inserting the records from Access but you want to retrieve the last one from Excel:</p> <ul> <li><p>make sure your primary key is sortable, so you can get the last one using a query like either of these:</p> <pre><code>SELECT MAX(MyPrimaryField) FROM MyTable; SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY MyPrimaryField DESC; </code></pre></li> <li><p>or, if sorting your primary field wouldn't give you the last one, you would need to add a DateTime field (say <code>InsertedDate</code>) and save the current date and time every time you create a new record in that table so you could get the last one like this:</p> <pre><code>SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC; </code></pre></li> </ul> <p>In either of these cases, I think you would find adding an AutoIncrement primary key as being a lot easier to deal with:</p> <ul> <li><p>It's not going to cost you much</p></li> <li><p>It's going to guarantee you uniqueness of your records without having to think about it</p></li> <li><p>It's going to make it easier for you to pick the most recent record, either using <code>@@Identity</code> or through sorting by the primary key or getting the <code>Max()</code>.</p></li> </ul> <p><strong>From Excel</strong></p> <p>To get the data into Excel, you have a couple of choices:</p> <ul> <li><p>create a data link using a query, so you can use the result directly in a Cell or a range.</p></li> <li><p>query from VBA:</p> <pre><code>Sub GetLastPrimaryKey(PrimaryField as string, Table as string) as variant Dim con As String Dim rs As ADODB.Recordset Dim sql As String con = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _ "Data Source= ; C:\myDatabase.accdb" sql = "SELECT MAX([" &amp; PrimaryField &amp; "]) FROM [" &amp; MyTable &amp; "];" Set rs = New ADODB.Recordset rs.Open sql, con, adOpenStatic, adLockReadOnly GetLastPrimaryKey = rs.Fields(0).Value rs.Close Set rs = Nothing End Sub </code></pre></li> </ul> <p><strong>Note about <code>@@Identity</code></strong></p> <p>You have to be <a href="http://www.accessmonster.com/Uwe/Forum.aspx/access/8107/Select-Identity" rel="noreferrer">careful of the caveats</a> when using <code>@@Identity</code> in standard Access databases(*):</p> <ul> <li><p>It only works with AutoIncrement Identity fields.</p></li> <li><p>It's only available if you use ADO and run <code>SELECT @@IDENTITY;</code></p></li> <li><p>It returns the latest used counter, <a href="http://msdn.microsoft.com/en-us/library/bb208866.aspx" rel="noreferrer">but that's for <strong>all tables</strong></a>. You can't use it to return the counter for a specific table in MS Access (as far as I know, if you specify a table using <code>FROM mytable</code>, it just gets ignored).<br> In short, the value returned may not be at all the one you expect.</p></li> <li><p>You must query it straight after an <code>INSERT</code> to minimize the risk of getting a wrong answer.<br> That means that if you are inserting your data at one time and need to get the last ID at another time (or another place), it won't work.</p></li> <li><p>Last but not least, the variable is set only when records are inserted through programming code.<br> This means that is the record was added through the user interface, <code>@@IDENTITY</code> will not be set.</p></li> </ul> <p>(*): just to be clear, <code>@@IDENTITY</code> behaves differently, and in a more predictive way, if you use ANSI-92 SQL mode for your database.<br> The issue though is that ANSI 92 has a slightly different syntax than the ANSI 89 flavour supported by Access and is meant to increase compatibility with SQL Server when Access is used as a front end.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to get id of newly inserted record using Excel VBA?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRenaud Bompuis
    1. USRenaud Bompuis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to get id of newly inserted record using Excel VBA?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO"SELECT MAX" smells real bad. While it will work in circumstances described, as in all cases things change over time, and before you know it this code will fail! Dont make smelly code, think about the next guy
      singulars
      1. PO
      1. USTFD
    2. CO@TFD: agreed on principle, but there are not that many ways to achieve what the poster wants, at least in MS Access. It's always going to be a trade-off. Max() will work OK for most data types, including strings, although I agree that there are probably cases where you may not get what you want.
      singulars
      1. PO
      1. USRenaud Bompuis
    3. COThanks for the detailed answer, and for the helpful caveats about @@IDENTITY - I'd vote up if I could.
      singulars
      1. PO
      1. USLunatik
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload