AccessJunkie
Senior Managing Editor
- Local time
- Today, 14:17
- Joined
- May 11, 2006
- Messages
- 278
Hi Jane,
Let's tackle the DateDiff question first.
Your syntax for that expression is correct if you were using a desktop database within Access, but since you are using an Access web app, the expression syntax will be a little bit different.
To achieve your goal, I believe your syntax needs to be the following:
=DateDiff(Day,[Posting Date],Today())
You would use that either as the Control Source for a text box on the view or as a calculated column within a query. You don't want to create a calculated column at the table level for this type of expression.
Now for your other questions.
I think I know where you are going with this. I assume people are not using the web app yet, correct? They are still using the spreadsheet(s) while you are working on this web app to replace it, correct? Which means the data in the app is stale because it doesn't have any changes made after you imported the data.
Technically you are correct that you can't import new records directly into the web app tables during an import operation. Access will always create a new table during this process. Which means of course you don't have the new data in the correct master tables(s).
On the delete issue, I'll get back to that in a second...
You also said:
To answer this question, yes if you delete a table in a web app, Access will automatically delete all associated views tied to that table that show up in the View Selector. You could still have some related objects around though like queries, data macros, related views not in the View Selector, etc. that would no longer be functional. So in essence, yes, you would be starting completely over. Naturally, I do not recommend this approach at all!
So how do we do this then?
When you are ready to go live with all the correct and up to date data, I do the following:
- Make a back-up copy of the app. I'm a back-up freak so I always take lots of backups. This step is optional, but its good for peace of mind.
- Clear out all records in all the tables. This is super, super easy to do using data macros. A simple ForEachRecord loop with a DeleteRecord action inside the loop will clear everything out. You need to start with the child tables and then move on to the parent tables.
You could optionally just save a backup copy of the app and elect not to include all the data. Then install that empty app and start from there.
- The next step is to import all of the data from the Excel spreadsheet(s) into the app with empty tables. When you import, provide different names for the new tables that will be created. You now would have all of our original master tables with no records as well as new tables with all of the live data.
- The final step is to create some new data macros that will "copy" the data from the imported temp tables into the master tables. The lookup fields to related tables would also have to be accounted for in this step as well. After this is complete just deleted the imported tables so you are left with just the master tables and objects.
That might all seem a bit scary at this point, but trust me, it can be done. I've done this very thing many times and even on some very large Access web apps that I've helped created that are being used throughout the organization here at Microsoft.
So what I would suggest at the moment is keep plugging away on what you've been doing so far. Keep modifying and adding to the app as you have been doing until you've think you reached a point where you are ready to go live. Once you've reached that point, then we can take care of getting the correct data into the app, turn your users loose on the web app, and then tell them to stop using the Excel spreadsheet going forward. You can continue to make more changes to the app even after it is use as well.
Does that help?
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation
Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
Let's tackle the DateDiff question first.
Your syntax for that expression is correct if you were using a desktop database within Access, but since you are using an Access web app, the expression syntax will be a little bit different.
To achieve your goal, I believe your syntax needs to be the following:
=DateDiff(Day,[Posting Date],Today())
You would use that either as the Control Source for a text box on the view or as a calculated column within a query. You don't want to create a calculated column at the table level for this type of expression.
Now for your other questions.
I think I know where you are going with this. I assume people are not using the web app yet, correct? They are still using the spreadsheet(s) while you are working on this web app to replace it, correct? Which means the data in the app is stale because it doesn't have any changes made after you imported the data.
Technically you are correct that you can't import new records directly into the web app tables during an import operation. Access will always create a new table during this process. Which means of course you don't have the new data in the correct master tables(s).
On the delete issue, I'll get back to that in a second...
You also said:
To answer this question, yes if you delete a table in a web app, Access will automatically delete all associated views tied to that table that show up in the View Selector. You could still have some related objects around though like queries, data macros, related views not in the View Selector, etc. that would no longer be functional. So in essence, yes, you would be starting completely over. Naturally, I do not recommend this approach at all!
So how do we do this then?
When you are ready to go live with all the correct and up to date data, I do the following:
- Make a back-up copy of the app. I'm a back-up freak so I always take lots of backups. This step is optional, but its good for peace of mind.
- Clear out all records in all the tables. This is super, super easy to do using data macros. A simple ForEachRecord loop with a DeleteRecord action inside the loop will clear everything out. You need to start with the child tables and then move on to the parent tables.
You could optionally just save a backup copy of the app and elect not to include all the data. Then install that empty app and start from there.
- The next step is to import all of the data from the Excel spreadsheet(s) into the app with empty tables. When you import, provide different names for the new tables that will be created. You now would have all of our original master tables with no records as well as new tables with all of the live data.
- The final step is to create some new data macros that will "copy" the data from the imported temp tables into the master tables. The lookup fields to related tables would also have to be accounted for in this step as well. After this is complete just deleted the imported tables so you are left with just the master tables and objects.
That might all seem a bit scary at this point, but trust me, it can be done. I've done this very thing many times and even on some very large Access web apps that I've helped created that are being used throughout the organization here at Microsoft.
So what I would suggest at the moment is keep plugging away on what you've been doing so far. Keep modifying and adding to the app as you have been doing until you've think you reached a point where you are ready to go live. Once you've reached that point, then we can take care of getting the correct data into the app, turn your users loose on the web app, and then tell them to stop using the Excel spreadsheet going forward. You can continue to make more changes to the app even after it is use as well.
Does that help?
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation
Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
