Results 1 to 4 of 4
  1. #1
    stildawn is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    185

    Excel Template File to Add/Update Access Backend Table

    Hi All

    I am building a database and I haven't used Access in a long time so very rusty.

    One of the main things I need to achieve is adding/update records from an Excel template file as below process:


    • Users have a simple excel template file (this will match the fields in the Access table) (this is because I will also create a bunch of VBA code in excel to automate this excel template as much as possible from other data sources)
    • Users enter in new records into the excel template file and save
    • Users "push" excel template into Access backend somehow (Ideally via a button on a frontend userform)
    • Access creates new records in the table from the excel template file
    • However Access needs to match a few fields between excel and table and if identical change a date on the record in Access, as well as adding the new record from excel template file
      • This is for valid from and valid to dates, there might be a record that is valid from 1st May to 31st May already in the Access table, the new record in the excel template file might be identical except for the valid from date is 15th May.
      • So Access would change the initial records valid to date to 14th May in this case, then add the new record as well, so the Access table shows the below:
        • A record valid from 1st May to 14th May (initial record already in Access)
        • A new record valid from 15th May to 31st May, they will have different values in other fields, but the first 5 ish fields will be identical and can be used as the checking criteria.




    Can someone please point me in the right direction on the above?

    Regards

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Post

    What are your reasons for not making a frontend in access? It would make development much easier.

    Anyway, you can use DAO or ADO in your excel vba to manipulate the access data.

    I'm comfortable with DAO, here is some untested template code that I would use.
    You'll need to add a reference to the DAO object library: from the vba editor go to Tools -> References -> Microsoft DAO

    Code:
    Sub GetSomeData()
    On Error Goto ErrHandler
       Dim db as DAO.Database
       Dim rs as DAO.Recordset
    
      Dim qry as String
    
      qry = "SELECT * FROM some_table;"
    
      Set db = OpenDatabase("C:\Full_Path_To_DB\My_DB.accdb")
      Set rs = db.OpenRecordset(qry)
        If Not (rs.BOF And rs.EOF) Then 'make sure we got some records
          'Returned some records, lets loop through them
          rs.MoveFirst 'make sure I'm at the beginning of the data
          Do While Not rs.EOF 'start looping
            'process some data here
    
            rs.MoveNext
          Loop
        Else
          'query didn't return any records
          
        End If
      rs.Close
      db.Close
    
    ExitHandler:
      Set rs = Nothing
      Set db = Nothing
      Exit Sub
    
    ErrHandler:
      Msgbox Err.Description, , "Error #" & Err.Number
      Resume ExitHandler
    
    End Sub
    Code:
    Sub SaveSomeData()
    On Error Goto ErrHandler
       Dim db as DAO.Database
    
      Dim qry as String
    
      qry = "INSERT INTO some_table (field) VALUES ('value');"
    
      Set db = OpenDatabase("C:\Full_Path_To_DB\My_DB.accdb")
      db.Execute qry, dbFailOnError
    
      db.Close
    
    ExitHandler:
      Set db = Nothing
      Exit Sub
    
    ErrHandler:
      Msgbox Err.Description, , "Error #" & Err.Number
      Resume ExitHandler
    
    End Sub

  3. #3
    stildawn is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by kd2017 View Post
    What are your reasons for not making a frontend in access? It would make development much easier.
    I will be making a frontend in access to use the data in the backend tables.

    Its just the updating the backend I want to do via excel template, as the updates will be massive and regular provided by other sources, and I'll likely have to build VBA in excel to manipulate the other data sources in excel to match the template/access tables.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would add code in Access to import the Excel file into an empty temporary table then build your queries/ VBA from there.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2019, 02:57 PM
  2. Replies: 14
    Last Post: 04-07-2016, 10:12 AM
  3. Replies: 1
    Last Post: 11-05-2015, 12:13 PM
  4. Replies: 8
    Last Post: 01-27-2014, 12:41 PM
  5. VBA to update Products Table from excel file
    By Mclaren in forum Programming
    Replies: 2
    Last Post: 07-26-2012, 12:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums