Updating Access database

[scrumpyjack]

Is there any way I can automatically update my Access data base from my excel spreadsheet?

[xLJer]

You can update an Access database from Excel. I worked out an example
in the attached Excel and Access files, but you'll need to figure out
the steps to be done for your specific application. The attached has
two fields, a name and a score.

My example database has a link to the Excel worksheet. From the Excel
code in Module1, a macro in the Access database is run, which first
runs an Access query to append new records, if there any any, from the linked
Excel file to a permanent keyed table, then the same macro runs a
second query to update a second field in the permanent table, the scores. In the Access
macro, warnings are turned off so that you do not see the standard warning
messages where you would have to click a button to continue.

If you try out these files, put them in the same folder.

Excel code in Module1:

Sub Run_ACCDBMacro()

Dim dbFilePath As String, tblName As String
Dim sourceSheet As String
Dim AccApp As Object

    dbFilePath = ThisWorkbook.Path & "\PScores.mdb"
    sourceSheet = "Sheet1"
    tblName = "Sheet1"
   
    Set AccApp = CreateObject("Access.Application")
    'Use next statment only needed if you want to see Access - default is to be "invisible"
    AccApp.Visible = False
    AccApp.Application.AutomationSecurity = msoAutomationSecurityLow  'turn off Access warning message
    
    AccApp.OpenCurrentDatabase (dbFilePath)
    AccApp.D*o*C*m*d.RunMacro "UpdateSheet1Tbl"   ‘asterisks in this line must be removed
    AccApp.CloseCurrentDatabase
    AccApp.Quit
    Set AccApp = Nothing
    
End Sub

[scrumpyjack]

OK thanks I'll have to sit down and play and see what happens.
Thanks again
Fred

[xLJer]

This solution was initially tested on a Windows 7 PC. I ran it on another PC with Windows XP
and got and error when setting the Access security level. Assuming you are using one of these
operating systems, the Excel Module1 replacement code below should eliminate this error.

Also, you will need to re-link the Excel file in the Access database.
Do these steps:
- open PScores.mdb in Access
- view list of Access Tables and delete the currently linked Excel "Sheet1"
- click on File
- Get External Data & Linked Tables
- navigate to the folder with the Excel file
- choose Excel filetypes to display the file in the dialog box
- select the Excel file RunAccMacroText.xls
- check First Row Contains Headings if it is not already checked
- click Next. Linked Table Name should be "Sheet1"
- click Finish

New Excel code:

Sub Run_ACCDBMacro()

Dim dbFilePath As String, tblName As String, operSys As String
Dim sourceSheet As String
Dim AccApp As Object

    dbFilePath = ThisWorkbook.Path & "\PScores.mdb"
    sourceSheet = "Sheet1"
    tblName = "Sheet1"
    
    operSys = Application.OperatingSystem
    operSys = Mid(operSys, (InStrRev(operSys, " ") + 1), 1)
    
    Set AccApp = CreateObject("Access.Application")
    'Use next statment only needed if you want to see Access - default is to be "invisible"
    AccApp.Visible = False
    
    'turn off Access security warning message
    If operSys = "6" Then       'O/S is Windows 7
        AccApp.Application.AutomationSecurity = msoAutomationSecurityLow
    ElseIf operSys = 5 Then     'O/S is Windows XP
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Else
        'some other operating system
    End If
    
    AccApp.OpenCurrentDatabase (dbFilePath)
    AccApp.DoCmd.RunMacro "UpdateSheet1Tbl"
    AccApp.CloseCurrentDatabase
    AccApp.Quit
    Set AccApp = Nothing
    
End Sub

[scrumpyjack]

Thanks again
Fred