Import/Enter arrays into Access Database

[jsong]

I have this huge data set in Excel (> 1000 rows and 25 columns) and i need to enter the data into Access. The reason I can't simply import it is b/c the data comes from multiple users with mulieple data sets every day.

I know how to enter it into an array and loop through it to enter access, but is there a way to import or "dump" the array into an Access database? What would be the best way to enter in a large data set into Access? Thanks in advance!

 

[pooratlas]

Would you pls restate the reasons that importing or linking are not options?

 

[boblarson]

The reason I can't simply import it is b/c the data comes from multiple users with mulieple data sets every day.

That doesn't make sense. Setting up an automated, or semi-automated import makes more sense than to enter into an array.

 

[jsong]

Well, here is my situation. I have this excel spreadsheet called "Rosters" that contains a large list of people ( >250). A team of 8 people works 80 Rosters (different workbooks) and when they are done or every 30 minutes, they update the access database with the Roster information.

They receive new rosters every day for 80 Rosters. Each team member updates the access database. Now what i want is instead of it having to loop through each line/record in Excel to enter data into Access, can I simply add all the excel data into an array and "dump"/paste/import/transpose it into access? Thanks,

 

[boblarson]

Are the Excel workbooks "standard?" In other words, do they all have the same columns for entering this information?

I guess I'm struggling to figure out why an import is not an option. If you can get the workbooks to a specific location (or if they are there) then you can set up code where you can push a button and it goes and imports the new data. In fact it can import to a temp table that is already set up and then you use Append queries to append the data to the main table so you don't get duplicates.

But, I'm confused as to what you are thinking in terms of an array as you would need to loop through each of the workbooks to build an array anyway, so why not just import the data?