Auto updating access table based on a spreadsheet

[TerriO]

I am very very rusty at Access and have created a table based on an excel spreadsheet. The spreadsheet will be updated periodically and I want to have the database automatically update with limited or no user interaction. Years ago I recall creating a macro to do this but not sure how. My technical experience with access is very limited. Help!


Terri

 

[BNPMike]

I'm doing the very same thing now. Start with the TransferSpreadsheet action. Then just do a series of update queries based on the imported table(s).

 

[TerriO]

Thanks for your reply, but what do I need to query?

Terri

 

[HitechUser]

TerriO,

This may help.

http://www.tek-tips.com/viewthread.cfm?qid=958951&page=1

Or this Excel macro - (Example below

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

The macro example assumes that your VBA project has added a reference to the DAO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft DAO x.xx Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Hey, maybe just maybe this may help..

Good Luck...

 

[BNPMike]

What I do is import the sheet(s) into staging tables. This helps you to isolate errors - it's easier to check things went properly. Then write update/append queries to move the data from the staging tables to your operational tables.