Lookup Data Type in Access 2013 Web App (1 Viewer)

Hi,
Very new to Access, so be gentle with me!! I guess this is a general "can it be done" question.
I want to create a Web App in Access 2013 that contains a table of client names, addresses etc, and a second table that contains order details, including client name. It would be nice to ensure that as someone adds a new order they are give a drop down menu containing existing client names, and I can see how you can do this for a brand new table using the lookup data type. But I already have an Excel spread sheet containing client names. If I import this into my Access web app to create the client table, and import the existing orders to create the order table, I then try and change the data type of the client name (in the order table) from short text to lookup, it won't let me!!
(If I create an empty client table from scratch, it lets me define the company name as a lookup data type - but I can't then import from Excel into this empty table)
I can't believe that what I'm trying to do is not possible, but I can easily believe that I'm not going about if the right way...
Any suggestions?
Many thanks.
Jane

Hi Jane,

I believe I can help you with this.

A quick question for you since you mentioned you are creating an Access 2013 web app. Are you using Office 365 to host this Access web app or are you using an internal SharePoint/Access Services setup at your organization?

The behavior you describe below is expected based on what I'm understanding from your comments. I assume you were getting some type of data type conversion error when trying to convert the Short Text field to a Lookup field, correct? The reason for this is a Lookup field is actually going to store the ID value from the related table (a number) and your existing text data can't successfully be converted to an ID value.

To answer your question, yes, you can certainly get your two Access web tables to perform as you would expect and like. Using the lookup field is the correct way to go in an Access web app because you can define a relationship that will prevent orphan records in the Orders table and ensure that only existing client names be used in the Orders table.

Now to reach your end goal, there are actually many ways to do this but each will require a little bit of work on your part to "clean up" the data and get the appropriate relationships defined.

One way you can do this is to import your two Excel sheets into an Access desktop database first. From there you can use queries and perhaps some data macros or VBA code to get the data correctly mapped using a Lookup field defined within the desktop database. Once everything is correctly setup you can them simply import the two tables from the Access desktop database into the Access web app in one operation. During the import, Access will create the appropriate relationships on the SQL Server tables used as the base for the Access web app.

The other option is to import both Excel sheets into the Access web app. No relationship exists at this point. You can then create some data macros to write the "correct" data into a new Lookup field and then delete the no longer needed original field that holds the text data. Either option will get you to your final result.

I'm going to assume you'd like some more step by step instructions on how to proceed, correct? I'd be happy to help you get his set up. I can walk you through the steps so you can learn more about what is going on and how everything is linked together. I think I would go with the second option since you already seemed to have imported the two Excel sheets into the web app. I can help you create the needed data macro to get the data cleaned up.

Some questions for you before we start:
1. Could you tell me the names of your two tables?
2. Could you tell me the names of the fields in your tables and their data types?
3. About how many records do you have in each of the two tables?

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

Jeff,
Thank you so much for offering to help me - I'm eternally impressed that people give up their time to do this kind of thing!!
To answer your questions - Yes I'm using Office 365 services, rather than hosting on an internal server.
My two tables are Customers and Orders.
At the moment, Customers just contains one field - Customer Name - a short text field. I may add addresses later on....
Orders contains the following fields;
Customer Name - Short Text
Oder Date - Date
Quantity - Whole Number
Total cost - Currency
Item Cost - Currency
Contact - Short Text

At the moment we have about 150 Customers and around 400 orders....

Thanks again for your help...

Hi Jane,

No trouble at all.

Thanks for the additional information on your setup. I've created my own spreadsheet with two tabs of information, added in columns using the names you provided, and put in some dummy data. I did this so I can follow along with your setup.

The first step I'd like you to take is to do either one of these two options based on your preference and data status.

1. If the data in the web app is not current (stale data compared to the spreadsheet) then delete both of the tables and all other objects in your current web app so as to start fresh. Note that deleting the tables in the web app should delete the quick created views as well. If you just have the table structure but no data in the web app, then go ahead and delete those tables as well. We'll do a fresh import of the Excel data. (The reason for this is whenever you import data into a Access 2013 web app, it imports as a new table. You cannot import data into an existing table in an Access 2013 web app using the current import wizard dialog.)

Next, import the spreadsheet tabs of information you are currently using (one at a time) into the empty web app shell so we have a clean starting point again. If the first row of data in your Excel sheets is column headers, then be sure to check that option in the import dialog wizard.

Don't do anything else at this point like changing the tables or views so your starting point will match my starting point.

2. If your web app data is more current than the spreadsheet data we should probably start here instead of doing a new import. Don't delete anything then and we'll work from this starting point. Just confirm your field names match what you previously provided.

Let me know when you are at this point and I'll walk you through the next steps. I want to ensure I'm following along right where you are.

Thanks,

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

Jeff,
If it seems I take a long time to respond, it's because I have to do my day job as well (recruitment consultant!), so don't get nearly as much time on this as it probably needs.....
We're still using the perfectly serviceable Excel spreadsheet to manage orders and job postings and fortunately I'm not under any specific time constraints in which the replacement app has to be up and running. At the stage I'm at, its easy just to delete anything I've done in Access and start from scratch.
So I have just created a brand new App (Test1) and imported the data from Excel - into the two tables, Orders and Customers. Awaiting further instructions!!

Hi Jane,

No trouble at all on the delay. We'll get you up and running in no time.

Now that we're both at the same starting point, please follow these steps in order. If you have any questions or issues with any of these, just let me know.

1. Open the Customer table in Design view.
I would mark the Customer Name field as Required since we don't want this field to be null for any records. So change the Required property to be Yes at the bottom of the property sheet for this field.

2. I would rename the autonumber ID field that Access created to be CustomerID instead of just ID. (My personal preference here instead of having ID for each table that Access defaults to.)

3. Save changes to this table and close it. Note, Access will probably display a message indicating data integrity rules have changed. Just click Yes on this dialog. (Let me know if this doesn't save properly.)

4. Open the Orders table in Design view. Be sure you closed the Customers table because it will need to be closed for the next steps.
Rename the ID field here to be OrderID. (Again, personal preference.)

5. For the Order Date field, do you need to store and display the time as well as the date portion? Access probably defaulted to using the Subtype Date with Time for this field. If you don't need the time, change the Subtype to Date on the Property Sheet and make the Display Format property set to Short Date. This step is optional of course, but I changed it in my sample.

6. Double check the Total Cost and Item Cost fields. When I imported these, Access created these as Number fields. You might want these as Currency fields instead. I'd like to revisit these two fields with you after we get the Lookup field taken care of. One of these might make better sense as a Calculated field.

7. Save your changes to the Orders table so far. Note that Access might display a message that some data may be lost because you adjusted the data types. Click Yes to continue.

8. Ok, now let's work on changing the Customer Name field in this table to be a lookup to the customer names stored in the Customers table. Rename the existing Customer Name field in the Orders table to be Customer Name Old.
Save your changes again.

9. Insert a new field beneath the Customer Name Old field and name this new field Customer Name. Select Lookup as the Data Type for this field. Access opens up the Lookup Wizard dialog. Select these options:
- I want the lookup field to get the value from another table or query
- Select the Customers table
- Select Customer Name where it asks, "Which value do you want to display in your lookup?"
- Leave the default sort option as is.
- Be sure the relationships option is set at the default - "Prevent delete if there are corresponding records in the Orders table."
- Click Ok on the dialog to commit the options after you selected everything.

10. Save your changes now to the Orders table again.
At this point there is no data in this field for any records. That's fine, we'll populate this data next.

Let me know when you've completed all of these steps and if you ran into any issues.

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

Jeff,
I've done this - no problems.
Thanks.
Jane

Hi Jane,

Perfect, glad to hear everything is going fine so far.

Here are the next steps to complete.

1. Create a new standalone data macro which we will use to populate the new Customer Name lookup field in the Orders table.
Start by closing all open objects and then click the Advanced button on the Home ribbon tab (it's in the Create ribbon group).
Select the Data Macro option from the drop-down list of choices beneath the Advanced button.

2. Access opens a new empty data macro and displays the macro design surface. Have you created any data macros before in Access 2013 web apps or 2010 style web databases? Data macros are very powerful. Not to worry if you haven't, I have a completed one that you can just copy.

3. Essentially we want to do the following (in English):
- Loop through each record in the Orders table one at a time
- Within that loop we will read the current value in the Customer Name Old field. (The one that has the text data of the Customer Names.)
- We will then attempt to find that customer name within the Customers table using a LooukupRecord action.
- Once we find that matching record, we will grab the CustomerID field value which is the unique identifier for the record. We will save that value in a local variable
- We then will write that local variable into the new Customer Name lookup field in the Orders field for the record we are currently reviewing.
- We then move to the next record in the Orders table and complete the same steps above. We repeat this process until all Order records have been processed.
- In theory, when finished, there should be data in the Customer Name field for each record in the Orders table.

All make sense?

4. Ok, let's get started.
Here is a screenshot of what the data macro logic will look like - I'm hoping your field names match my example perfectly. Take a few minutes and just study this to understand the macro logic in place and it how it relates to my explanation above. Note that I've added in extra macro comments to further explain.



5. You can either type/input that into the empty macro window manually or just copy and paste the below logic I have posted here. To copy and paste this logic, do the following:
- Copy the logic below (it is in XML format) to your clipboard. Be sure you start exactly with < character below and the ending > character and copy that all to your clipboard.
- Click on the macro design surface away from any other objects - just click the empty white surface so your focus is there.
- Now press CRTL+V to paste all that macro logic from your clipboard onto the macro design window.
- It should just all appear now magically in your window. Let me know if this step doesn't work.

Here is the logic:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro><Statements><Comment>This data macro will loop through all the records in the Orders tables and fill in the corect related ID value for the Customer Name lookup field.</Comment><ForEachRecord><Data><Reference>Orders</Reference></Data><Statements><Comment>Find a match in the Customers table where the name matches what is in our current short text field in the Orders table.</Comment><LookUpRecord><Data><Reference>Customers</Reference><WhereCondition><Expression><Original>[Customers].[Customer Name]=[Orders].[Customer Name Old]</Original><FunctionCall Name="="><Identifier Name="Customers.Customer Name" Index="0"/><Identifier Name="Orders.Customer Name Old" Index="1"/></FunctionCall></Expression></WhereCondition></Data><Statements><Comment>Now that we found a match, set a local variable equal to the CustomerID value for this record.</Comment><Action Name="SetLocalVar"><Argument Name="Name">varCustomerID</Argument><ExpressionArgument Name="Value"><Expression><Original>[Customers].[CustomerID]</Original><Identifier Name="Customers.CustomerID"/></Expression></ExpressionArgument></Action></Statements></LookUpRecord><Comment>Now write this data into our new lookup field.</Comment><EditRecord><Data/><Statements><Action Name="SetField"><Argument Name="Field">[Orders].[Customer Name]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varCustomerID]</Original><Identifier Name="varCustomerID"/></Expression></ExpressionArgument></Action></Statements></EditRecord></Statements></ForEachRecord></Statements></DataMacro></DataMacros>


6. Save and close this new named data macro. Name the macro something like dmFillInLookupData. Let me know if this fails to save for any reason. If it fails to save, you won't be able to continue with the next steps until that is resolved.

7. Now that we have the completed data macro logic, we have to execute it. We have to call it somehow. There are many ways to execute this, but let's take a simple approach of adding a temporary command button to one of the views to call it.

8. Open the Form/View called Customers List in design view. In Access 2013 web apps we use the term views to describe user interface forms used for data entry in the web browser. They are listed though under the Forms heading in the client Navigation Pane. Confusing? Yes I agree. Just remember that a View = Form in an Access 2013 web app.
- Once you open the view in Design view, click the Command Button option in the Controls group on the ribbon and Access will place a new command button control onto the form design grid.
- Select the command button control and three charm buttons appear next to it. Click the charm button that has a lightning bolt on it- this is the Actions charm.
- You see the Actions flyout menu and there will be one option called "On Click" since command button controls support this event. Click On Click and you'll see a macro window open here modally.
- In the drop-down list on the empty macro window select the action called RunDataMacro. Access places the action on the design window.
- There is one argument for this action (Macro Name) with a drop-down list. Select the dmFillInLookupData run data macro option from this drop-down list here. This is what we named it earlier.
- That's all we need to do for the design here so click Save on the ribbon to save your macro changes and then click Close to close the macro window.
- You should be back on the view window now. Click Save again to save your view changes.

9. Now that you've created a temporary place to run this named data macro one time, let's execute it in the browser. Click The Launch App button on the ribbon to open the app in your web browser.
- Navigate to the Customers table and then the List view in your browser (it will probably open to this view by default).
- Click the command button one time to execute it. Let me know if there are any errors here.

10. The data should be set so let's do a quick check. Go back to Access client and open the Orders table in datasheet view within client. Do a quick scan comparing the values in the Customers Name Old field and the new Customers Name lookup field. They *should* appear to be all identical.
Do you have any blanks for the new Customers Name field in the Orders table at this point? Let me know if there are any blanks.

OK, let me know how you progressed through these steps and the results of the data in the Orders table. I'd like to check your progress here before we do all the final cleanup work. Don't delete anything yet.

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

Jeff,
Thanks so much for all your help. I'm going to be out today and most of tomorrow, so I'm going to try this over the weekend. I will report back on Monday.
Thanks again.
Jane

Hi Jane,

No problem at all.
Just post back when you have time and let me know the status of how things went up to this point.

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

Jeff,
You're a genius!. All worked perfectly and I have no blanks in the table. (I couldn't open the screen print you sent for some reason, - I do have internet explorer set up to show pictures - but I didn't need to see it as following your instructions everything worked as it should). I now have the Customer Name and Customer Name Old showing identical data in the Orders table.
Thanks
Jane

Hi Jane,

Excellent! I'm pleased to hear you have successfully transitioned the customer data into the new lookup field.

Now let's do some cleanup work.
Please follow these remaining steps.

1. Delete the command button control from the view called Customers List since you no longer need it. Save and close that view.
2. Highlight the dmFillInLookupData data macro object in the Navigation Pane and delete that as well since it is no longer needed.
3. Open the Orders table in Design view and then delete the field called Customer Name Old since that is no longer needed as well. This is the field that is the Short Text data type. Our new Customer Name lookup field is the one we want to keep. Save your table changes here now.

You should be all cleaned up now!

Since this is a lookup field now, you must remember that the CustomerID value from the Customers table is what is actually stored in the Orders table. Access displays the customer name to you in the UI controls, but it actually stores the related CustomerID in this table and maintains the data integrity.

I have some follow-up questions for you if you don't mind.

Are you familiar with how to use the new Autocomplete and Related Items controls in the 2013 web apps? These are two new control types. On the Orders List and Datasheet views, Access created an autocomplete bound to this field. Access should also have created a new Related Items control on the Customers List view. If you'd like, I can briefly describe how to use these two controls if you're not familiar with them. Just let me know if you'd like some information on that.

In the Orders table you have these three fields:
Quantity
Total Cost
Item Cost

Am I to assume that you are using this data like so?:
Quantity * Item Cost = Total Cost
If so, are you filling in those values by hand or were you using a formula in the Excel sheet to calculate the Total Cost?

If so, we should consider changing the Total Cost field in this table to be a calculated field. Do you know how to do that?

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

Jeff,
Sorry for the delay in responding - I've been busier than I was expecting on the recruitment side - good for business bit bad for application development!!

I have done the tidying up. Thanks. In answer to your questions, no I'm not familiar with the controls you mention. To be honest, I'm not familiar with anything in Access - I used to be in IT, but my database and programming skills are of absolutely no use here, being based on prehistoric Mainframe technologies, so it's all an exciting voyage of discovery for me!!
You're right, the Total cost field should be a calculated field - just haven't got round to working out how to do that yet.
Thanks again for all your help - I appreciate the time you give up for this.

Jeff,
Feeling a bit smug now as I have made some progress - creating three more tables - Employees (just one field - Employee Name), Contacts (Two fields - Customer name and Contact Name) and Postings (Fields are Date, Customer Name, Position, Contact Name, Employee Name) - This is to track job posting requests from our customer base. Using your Data Macro as a template, I have managed to set up the Customer Name, Contact Name and Employee Name in the Postings table as lookup fields. One other bonus is that I'm improving my language skills as if I make a mistake, it tells me what I've done wrong in either German of French!!

Hi Jane,

Apologies myself as well for the delay in checking back.

I'm pleased to hear you've made progress on adding more elements to your app.

Let me first get back to some of the earlier elements.

1. I think it might be good to change the Total Cost field in the Orders table to be a calculated field. Although you could do this in a query, for the web app context a calculated field at the table field might be better for performance reasons.

You can delete the existing Total Cost field in this table and then save changes. Add in a new field, you can use the same name as previously, and then select Calculated for the data type. Access will open the Expression Builder dialog. Enter the following expression into the dialog:
=[Quantity]*[Item Cost]

Close the Expression dialog when finished. If you want to display this as currency on the views, then change the Display Format at the bottom of the table design window for this field to Currency. Save your table changes and then you should be good to go. Access will take care of this calculation for you whenever you add new records or update the two fields that are used in the expression.

2. The Related Items control is a new control type that displays related records at runtime which follows lookup fields. Open the List view for the Customers table in your browser and you'll see how this works.

Note that it might display an error because we deleted the Customer Name Old field in a previous step. If you see an error, open this view in client designer to fix following these steps:

- Click the control on the design surface
- Click the Orders label caption on the top left of the control
- You'll see three charm buttons appear
- Click the Data charm button - the first one
- You'll see options for assigning a data source, related field, display fields, and caption. The one in red is the Customer Name Old field. Change that to be Customer Name using the drop-down list.
- Save your changes and then refresh the view in your web browser

Now that it is fixed, you should see related order records as you select different customers from the list control on the left in your web browser. The Related Items control can display up to four columns of data. If you click on a record inside the control, Access will open a different view as a popup where you can see all the details of that record. A very handy feature.

3. For lookup fields, Access defaults to displaying these in an Autocomplete control. This type of control is also new. It is like a text box+search feature+drop-down list all in one.

If you begin to type letters into this control, Access will search for those characters in the lookup field and display up to eight items in a drop-down list. If you continue typing, Access will look for matches based on the new text. In this way the list should get smaller as you type more characters. You can select the correct related lookup value whenever you see it in the list. You don't have to type all the characters; you just type enough characters until you find the one you want and then select it.

Does that make sense?

I'm glad to hear you've made additional updates to the app and are adding in more elements. That's great to hear you've been able to create other data macros to populate other lookup fields.
On a personal note, I usually break out employee and contact names into separate fields like First Name, Last Name, Middle Name, etc. It's not required you do it this way, but I thought I should point it out.
I would also steer clear of using Date as a field name since Date is a reserved word in Access used for other things. I would use Order Date or Hire Date or something like that instead depending upon what specifically the context is.

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

Jeff,
Thanks. I have set up Total Cost as a calculated field.
Just one question on the Related Items Control. I can see how it works in my app, showing me orders associated with Customers, when I'm in the Customers List. Very Nice. Do you know whether it should also show me Postings associated with a Customer, as I am also using a lookup field (looking up Customer Name) in that table?
If it only shows one set of Related Items, how does it decide which one to show?
Thanks.
Jane

Jeff,
Please ignore my earlier mail. I can see that you just add another tab to do this!!
A case of RTFM!!!!!
Jane

Hi Jane,

Looks like you beat me to it on a reply.
Yes, you can add extra tabs to the related items control to display data from other related tables.

A quick note. If you had not previously modified that list view in any way, Access would have automatically added the extra tab for you after you defined the new lookup field. Since we had already made a design change to that list view, Access won't automatically make changes to that view if you make further schema changes. At this point we assume since you made design changes to the list view, you don't want Access to touch it again.

I'm pleased to hear you're working away on your web app.

Good luck with your project!

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

Jeff,
I do have one quick (I hope!) question. I'm trying to set up a calculated field that returns the number of days that a job posting has been active. This is basically todays date minus the posting date. I've tried to research this and came up with DateDiff("d", [Posting Date],Date()), but it's not having any of it. Am I on the right lines?
Many thanks.
Jane

Jeff,
(Now I've had the opportunity to spend a bit of time on this, the questions are coming thick and fast. I'm not sure that to keep bombarding you endlessly is the proper way to get support on this, but if I haven't already tested your patience to the extreme, I have another one....)
As I get more familiar with the technology, modify tables, add views, macros and queries etc, it occurred to me that I may have a problem when the time comes to "go live". As I understand it, I can't add new records to an existing table by copying them over (in bulk) from Excel, so when we go live, I will have to delete all the tables and recreate them from Excel with the up-to-date data, and then presumably re-define the various fields as lookup, re-run data macros etc . What I'm not sure about is whether when I delete a table, will it delete all views, macros, queries etc associated with that table, and if so, then will I be effectively having to recreate the app in its entirety???