Import/Enter arrays into Access Database

jsong

Board Regular
Joined
Oct 11, 2007
Messages
50
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

pooratlas

Board Regular
Joined
Apr 17, 2007
Messages
166
Would you pls restate the reasons that importing or linking are not options?
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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

Board Regular
Joined
Oct 11, 2007
Messages
50
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,199
Messages
5,857,888
Members
431,905
Latest member
RW11700

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top