Help Using a RecordSet With a Loop in a PassThrough Query

[RunTime91]

Greetings All...

I am currently using a recordset (Rs) to loop through a ListBox grab the selected items and add them to an Access BkEnd Linked Table

What I am trying to do is use a pass through query to send the data directly to the SQL server.

Here is the Rs that populates the access linked table

Dim Dbs As DAO.Database
Dim Rs As DAO.Recordset
Dim Item As Variant
Set Dbs = CurrentDb()
Set Rs = Dbs.OpenRecordset("TblTmpTktExtAccs")
   
  For Each Item In Me.List14.ItemsSelected
    Rs.AddNew
      Rs!ItemsRequested = List14.ItemData(Item)
    Rs.Update
  Next
  
    Rs.Close
  
  Set Rs = Nothing

Below is a passthrough Query I am currently using with an INSERT Query

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = strConnection
conn.Open

StrSQL = "INSERT INTO dbo.SalesNos (EmpID, EmpName, SalesTtl) " & _
         "VALUES('" & EmpId & "', '" & EmpName & "', '" & Sales & "')"
conn.Execute StrSQL

conn.Close
Set conn = Nothing

What I'm trying to do is adopt the looping Rs to the passthrough or vice versa

As Always - Thank You for your help...

[pbaldy]

If the SQL Server table is linked the same code will work. If you want to use the pass through, you'd put the bit setting strSQL and the Execute line inside the loop.

[RunTime91]

Hey Paul Thanks for being out there...

Not sure I'm following, but that may be due to my not explaining myself very well

Here is an 'in my mind - this seems like it should work' version of what I am trying to do

Dim conn As ADODB.Connection
Dim Rs As DAO.RecordsetSet conn = New ADODB.Connection
conn.ConnectionString = strConnection
conn.Open

Set Rs = conn.OpenRecordset("tri.TktExtAccsTmp")
   
  For Each Item In Me.List14.ItemsSelected
    Rs.AddNew
      Rs!ItemsRequested = List14.ItemData(Item)
    Rs.Update
  Next
  
  conn.Close
 Set conn = Nothing

[pbaldy]

That's going a different direction. I assume it doesn't work? What's the error, and where? Based on your original question I was envisioning:

  For Each Item In Me.List14.ItemsSelected
    StrSQL = "INSERT INTO dbo.SalesNos (EmpID, EmpName, SalesTtl) " & _
              "VALUES('" & EmpId & "', '" & EmpName & "', '" & Sales & "')"
    conn.Execute StrSQL
  Next

With the values coming from the listbox or wherever.

[RunTime91]

OMG!!! LOL!!!

Your interpretation of what I wanted to do > my interpretation of what I wanted to do.

That work's brilliantly, Paul!!

Thank You so very much!!

(stay tuned ((please)) for my next question)

[pbaldy]

Happy to help!