Wie man die id des neu eingefügten Datensatzes mit Excel VBA?
Scheint eine gemeinsame genug problem, aber die meisten Lösungen beziehen sich auf die Verkettung mehrerer SQL-Befehle, was ich nicht glauben kann, nicht mit ADO/VBA (bin ich froh, gezeigt werden falsch in diesem Zusammenhang allerdings).
Ich derzeit legen meine neuen Datensatz führen Sie eine select-Abfrage mit (ich hoffe) genug Felder, um zu garantieren, dass nur die neu eingefügten Datensatz zurückgegeben werden können. Meine Datenbanken sind auf die selten zugegriffen wird, die von mehr als einer person gleichzeitig (vernachlässigbares Risiko von einem anderen insert-geschehen zwischen Abfragen) und aufgrund der Struktur der Tabellen, die Ermittlung der neue Datensatz ist normalerweise ziemlich einfach.
Ich bin jetzt versuchen, ein update einer Tabelle, die nicht viel Spielraum für Einzigartigkeit, anders als in den künstlichen Primärschlüssel. Dies bedeutet, es besteht die Gefahr, dass der neue Datensatz nicht eindeutig sein kann, und ich bin verabscheuen, um ein Feld hinzuzufügen, nur um Kraft, Einzigartigkeit.
Was ist der beste Weg zum einfügen eines Datensatzes in eine Access-Tabelle Abfragen dann der neue Primärschlüssel aus Excel in dieser situation?
Danke für die Antworten. Ich habe versucht, Sie zu bekommen @@IDENTITY
arbeiten, dies aber stets 0 zurück, mit dem code unten.
Private Sub getIdentityTest()
Dim myRecordset As New ADODB.Recordset
Dim SQL As String, SQL2 As String
SQL = "INSERT INTO tblTasks (discipline,task,owner,unit,minutes) VALUES (""testDisc3-3"",""testTask"",""testOwner"",""testUnit"",1);"
SQL2 = "SELECT @@identity AS NewID FROM tblTasks;"
If databaseConnection Is Nothing Then
createDBConnection
End If
With databaseConnection
.Open dbConnectionString
.Execute (SQL)
.Close
End With
myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly
Debug.Print myRecordset.Fields("NewID")
myRecordset.Close
Set myRecordset = Nothing
End Sub
Irgendetwas hervor verantwortlich?
Angesichts der Vorbehalte freundlicherweise geliefert von Renaud (unten) es scheint fast so viel Risiko bei der Nutzung von @@IDENTITY
als mit jeder anderen Methode, so habe ich gegriffen, um mit SELECT MAX
für jetzt. Für zukünftige Referenz, aber ich wäre interessiert zu sehen, was ist falsch mit meinem Versuch oben.
- Mit
Max
kann tückisch sein... wenn jemand anderes das einfügen von Datensätzen, kann es geben, Sie einem anderen Datensatz-ID. Wenn du der einzige bist, der das einfügen von Datensätzen, gehen Sie vor. - Eine kleine Anregung ist, dass es im Allgemeinen besser, die Praxis zu verwenden SCOPE_IDENTITY() statt @@IDENTITY - in dieser Weise, werden Sie abgedeckt werden sollten arbeiten in einer multi-user-Umgebung, jetzt oder irgendwann in der Zukunft.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Über Ihre Frage:
Wenn Sie sind mit einem AutoIncrement für den Primärschlüssel, dann haben Sie Einzigartigkeit und könnten Sie
SELECT @@Identity;
um den Wert der letzten automatisch generierten ID (siehe Einschränkungen unten).Wenn Sie nicht autoincrement verwendet wird, und Sie das einfügen der Datensätze aus Access, aber Sie abrufen möchten, und das Letzte aus Excel:
stellen Sie sicher, dass Ihre primary key ist sortierbar, so können Sie sich die Letzte mit einer Abfrage wie der folgenden:
oder, wenn das Sortieren Ihre primäre Feld würde nicht geben Ihnen den letzten, würden Sie brauchen Sie, um ein DateTime-Feld (sagen
InsertedDate
) und speichern Sie das aktuelle Datum und die Zeit jedes mal, wenn Sie erstellen einen neuen Datensatz in die Tabelle, so könnte man das Letzte, einer wie dieser:In beiden Fällen, ich denke, Sie finden würde hinzufügen eines AutoIncrement primary key als viel einfacher zu handhaben:
Geht es nicht kostet Sie viel
Es wird garantieren Ihnen die Einzigartigkeit Ihrer Unterlagen, ohne nachzudenken
Es geht, um es einfacher für Sie zu Holen den letzten Datensatz, entweder mit
@@Identity
oder durch Sortierung nach dem Primärschlüssel oder dieMax()
.Von Excel
Um die Daten in Excel haben Sie ein paar Möglichkeiten:
erstellen Sie eine data link mithilfe einer Abfrage, so können Sie das Ergebnis direkt in eine Zelle oder einen Bereich.
Abfrage aus VBA:
Hinweis zu
@@Identity
Müssen Sie vorsichtig die Vorbehalte bei der Verwendung
@@Identity
im standard-Access-Datenbanken(*):Funktioniert es nur mit AutoIncrement Identity-Felder.
Es ist nur verfügbar, wenn Sie ADO verwenden, und führen Sie
SELECT @@IDENTITY;
Es gibt den letzten Zähler verwendet, aber das ist für alle Tabellen. Sie können nicht es verwenden, um zurück den Zähler für eine bestimmte Tabelle in MS Access (soweit ich weiß, wenn Sie angeben, dass eine Tabelle mit
FROM mytable
, es wird einfach ignoriert).Kurz gesagt, der Wert, der zurückgegeben wird, möglicherweise nicht auf alle die, die Sie erwarten.
Müssen Sie die Abfrage direkt nach einer
INSERT
zu minimieren das Risiko des Erhaltens einer Antwort ist falsch.Das bedeutet, dass wenn Sie das einfügen Ihrer Daten zu Zeit und müssen um die Letzte ID zu einem anderen Zeitpunkt (oder eine andere), funktioniert es nicht.
Nicht zuletzt, die variable wird nur gesetzt, wenn Datensätze eingefügt durch Programmierung von code.
Dies bedeutet, dass der Datensatz Hinzugefügt wurde, durch die Benutzer-Schnittstelle
@@IDENTITY
wird nicht eingestellt werden.(*): nur um klar zu sein,
@@IDENTITY
verhält sich unterschiedlich, und in einer mehr vorausschauenden Weg, wenn Sie ANSI-92 SQL-Modus für Ihre Datenbank.Das Problem ist aber, dass die ANSI 92-hat eine etwas andere syntax als
der ANSI-89-Aroma unterstützt durch den Zugang und soll zur Erhöhung der Kompatibilität mit SQL Server, wenn Access verwendet wird als front-end.
Wenn die künstliche Schlüssel (AutoWert, können Sie die Verwendung von @@identity.
Beachten Sie, dass mit diesen beiden Beispielen, die Transaktion ist isoliert von anderen Veranstaltungen, so dass die Identität zurückgegeben ist das eben eingefügt. Sie können dies testen, indem Sie anhalten, den code zu Debuggen.Print db.RecordsAffected-oder Debug.Drucken lngRecs und einfügen eines Datensatzes manuell in Tabelle1, fahren Sie den code, und beachten Sie, dass die Identität zurückgegeben ist nicht so, dass der Eintrag manuell eingefügt, aber von den vorherigen Datensatz eingefügt durch code.
DAO-Beispiel
ADO-Beispiel
Re: "ich habe versucht @@IDENTITY arbeiten, dies aber stets 0 zurück, mit dem code unten."
Den code sendet
SQL
undSQL2
durch verschiedene Verbindungs-Objekte. Ich glaube nicht, dass@@identity
zurückkehren wird etwas anderes als null, es sei denn du fragst aus der gleichen Verbindung, wo Sie ausgeführt, IhreINSERT
- Anweisung.Versuchen Sie dies:
zu:
Hier ist meine Lösung, die nicht @@index oder MAX.
Genießen!
Versuchen Sie folgenden makro-code.Zuerst fügen Sie eine command-Schaltfläche, um das Blatt von der control-box und fügen Sie folgende codes im code-Fenster
8 Jahre zu spät zur party... Das problem ist, dass Sie mit dbConnectionString zu erstellen neue Verbindung. @@identity ist spezifisch für die Verbindung, die Sie verwenden.
Ersten, schließen Sie nicht die ursprüngliche Verbindung
ersetzen
mit der Verbindung, die Sie zuvor für die insert -
und Sie hätten schon alle gesetzt. In der Tat, Sie nicht sogar brauchen, um die Tabelle angeben: