Results 1 to 8 of 8
  1. #1
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16

    Importing Access data from other copies of the database

    Good morning! I have a pretty simple Access database that we are going to use to record cybersecurity compliance with federal law. We will have 5 separate groups filling out 5 different groups of requirements. Due to policy, we are not allowed to collaborate on our Sharepoint server while we are on the client's network. As a result, each assessor will have their own copy of the database, but only fill out their portion of the assessment data and provide that data to the assessment lead to compile and run reports on his copy of the database. Whew!

    I have everything I need coded, and a query that will generate an Excel spreadsheet with the assessor's changes. What I can't seem to do is find a way to have the lead import those changes after collecting the spreadsheets. I have an Import button that I am testing with an On Click event that grabs the spreadsheet (I'll be putting in some logic later to allow the lead to browse to the file once I get this part fixed) and updates the Requirements table, but it always errors out with 0 records updated.

    Here's my On Click event:

    Code:
    Private Sub Btn_Import_Data_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Tbl_Requirements", "C:\temp\Export_1.xls", True
    End Sub
    Which is basically a reverse of my export statement for the Export button

    Just FYI, my Tbl_Requirements, the Requirement Number is the Primary key and is a short text field, and the typical data in it is something like 3.1.2[a], 3.1.2[b], 3.1.2[c], etc. I export the Requirement Number and a few checkbox fields in the spreadsheet.

    So, where am I going wrong, or is there no good way to do this with a lite-touch approach (My assessors aren't Access savvy)

    Many thanks!!

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you tried acLink rather than acImport then run queries to update records.?

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    link the external table and you can run any query on that table.

  4. #4
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by moke123 View Post
    Have you tried acLink rather than acImport then run queries to update records.?
    That would require our systems to be able to talk to each other, correct?. We don't have that luxury at our clients' site. Peer to peer communication is blocked. They only allow us an outbound connection to reach the internet so we can send email to each other, but not link to each others' tables.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then they can send you an excel sheet,
    save it to the same place everytime: c:\temp\Data2Import.xls
    THIS file is linked as an external table,
    then all you do is run an append query after you save the XL file.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Bill the Cat View Post
    Here's my On Click event:

    Code:
    Private Sub Btn_Import_Data_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Tbl_Requirements", "C:\temp\Export_1.xls", True
    End Sub


    Since your source spreadsheet has an extension of .xls, I would suggest you use/try
    Code:
    Private Sub Btn_Import_Data_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_Requirements", "C:\temp\Export_1.xls", True
    End Sub


    acSpreadsheetTypeExcel12 --> This is used for Excel 2007 format
    acSpreadsheetTypeExcel12Xml --> This is used for Excel 2010 - 2019 format

    See https://ss64.com/access/acspreadsheettype.html

  7. #7
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by ssanfu View Post
    Since your source spreadsheet has an extension of .xls, I would suggest you use/try
    Code:
    Private Sub Btn_Import_Data_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_Requirements", "C:\temp\Export_1.xls", True
    End Sub


    acSpreadsheetTypeExcel12 --> This is used for Excel 2007 format
    acSpreadsheetTypeExcel12Xml --> This is used for Excel 2010 - 2019 format

    See https://ss64.com/access/acspreadsheettype.html

    Perf!. Got it all working and put some additionalc logic in to choose the file to import into my update table, run the update query, and kill all of the data in the update table so I can accomplish this from multiple assessors. Thanks for the help!

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.........

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

Similar Threads

  1. Replies: 1
    Last Post: 03-26-2016, 06:42 AM
  2. Replies: 1
    Last Post: 05-13-2015, 02:25 PM
  3. Copies of database
    By buckwheat in forum Access
    Replies: 2
    Last Post: 09-17-2013, 02:23 PM
  4. Importing data into Access database from Excel textboxes
    By Sambora in forum Import/Export Data
    Replies: 3
    Last Post: 05-06-2013, 02:27 PM
  5. Conflicted copies of BE database.
    By kcrty in forum Access
    Replies: 2
    Last Post: 02-26-2013, 06:40 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