Append /Update Querys

[bnickelsen]

Let’s say I have a text (Delimited) file.
I have a linked table to this file.
The file is updated weekly.
I want to track each update.

First question: Can you have both an Append and an Update query from the linked table to the tracking table? Or will it take some code to pull that off.

Second question: Is there a way to have the Query only run on a given day and some how set it to the correct week? In my table I would have a table of "Weeks" set with a 1 to many relationships to the tracking table.

 

[KenHigg]

I don't see why you shouldn't be able to read from the linked table and use the data to update and/or append to the Access table. Have you experienced any problems?

 

[bnickelsen]

I am still learning as I go...

I Guess I am still trying to understand how Append and update work.

If I am understanding correctly
Append add new records.
Update changes records.

So for this task, I would need an append query to add new Jobs.
And then an update for the ones I already have.
And if I understand, then each week I need to open the database and run each query.?.

If Above is true I then assume I can make a macro to fire when the data base is opened to run the query.?.

 

[KenHigg]

Um...

You have the update/append correct.

That sounds like it would work. You may need to do some code stuff to check and see if the new weekly txt has already been appended - you wouldn't want to accidentaly append it twice...

I think I would strongly consider doing a button on some type admin form that would run the append/update routines instead of trying to do an automatic thing. Does this make sense and sound like it would work? Say every monday, someone would be assigned to run the update?

 

[bnickelsen]

That would be posable.

 

[KenHigg]

Here's another thought - In your table that you want to append from, is there a primary key or may a couple fields that you can make composite primary key out of? That way, if the append had already happened, the pk's would prevent duplications. Make sense?

 

[bnickelsen]

Yes every Job has a uniqe number, I planed on using that as the filter.