UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Do I Need To Create A Copy Of My Backend Table Which Isn't Read-only, Any Version    
 
   
hapless
post Aug 1 2020, 08:13 PM
Post#1



Posts: 8
Joined: 1-August 20



Hello,

I hope my question is clear and my included context isn't excessive. This is probably a stupid question but this is my first time dealing with databases and I greatly appreciate any help

Context:

I am trying to create an Access database that stores basic order and customer info for an ongoing volunteer-run fundraiser. The volunteers involved will be accessing this database through Access Runtime on their machines.

Currently, are using a donor relationship management client that publishes an order form they have on their website, does the payment processing and stores all captured data in its database. It is set up extremely poorly (not for ecommerce) and 3 different built-in reports need to be run, exported, and joined to display all the information needed for just one order record.

Additionally, the volunteers need to track fulfillment status for each order since they are handling delivery themselves. Their DRM client does not capture this post-transaction event. I will have to create a field in Access which they will then populate through data entry.

Current situation
So far, another skilled volunteer helped me with staging the data in SQL Express Server on my computer. The staging table is just a cleaned up extract from several flat files (exported from the DRM client) and does not have any relationships. I have successfully imported it into Access. If I import it as a linked table, a lot of the records in one calculated field do not display the correct information. It displays correctly with the imported table so I am just keeping it unlinked.

I do not have the skills to make any changes to the SQL table within SQL Server Management Studio.

Additionally, every night as orders are made I am exporting the DRM reports to the same file path SQL is using for importing source data. So the previous tables in SQL from the last export are being overwritten with new records daily, and then I run a stored procedure which does all the transformation and cleaning steps needed.

Goals

I want to deploy this project as a split database.
I want the users to be able to do data entry through a form for order fulfillment status.
I want their data entry to be saved in a table which automatically refreshes and is viewable by all users


Questions

1. I know I have to create relationships in my staging table for any forms or queries to work, but would I then save my edits in a separate table?
The staging table that is imported is going to have a saved import procedure run on startup of the app so that the most recent update from the SQL server is pushed through, so it will be constantly overwritten.

2. How can I make it so that the separate table (which is now restructured) saves any changes and is viewable to all app users? Do I have to store it in the back end?

3. Can I set it up so that any updates the user makes in their session will be immediately saved and able to be queried in the same session?


Thank you again!!!


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:19 AM