Solved Showing the Date when I Append Records to a Table

[Weekleyba]

I have a small database with one table.
I append the table weekly.
I would like to automatically show on the main form, the date of the last update to the table.

How can I do this?

 

[theDBguy]

Hi. You could try creating a custom function to read the "last modified" property of the table (although I don't know how accurate that could be), or you could simply add the date you imported the data (or store it in another table), so you can easily get it back. Just some thoughts...

 

[Weekleyba]

Thanks. I’ll work on that tomorrow.
Really appreciate the help!!

 

[Weekleyba]

Pat,
I've been working on this today and find myself stuck on multiple fronts.

Three things:
1. Update Query - I really want to learn how to do this. Attached is my attempt in creating an update query to change the table field CreateDT to the current date. Right now, if the field is blank, nothing happens. If I have a date in it, the query will change it to "12:00:00AM" Can you look at my update query and tell me where I'm going wrong?
2. Updating the Table - I thought Appending the table was what I needed but that was wrong. Appending adds the records to the table, doubling up the records. I want to replace all the records with new. Is it best to just overwrite the existing table with the same name? Of course when I do that, my CreateDT field disappears. And I'll need to close the Main_F first since it has the table as the record source. Unless there's a way around that?
3. Even when I had the CreateDT field in the table and the textbox on the form, Main_F, with the Control Source (=DMax("[CreateDT]","IHS - Allowance Status by Locat"), I couldn't get it to work. So I'm still stuck on that to.

Spent the last two hours searching the web but coming up short on all these.

Could you take a look at my db and help me out?
Thanks.
Brad

 

[theDBguy]

Hi. All I had to do was change the UPDATE query to this and the form worked.

UPDATE [IHS - Allowance Status by Locat] SET [IHS - Allowance Status by Locat].CreateDT = Date();

 

[Weekleyba]

Thanks DBguy, that solves problem 1. Nice!
Any thoughts on 2 & 3?

 

[theDBguy]

Thanks DBguy, that solves problem 1. Nice!
Any thoughts on 2 & 3?

Hi. The image above should cover #3, right?

 

[Weekleyba]

#3 is about setting the CreateDT when I update the Table.
But how can I update the table?
Appending: It will not let me append, most likely since it has the existing table has the extra field CreateDT.
Importing: This deletes the existing CreateDT field and leaves at a blank field.

So, I just I need help with how to update the table while keeping track of date the table with last updated.
I guess I'm back to my original problem still.

 

[theDBguy]

#3 is about setting the CreateDT when I update the Table.
But how can I update the table?
Appending: It will not let me append, most likely since it has the existing table has the extra field CreateDT.
Importing: This deletes the existing CreateDT field and leaves at a blank field.

So, I just I need help with how to update the table while keeping track of date the table with last updated.
I guess I'm back to my original problem still.

Okay, let's go back to the beginning. Where does the data come from?

 

[Weekleyba]

The data for table "IHS - Allowance Status by Locat" comes from online program that I use.
I export the data to an Excel file and then import into my db so I can manipulate it into some user friendly reports.
I'll need to import this everyday, so I want to make it as easy a process as possible.

 

[theDBguy]

The data for table "IHS - Allowance Status by Locat" comes from online program that I use.
I export the data to an Excel file and then import into my db so I can manipulate it into some user friendly reports.
I'll need to import this everyday, so I want to make it as easy a process as possible.

Okay, next question, do you want to keep the previous day's import or do you just replace it with today's import? Also, when you said you wanted to show the date of the last "update" to the table, can we assume you mean the last time the data was "imported?"

 

[Weekleyba]

No, I do not need to keep the previous day's import.
Yes. The last update does mean the last time the data was imported into Access.

 

[theDBguy]

No, I do not need to keep the previous day's import.
Yes. The last update does mean the last time the data was imported into Access.

Okay, in that case, to make it simpler, I would just create a new table with only one column and one record in it to store the date when you import the data. So, when you import the data, make sure you update this table to show the new date. In your main form, point the DLookup() expression to that new table. If you're doing the import manually, just update the table manually too. If you're using code to import the data, then add some code to update the table with the new date as well. Hope that helps...

 

[Weekleyba]

So, just do it manually?
I was looking for more automated way.

Is there not a way to do this automatically when the table is imported?
That way, when I had this over to Admin, there's no way for them to forget putting in the Last Update date.
It really does makes a difference on when the data was last imported to those using the db.

Any other suggestions?

 

[theDBguy]

So, just do it manually?
I was looking for more automated way.

Is there not a way to do this automatically when the table is imported?
That way, when I had this over to Admin, there's no way for them to forget putting in the Last Update date.
It really does makes a difference on when the data was last imported to those using the db.

Any other suggestions?

Hi. Like I said, it depends on how you were doing the import. If you were doing it manually, then you can update the date in a separate table manually. But if you were doing it using automation, then you should also be able to automate updating the date.

So, how exactly are you or your users importing the data from Excel into Access? If by code, maybe you could post it here, so we can show you how to add the part to update the date.

 

[Weekleyba]

Pat - You are correct. It was not working for me because I had set a filter on the Excel file that I was trying to use to update. My bad.
It does work now, if I don't mess with the Excel file.

One last thing...
After I append the table, how can I tell the Last Date text box to refresh? Automatically I mean.
Would it be on the forms Current Event?
The Main_F is not bound.

 

[Weekleyba]

Pat,
I may be going crazy here because I thought I had this working but now.....it's not.
When you append the table, is it suppose to add all the records to the existing table? I do not want this. It just doubles the number of records!
If Import the source data, it deletes the other records, which is fine, but it also deletes the CreateDT field, not fine.
Not sure what to do now about the CreateDT field.
Suggestions?

 

[Weekleyba]

The source for the Excel spreasheet is an online program called FBIS. I log into it and input my criteria, and export the data into an Excel spreadsheet.
My plan then was to import that data into my db for all the engineers to easily view in a much more viewer friendly reports.
But the data that I export to Excel changes all the time. So append is out.
I would be fine with just importing it, while wiping out the existing table data.
The only problem with that is not being able to automatically have the CreateDT work. Since it deletes that field.
Maybe I’m down to just having to have the admin manually put in the date each time.
Does that clarify?
Thanks for looking at this for me.

 

[theDBguy]

Hi. In Post #15, I made a suggestion, and in Post #18, I asked a question. Just wanted to bring your attention to them, just in case I am still able to help. Cheers!

 

[Weekleyba]

DBguy, I always appreciate your help.
In previous posts I’ve explained where the Excel file that I import comes from.
Now that I have the Excel file, I need to import it. The only way I know how to import is manually. Therefore your solution for the update date, is to add it manually.
Do have any examples on how to import an excel file, completely erasing the existing, via VBA? Then perhaps I could figure out how to write the code for updating the date.
Idea: If I stored the excel file in the same location and called it the same name every time, would linking the table work for me? Still need some code to perhaps push a button and update via the link. I’d have to delete the old excel file prior to placing the new updated file with the same name. Would that break the link?
I’m just not sure in this.
Thanks for helping!