Pushing data from Excel into Access?

  • Thread starter Thread starter sharp via AccessMonster.com
  • Start date Start date
S

sharp via AccessMonster.com

I have designed an Access system for keeping track of Excel spreadsheets.

The system has a GUI that allows certain critera to be entered, then creates "empty" spreadsheets from an Excel template (.xlt) These spreadsheets are populated with data by humans and saved to a central network share.
On a nightly basis, a Windows scheduled task calls a Macro in the database which opens all of the Excel sheets, extracts (Pulls) summary data from them and stores it in a table. This, obviously, takes longer and longer as more spreadsheets are added to the project.
The database GUI is also used for generating reports.

This all works fine; but sometimes, the data needs to be more "current".

So, to my question:
Is is possible / sensible to try to "Push" the data into Access from Excel (for example by using the Worksheet BeforeSave Event) using ADO or DAO? As my knowledge of Office automation is self taught, I'm probably missing an obvious (perhaps better) way to do this.
 
Sure it is possible. All you need to due is use ADO to connect to the Jet
database and open the table in which the data will be stored. Use the add and
update methods as they apply to the ADO recordset object. Here is some sample
code that I execute from Excel to update a Jet database. A new record is
produced for each state the is containing in an Excel array.

Dim Con As ADODB.Connection
Dim rst As ADODB.Recordset
Set Con = New ADODB.Connection
Set rst = New ADODB.Recordset
Fname = ThisWorkbook.Path & "\Network.mdb"
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Fname
Con.Open
With rst
.Source = "Table_Name"
.ActiveConnection = Con
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End with
For State = 1 To 51
rst.AddNew
rst.Fields("Date") = ArrState(State, 1)
rst.Fields("State") = ArrState(State, 2)
rst.Fields("NetworkYTD") = ArrState(State, 3)
rst.Fields("TotalYTD") = ArrState(State, 4)
rst.update
Next State
rst.Close
Set rst = Nothing
Con.Close
Set Con = Nothing
End Sub
 
Excellent.

Thank you very much for your quick reply Jared. I will try this out and post my results here.
 
Back
Top