How to insert Dao recordset into table

[tomtom123]

Hi I`m quite a beginner in MS access and VBA and I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table.

I found similar threads but I am still not able to generate functioning code.
This is the code I have so far and I would appreciate any help.

Function fDAOServerRecordset()



    Dim db As DAO.Database
    
    Dim dblcl As DAO.Database

    Dim rssql As DAO.Recordset

    Dim strConnect As String
    
    Dim strSQL As String
    

    'With integrated security

    strConnect = "ODBC;DSN=FIS-DW;Trusted_Connection=Yes;DATABASE=FIS_DW"

   
    strSQL = "SELECT [Base_Barcode] FROM [tablesamples]"
    
    'Open the database and recordset
    Set db = DBEngine.OpenDatabase("FIS_DW", False, True, strConnect)

    Set rssql = db.OpenRecordset(strSQL, dbOpenSnapshot)
       
         
 'This is where I am stuck! I would like to insert the data into an existing table

    db.Close
    
    Set rst = Nothing

    Set db = Nothing

    

End Function

Thanks!

 

[joeKra]

Replace dao with ado.recordset

 

[MarkK]

A recordset is an in-memory representation of data in a table, so you don't "add one to an existing table." A recordset is bound to the table it originally drew it's data from. It's like a window in a house. It shows what's in the house. That's it.

You could use recordsets to move data from one table to another, but then you would need two recordsets. And that would be tedious and slow.

Most commonly you would use SQL, or Structured Query Language, to write an Insert query. An insert query will have this general form . . .

INSERT INTO tDestination ( Field1, Field2, Field3 )
SELECT tSource.Field1, tSource.Field2, tSource.Field3 
FROM tSource
WHERE tSource.SomeConditionIsTrue

hth