Append data from a linked excel range to a table

[murad5]

Hi, I'm trying to get data from a number of linked excel tables into one access table. The reason I can't just use an append query is that the number and names of the linked tables will change regularly, and I would like to be able to just have something like "Data" at the end of each of their names and then cycle through all the tables in the database and append any table with "Data" at the end of its name.

Any ideas, or should I just create an append query for each one?

Thanks

 

[DaltonVB]

Create a query:

SELECT Name, Type
  FROM MSysObjects
 WHERE Name Like "*date"
   AND Type=1;

open the query up with a recordset and run append query:

Private Sub Command0_Click()
    Dim i As Integer
    Dim iCount As Integer
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    
    Set rs = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    
    rs.Open "qryTables", _
             cnn, _
             adOpenStatic, _
             adLockBatchOptimistic
    iCount = rs.RecordCount
    For i = 1 To iCount
        DoCmd.RunSQL "INSERT INTO tblTarget " _
                   & "SELECT * " _
                   & "FROM " & rs!Name & ";"
        rs.MoveNext
    Next
End Sub

 

[murad5]

I'm beginning to think I've bitten off more than I can chew here! I'm very much a beginner in using VB for Access. When I try to run this code, I get the error message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'"

The code breaks down when it hits the line which starts "rs.Open". I presume my problems in my query. Any ideas?

Thanks for your help...

 

[DaltonVB]

When you created the query,

SELECT Name, Type
  FROM MSysObjects
 WHERE Name Like "*date"
   AND Type=1;

did you save it as 'qryTables'?

 

[murad5]

Ah, that seems to have helped. However, the problem now seems to be that there the query now returns no records?

 

[murad5]

Figured that one out - the type for linked tables seems to be 6, rather than 1. However, even though the query now returns the names of the tables I want to append, the recordcount property or the recordset is still 0?

 

[DaltonVB]

What type of recordset are you using?
If you open it as 'adOpenStatic' you will get a recordcount but if you use Dynamic, you will not.

This will return a count.

rs.Open "qryTables", _
             cnn, _
             adOpenStatic, _
             adLockBatchOptimistic
iCount = rs.RecordCount

 

[murad5]

I've opened it as 'adOpenStatic', using the same code as above, but it still gives a recordcount of 0...