locked
Update Access from an Excel Spreadsheet RRS feed

  • Question

  • Is it possible to update Access from an Excel spreadsheet?  I have an excel spreadsheet that I want it update my access database if entry is made in the excel spreasheet.  Thanks
    Monday, February 7, 2011 3:33 PM

Answers

  • Abdulmi,

    You should be able to save that Oracle export to a shared folder and link to it using Access.  If you can get ODBC connectivity to the data in the Oracle DB, you can link to that data directly in Access.

    What version of Access are you using?

    • Marked as answer by ForGod Monday, February 7, 2011 9:06 PM
    Monday, February 7, 2011 7:25 PM

All replies

  • hi,

    sure. E.g.

    http://www.exceltip.com/st/Export_data_from_Excel_to_Access_%28DAO%29_using_VBA_in_Microsoft_Excel/426.html


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, February 7, 2011 3:41 PM
  • Abdulmi,

    I have done this type of thing once or twice, and I can tell you that you really would rather not do it this way, even though it is quite possible.

    The main reason for this is that it is simply rather difficult to assure that the excel input form/spreadsheet stays in the form that it was in when your code was created. For example, say you're updateing a tabular table of data and you want column F's data to go into "Field4" in your data table - unless you go to some pains to prevent the user in Excel to mess that up by either inserting or deleting columns/rows in the spreadsheet, well, I'm sure you get the picture.

    Is it possible to sufficiently lock down a spreadsheet to use it this way and have a reasonable level of assurance that things won't get badly messed up? yes - but only by sacrificing what excel is best at: being a totally flexible and dynamic platform for managing data entry/presentation.

    If you're going to sacrifice that, then why are you still using excel to begin with? There's a catch-22 there.

    If you are going to bulldoze your way past that, then there are a number of things to consider and do to use Excel as a front-end for MS-Access data. But I again warn you: there are quite a lot of ways that this can get messed up rather easily unless you are sufficiently careful. Otherwise, the old I.T. Maxim: Garbage in = Garbage Out *WILL* apply.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, February 7, 2011 4:03 PM
  • Thanks for the advice
    Monday, February 7, 2011 4:36 PM
  • I used the following macro but I get "user-defined type not defined" with database highlighted, is there something I am doing wrong?

     

    Sub DAO()
    Dim db As Database
    Dim rs As Recordset, r As Long
        Set db = OpenDatabase("S:\IMILab\IMI Lab Status.mdb")
       
        Set rs = db.OpenRecordset("Tblbatches-IMI", dbOpenTable)
        r = 3
        Do While Len(Range("A" & r).Formula) > 0
                With rs
                .AddNew
                .Fields("Commodity #") = Range("A" & r).Value
               
                .Update
            End With
            r = r + 1
        Loop
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End Sub

    End Sub

    Monday, February 7, 2011 5:05 PM
  • Did you add a reference to the DAO object library? (VBE Editor Tools->References  make sure that the Microsoft DAO 3.6 library is checked as selected - it is generally not preselected in an Excel VBA Project).

    Also, you might want to change your variable declarations slightly..

    Dim db As DAO.Database
    Dim rs As DAO.Recordset...

     


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, February 7, 2011 5:29 PM
  • Thanks Mark, you are right. I give up.  I can't get passed the security of the Access database
    Monday, February 7, 2011 6:02 PM
  • Abdulmi,

    What do you mean - Is the access database "secured"? or are you referring ot the generally more stringent limitations/requirements for Relational databses -vs- spreadsheets?

    Either way, there are means of delaing with both issues. But the ultimate question is will it be worth your time & energy to continue this way or do something different/better/more efficient?

     


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, February 7, 2011 6:05 PM
  • Yes, the database is secured.  What would you considered more effecient?  The method is so convenient (or I think it is convenient) because the report is exported from an Oracle-type program to an excel spreadsheet.  I was thinking of using this spreadsheet as the source for updating the database.

    Is there a way of copying and pasting data onto access? I dont think access is the "copying and pasting" type.  Thanks

    Monday, February 7, 2011 7:01 PM
  • Abdulmi,

    You should be able to save that Oracle export to a shared folder and link to it using Access.  If you can get ODBC connectivity to the data in the Oracle DB, you can link to that data directly in Access.

    What version of Access are you using?

    • Marked as answer by ForGod Monday, February 7, 2011 9:06 PM
    Monday, February 7, 2011 7:25 PM
  • Abdulmi,

    Oh, you're only looking to do some Excel->Access import-type functions...? That's quite a different matter from what I thought you were asking about (using Excel as an ongoing data entry-type front-end for an MS Access set of data tables).

    Also, Access is MORE than able to extract the data directly from Oracle data stores...if you have the necessary permissions to establish ODBC or OLE-DB connections to the tables/views/stored-procedures from which to pull the data. (Your Oracle DBAs may be somewhat reluctant to share this information, and for some understandable reasons, however.) Why use Excel or a report if you don't need to if you can directly get the data form Oracle into Access for your needs? The Oracle DBA might be significantly willing to let you pull the data from a login with read-only permissions then from one with full read/update/etc permissions. However, your management might not be willing to suffer the political slings and arrows necessary to get authorization to do so through your IT organization's bureacratic defenses...

    Also, Access can import data tables directly from Excel - either from a given worksheet or from named ranges.  Drive the import process directly from within Access rather than from Excel, and it should be much simpler/faster.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, February 7, 2011 7:26 PM
  • Where are you with this thing now, Abdulmi?  I would go with Stefan's recommendation.  I used that exact same code about 1 week ago, on a secured Access mdb, and it worked fine (no need to enter a password, even though the mdb itself was password protected).  You may want to take a look at the link below, when you have some free time:

     

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

     

     

    http://www.erlandsendata.no/english/index.php?t=envbadac

     

    (one of the examples there is the one that Stefan suggested).

    Monday, February 7, 2011 11:51 PM
  • Hello,

    I get an error when I try to use this code with excell and access 2010. The error is in the line

    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\t150803\Escritorio\test.mdb;"

    the error says that does not recognize the format of the database.

    Any idea?

    thank in advance.

    Friday, March 21, 2014 1:56 PM
  • You could try using ADODB to read data from Excel to Access.  The following code sample of for reading a .xlsx (or .xlsm) from Access2010

    -- HDR=YES -- means first row of data in Excel file is column headers and will not be read.  If the first row is actual data then use
    -- HDR=No --

    '--add reference to Microsoft ActiveX Data Objects 6.1 Library -- For ACE in Tools/References

    Sub ReadDataFromXlsx()
       Dim cmd As New ADODB.Command, RS As ADODB.Recordset
       Dim rsDAO As DAO.Recordset, i As Integer   
          
       cmd.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\1C\testExcel.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""  
       cmd.CommandText = "Select * from [sheet1$]"
       Set RS = cmd.Execute
       Set rsDAO = CurrentDB.OpenRecordset("tblExcelData")
       Do While Not RS.EOF
          rsDAO.AddNew
          For i = 0 To RS.Fields.Count - 1
             rsDAO(i) = RS(i)
          Next
          rsDAO.Update
          RS.MoveNext
       Loop
       Debug.Print "Done!"
    
    End Sub

    If your data in Excel does not exist in a sheet called Sheet1 then change the sheet name in the code sample to your sheetname -- and you must follow it by the $ symbol (required).


    Rich P

    Friday, March 21, 2014 3:41 PM
  • @Andres4893

    That sounds like someone may have opened that database (in .mdb format) in Access 2010 and allowed Access 2010 to modify the database while Some of the Access settings were not set for the most backwards compatible modes.  This will result in a .mdb that cannot be opened again by the JET Database engine provider.

    Check the following settings in Access 2010:

    Current Database->Picture Property Storage Format = Convert app picture data to bitmaps

    Current Database->Document Window Options = Overlapping Windows

    Current Database->Use Window-themed controls on forms = unchecked

    Client Settings-> Encryption Method = Use Legacy Encryption

    If any of these settings needed to be changed, reset them, and do a compress & repair in Access 2010.

    IF you have never used any of the Access 2010 form controls or form editing features which are not backwards compatible with Access 2003, then Jet should be able to open the database again.

    if you have used those features, then you will have to change from using the JET database engine to using the newer ACE database engine to open and work with your database from Excel.

    i.e. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties
    ="Excel 12.0 Xml;HDR=YES;IMEX=1";


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

    Saturday, March 22, 2014 10:16 AM
  • You can try this.

    Sub UpdateClick()
       Dim conn As ADODB.Connection
       Dim myRecordset As ADODB.Recordset
       Dim strConn As String
    
       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Users\Ryan\Desktop\Northwind.mdb"
    
       Set myRecordset = New ADODB.Recordset
    
       With myRecordset
          .Open "Select * from PersonInformation", strConn, adOpenKeyset, adLockOptimistic
          .Fields("ID").Value = Worksheets("Sheet1").Range("A2").Value
          .Fields("FName").Value = Worksheets("Sheet1").Range("B2").Value
          .Fields("LName").Value = Worksheets("Sheet1").Range("C2").Value
          .Fields("Address").Value = Worksheets("Sheet1").Range("D2").Value
          .Fields("Age").Value = Worksheets("Sheet1").Range("E2").Value
          .Update
          .Close
       End With
       Set myRecordset = Nothing
       Set conn = Nothing
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, March 23, 2014 1:42 PM