How to use a Junction table

[June7]

I did see the first image showing table relationships. The second image is not something I am familiar with. It appears to be showing associated table adapter objects/properties/whatever. So query does not 'reside' in table, table is simply used within a query. Symantics

Code maybe something like:

strSQL = "SELECT tblSourceMaster.intSiTechID, tblSourceMaster.strVendorName,tblSourceMaster.strVendorID " & _
     "FROM ((lnkItemVendor " & _
     "INNER JOIN tblItemMaster ON tblItemMaster.intSiTechID = lnkItemVendor.intSiTechItemID) " & _
     "INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTechID = lnkItemVendor.intSiTechVendorID) " & _
     "WHERE (lnkItemVendor.intSiTechItemID = " & Me!intSiTechID
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = strSQL

At least I would construct SQL statement referencing form control/field for dynamic input like that in VBA.

[gwboolean]

June,

The second image is from the dataset. The dataset is not the actual database, but is a set of tableadapters that are created at runtime from the database and is used by Visual Basic. However, the database is updated from the dataset in real time as the dataset is used. The updates are carried out using a bindingsourceEndEdit() and tableadapterUpDate() commands. Please don't ask me how all that comes together and works as I have no idea.

The queries, as I am currently using them, do indeed reside in the tables (table adapters) contained in the dataset. Each query is written into the a tableadapter and called from the code of an event, routine or function of a form class, as shown above. As you stated, symantics.

Now, to your code. Is the code you provided above something that is written directly into the code in place of the query call that I have been using? If so, I would like to talk more about this as I would very much like to eliminate the use of writing queries into the tableadapters then calling them from my code, and instead have the query reside in the code where I want to use it.

I can see three problems for me with what you have. There is no End Using (where would that go for this? Probably after the cmd.CommandText line?). Secondly I already know that I will get an error with the oleDBCommand, although I think that will fix for me by making it Oledb.OleDBCommand. Lastly, where does cn dome from. It is undeclared.

Let me know, as I am very interested in coding my queries directly into the form events/routines/functions.

[June7]

Those two lines (slightly modified for the strSQL variable, may or may not be correct) are from your post so I don't know where you declare and set cn. Your example didn't show the End Using so I was not aware it was required, but yes I expect it would follow the cmd line.

Not having done database coding in VB.Net or VB6 - I have only adapted some VB6 code to VBA and used some VB6 books to help me learn VBA and run a couple of VB6 non-db apps - can't really offer more. Build an SQL statement string and use it where you see fit.