Query not working after recreating Table

[Firasath]

Hi,

I have to maintain a Month-To-Date complaints dump that will import in to Access on daily basis. I have created a Query with calculated columns based on that Table to calculate the various KPIs which is working fine. Now the problem I'm facing is when I try to import and overwrite the Table with the same name, the query is not working. It is asking to refer the values of the fields.

I tried linking the table, and it worked fine. But is is making the Access very slow.

How can I update the table with updated MTD data on daily basis without disturbing the Query.

Kindly advise.

 

[Joe4]

Don't delete/recreate the table.
Simply delete all the old data, leaving the table structure, and import the new data into that empty table structure.

 

[Firasath]

Don't delete/recreate the table.
Simply delete all the old data, leaving the table structure, and import the new data into that empty table structure.

Thanks for your prompt response.

Do I need to do it through VBA Code only or can I do it without that also.

 

[Joe4]

No VBA necessary. You can either do it manually, or through Macros.
Is there a certain part you are having trouble figuring out how to do?

 

[Firasath]

I'm comfortable with deletion of all records and keep the structure intact, but have doubts about inserting the new data in the table. Appreciate if you can guide me.

 

[Joe4]

In the same manner in which you import records into a new table, you can import them into an existing table. You just elect to import into an existing table instead of a new one.

Is that what you are referring to, or is there more to it than that?
What type of file are you importing?

 

[Firasath]

In the same manner in which you import records into a new table, you can import them into an existing table. You just elect to import into an existing table instead of a new one.

Is that what you are referring to, or is there more to it than that?
What type of file are you importing?

Got it. You mean to say I can use the option of Append Data. First delete the all data and then Append a new data.

I am using Excel to import.

Thanks a lot.

 

[Joe4]

Yes, that's it.

One other advantage to that is if you have used any special properties on your table fields (Custom Formatting, Validation, Indexing, etc), you don't need to re-do it every time.

 

[Firasath]

Thanks a lot for your support. Appreciated. I'm planning to put a code for it in a Form button. ​I'll try tomorrow from Office and then update you.

 

[Firasath]

Thanks Joe, it worked fine.

Facing some other issues for which raised two more threads separately. If you could please have a look on the recent one and advise.