Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a subroutine to insert all of this data into an Access database. What I'm struggling with is working out how to use SQL to INSERT
all of the data.
There are 20 tables, and the main table is called Entry
, which all the other tables link back to. To give a simplified example of the data structure, there is also a Keyword
table, which links back to the Entry
. And there is the Keyword_Priority
table, which links back to Keyword
. Both relationships are one-to-many, as below:
My question
I can use SQL INSERT
for the Entry
table to create one record. But how would I then create a record for the Keyword
table, referring to the primary key of the Entry
record I've just created? And how would I then create a record for Keyword_Priority
, referring to the Keyword
record? And so on.
This is all going to be done through VB.NET – I can't use Forms because there are 180,000+ entries to input. Is there any way to store a variable in SQL that I can refer to later?
Best Answer
you can also do it like here https://stackoverflow.com/questions/4135437/get-last-insert-id-from-access
by making use of @@identity
which will return the latest created ID for the database connection
OR make use of the "inbound" property that most id-columns are simply growing int values - so