Creating a loop to import all records

[Soulac]

I'm having a little mental Block at the moment and would like a little assistance if possible. I Have a legthy piece of code (which i won't bore you with) to extract file names from a network directory I then compare them to a table which lists files already imported the result of this query gives me the files needed to be imported. the records can range from 1 to a maximum of about 90 this will vary But will normally be below 10 the code i am using to import is as follows

Sub ImportData()

Dim strTableName As String
Dim strFileName As String
Dim StrPath As String
DoCmd.SetWarnings False



Set DBS = CurrentDb


Set RST = DBS.OpenRecordset("select * from qry_locate_imports")

strFileName = RST!import_list



strTableName = "DataSheet"
MsgBox strFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strTableName, strFileName, -1, "DataSheet!A1:AA50"





DoCmd.SetWarnings True

It's Pretty simple And more importantly it works But it Only picks up the first record of the recordset.

The recordset Import_list field is a string of the network path and is in this format

\\Tyson\Network\South Central Performance\Dailyoutputfiles\North 11-9.xls
Where the last characters represent the date.

I do have a workaround which involves refreshing the imported data and running the code for the Directory extract again but i'm sure there must be a simpler faster soulution

Any Idea's

 

[mdmilner]

Set RST = DBS.OpenRecordset("select * from qry_locate_imports") 

With RST
  Do Until RST.EOF
       strFileName = RST!import_list 
       strTableName = "DataSheet" 
       MsgBox strFileName 
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strTableName, strFileName, -1, "DataSheet!A1:AA50" 
    .MoveNext
  Loop
End With

This would be using DAO object models.
You'll need the reference added to the database AND it may be a good idea to modify your declaration like:

Dim dbs As DAO.Database
Dim RST As DAO.Recordset

Mike

 

[Soulac]

Thanks for that It loops ok but the .movenext is just not moving to the next record and keeps repeating the append for the 1st file in the set

Any Ideas

 

[Soulac]

Ignore the last Posting It's Just me being a D!ck I Had the StrFileName in the wrong place.

Thanks For Your Help Mike