CODEWITHSUNDEEP
ms-accessvbams-access-2010
Nigel
Nigel

Reputation: 309

Pass one line of recordset to a function

While I am moving through a recordset, I want to pass the current line through to another function. How could I do that?

I have set rs = "my_query". As I loop through rs, starting with the first record and moving through until the last record, I pass the current record to another function that fills out a table with all of the fields in the query. Right now I have to list every field I want passed into the other function and written to the table. It seems like there should be an easier way to get the current record written to a table. In the example below I am only showing 3 fields. "my_query" actually has a lot of fields. It is also a lot of work to change all of the references to the WritetoTable function when we add or remove fields from the query.

I'd like to just pass the whole rs to the WritetoTable function, but I don't know how to do that while making sure I only write the one record I want into the table.

Set rs = "my_query"
rs.MoveFirst
Do While Not rs.EOF
    Call WritetoTable(rs!field1, rs!field2, rs!field3......)
rs.MoveNext
Loop

Function WritetoTable(field1 as string, field2 as string, field3 as string...)
Dim rsTable as DAO.Recordset

Set rsTable = CurrentDb.OpenRecordset(Table,dbOpenDynaset)

With rsTable
.AddNew
!Field1 = field1
!Field2 = field2
!Field3 = field3
.update
End With

rsTable.Close
Set rsTable = Nothing

End Function

Upvotes: 2

Views: 3024

Answers (2)

Nigel
Nigel

Reputation: 309

Thank you to Remou and Overmind for leading me in the right direction. I decided to use the bookmark property of the recordset to ensure I could come back to the same place. The code below looks at each line in the query result and passes it to the WritetoTable function.

It is true that I could simplify this to an append query if it was as simple as the code I have shown. In my situation it would take a lot of time to run such specific queries off a big server table. So I run one query that has data in it that needs to be sorted out into various tables. The query still takes a long time but at least it only has to run once. I then have to go through the query results one line at a time to see which table it should be written to.

The code below lets me look at each line of the query result. When it needs to be written to my table I can pass the whole recordset into the WritetoTable function and use the bookmark to write only the one line I was looking at. I don't know if the code runs slower or faster than what I had before, but it is easier to edit and make changes to.

Set rs = "my_query"
rs.MoveFirst
Do While Not rs.EOF
    vPosition = rs.Bookmark
    Call WritetoTable(rs, vPosition)
rs.MoveNext
Loop

Function WritetoTable(rs as Recordset, vPosition as Variant)
Dim rsTable as DAO.Recordset

Set rsTable = CurrentDb.OpenRecordset(Table,dbOpenDynaset)
rs.Bookmark = vPosition

With rsTable
.AddNew
!Field1 = rs!field1
!Field2 = rs!field2
!Field3 = rs!field3
.update
End With

rsTable.Close
Set rsTable = Nothing

End Function

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

An append query would be much simpler and much faster. However, you have to ask yourself, do I really need the same data in two tables?

 Dim db As Database
 Set db = CurrentDB

 sSQL = "INSERT INTO ATable (Field1, Field2) " _
      & "SELECT FieldA, FieldB FROM BTable " _
      & "WHERE BTable FieldX='Y'"

 db.Execute sSQL, dbFailOnError

You could also have a saved query and simply run it in VBA. For Example:

 db.Execute "AQuery", dbFailOnError

Note that db.Execute will only work with action queries.

It is often best to use an instance of CurrentDb, because it will allow you to get RecordsAffected.

You can also append from an external database, for example:

 INSERT INTO ATable SELECT * FROM [ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=Server;DATABASE=Database;uid=User;pwd=Password].AnotherTable t WHERE t.FieldX Like "w*"

You need to ensure you have a good connection string. It is generally best to list the fields / columns, rather than trying a wildcard.

Upvotes: 0

Related Questions