Make table from linked table ALWAYS errors - out of range

[oliviar]

Hi Guys,
Trying to turn a linked table into an access table, several times a day. Because these linked sheets are dynamic, they are continually growing.

In order to update the access tables with their data, I need to run a make table query, then the update query.

However, it is maddening that I ALWAYS get:
"You cannot record your changes because a value you entered violates the setting define for this tables or list"

I fix it, by going into the make table query, and saving it. And then running it. I don't even need to change anything! I just need to go into the design, save it, then run it.

But! Its maddening. Is there a way to let access know that its cool, I know that the ranges have changed, and just run the **** query?

 

[Joe4]

I don't know if this night be what is going on, but I am not a big fan of automated processes that use Make Table queries, and I will tell you why. Based on what Access sees at the table at that time, it is going to try to determine what the format of each field can be, and it could guess wrong. Also, a standard make-table query will not set Primary Keys or Indexes for you, or other validation rules.

What I prefer to do is while creating the project, run my Make Table Query once. Then go into that table and make adjustments where needed (formats, indexes, unique keys, etc). Then, in my automated process, rather than blow away the table each time and re-build it using a Make Table Query, I will simply delete all the records from that table and then use an Append Query to write the records to it. That way, I can ensure the table has all the attributes I desire and is maximized for efficiency.

 

[oliviar]

So if I automate the deleting, I can automate the appending?

 

[Joe4]

Sure! Just create the Append Query, and open it to run it (just like you can automate any other type of Query, i.e. Select or Make Table Query) using the OpenQuery actions.

To automate the deleting, you can use the RunSQL command and use this command:

DELETE [TableName].* FROM [TableName]