Importing a Record Set

[Loomah]

hi
I’m developing a system to import data from a total of 96 spreadsheets into 3 tables in access, ie 32 spreadsheets into each table. giving the end users access is not an option as it is not a permitted application on the network and users will change from day to day.

I’m not an expert when it comes to access which is why I need some help here!

I have built a process in vba that loops through a list of ‘areas’ as defined by a single table (32 areas, each with 3 spreadsheets) and using the transferspreadsheet method it seems to work (barring the spreadsheets being “changed”!)

The code for transferring I’ve used is

DoCmd.TransferSpreadsheet acImport, 8, “TableName” , sPATH & sFileName, True, sourceSheetName

However I am a little nervous as to how robust using transferspreadheet is as, while testing, I occasionally end up with one or more of the target spreadsheets left open on my PC. or occasionally I get the “xyz.xls is now available for editing” type message about 10 minutes after running the import.

On inspection, when these quirks occur the data still seems to have imported but it isn’t entirely clear, hence my reservations that I have used the best option available to me. Any pointers/comments on this would be much appreciated.

My next option was to try and use ado to create a recordset and append that to the relevant table in access.

So finally to my actual question, is it possible to append a recordset to a table?

I can create a recordset and do some stuff with it (eg looping through the table of ‘areas’ mentioned some way back) but can’t get the data in that recordset into a table.

In a separate test file I have created the following to give me a recordset

 Sub ImportFromXL()
    Dim Conn1 As New ADODB.Connection
    Dim Cmd1 As New ADODB.Command
    Dim Rs1 As New ADODB.Recordset
    
    Dim xlConn As String
    Dim strSQL As String

       xlConn = "Driver={Microsoft Excel Driver (*.xls)};" & _
                     "Dbq=SomeData.xls;" & _
                     "DefaultDir=H:\TestArea\VBA;" & _
                     "Uid=Admin;Pwd=;"

       Conn1.Open xlConn
       Set Rs1 = Conn1.Execute("SELECT * FROM [Sheet1$]")
       Debug.Print Rs1.Fields(0), Rs1.Fields(1)

       strSQL = "INSERT INTO [SomeData] IN '' SELECT * FROM [Sheet1$]
       DoCmd.RunSQL strSQL
       Rs1.Close
       Conn1.Close

End Sub

This gives me the recordset from my test xl file and allows me to print the first 2 fields however the sql gives me error “3078” – “The Microsoft Jet database engine cannot find the input table or query 'sheet1$'. Make sure it exists and that its name is spelled correctly.”

To test the sql I changed it to the following where sheet1 is an existing (very poorly named) table in the same database. This seems to work fine

strSQL = "INSERT INTO [SomeData] IN '' SELECT * FROM [sheet1] where head2=""bob"""

Any help, suggestions (of alternative robust methods), pointers etc greatly appreciated.


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​