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!!