Automaticly importing data

[jjsaw5 via AccessMonster.com]

Hello again,

Here is my latest problem. I am importing data from another database into
Access. As of right now i have 3 linked tables and an "Make Table Query" that
i would like to run everytime Access starts to update a table with any new
information that has been added into my linked tables.

Does anyone know how i can do this, or if it is even possible?


If its not pushing it is there anyway i can get Access to update my table
every 15-20 minutes?



Once again thanks in advance for your help!

IF you need any clarification i check this all the time so i'll get back to
you ASAP!

 

[John W. Vinson]

Hello again,

Here is my latest problem. I am importing data from another database into
Access. As of right now i have 3 linked tables and an "Make Table Query" that
i would like to run everytime Access starts to update a table with any new
information that has been added into my linked tables.

Does anyone know how i can do this, or if it is even possible?

The first question is... WHY would you want to do this? If you have the linked
tables, why do you want to copy the data redundantly into a local table?

It may be appropriate... but you may want to consider whether this whole
exercise is necessary!

That said: yes, you can have a Form specified in the Tools... Startup menu
that opens when Access opens. In its Open event you could run your queries.

If its not pushing it is there anyway i can get Access to update my table
every 15-20 minutes?

Well, yes... you Could have the Timer event of this auto form set to rerun the
query. For 15 minutes use a Timer property of 900000 (milliseconds). The
problem would be that if you are USING the local table the code won't work
because it will be unable to delete the (in-use) table to make a new one.

This whole excercise sounds like the wrong way to do things to me... MakeTable
queries are VERY rarely appropriate in a production environment, they're
inefficient, hard to control, and cause bloating. At the very least consider
having a permanent local table and, rather than making a new table every time,
empty the local table and Append data into it. Better yet would be just to use
the linked table!

John W. Vinson [MVP]

 

[jjsaw5 via AccessMonster.com]

I understand where are coming from, but the users are only pulling certain
information from these tables. The access to the database that i have linked
is read only so, they want to be able to edit the information from the tables,
and they only want a certain status of work Example: they want all completed
work in a table.

But thank you for the help i'll try it out!

Hello again,

[quoted text clipped - 4 lines]

Does anyone know how i can do this, or if it is even possible?

The first question is... WHY would you want to do this? If you have the linked
tables, why do you want to copy the data redundantly into a local table?

It may be appropriate... but you may want to consider whether this whole
exercise is necessary!

That said: yes, you can have a Form specified in the Tools... Startup menu
that opens when Access opens. In its Open event you could run your queries.

If its not pushing it is there anyway i can get Access to update my table
every 15-20 minutes?

Well, yes... you Could have the Timer event of this auto form set to rerun the
query. For 15 minutes use a Timer property of 900000 (milliseconds). The
problem would be that if you are USING the local table the code won't work
because it will be unable to delete the (in-use) table to make a new one.

This whole excercise sounds like the wrong way to do things to me... MakeTable
queries are VERY rarely appropriate in a production environment, they're
inefficient, hard to control, and cause bloating. At the very least consider
having a permanent local table and, rather than making a new table every time,
empty the local table and Append data into it. Better yet would be just to use
the linked table!

John W. Vinson [MVP]

 

[jjsaw5 via AccessMonster.com]

I understand where are coming from, but the users are only pulling certain
information from these tables. The access to the database that i have linked
is read only so, they want to be able to edit the information from the tables,
and they only want a certain status of work Example: they want all completed
work in a table.

But thank you for the help i'll try it out!

Hello again,

[quoted text clipped - 4 lines]

Does anyone know how i can do this, or if it is even possible?

The first question is... WHY would you want to do this? If you have the linked
tables, why do you want to copy the data redundantly into a local table?

It may be appropriate... but you may want to consider whether this whole
exercise is necessary!

That said: yes, you can have a Form specified in the Tools... Startup menu
that opens when Access opens. In its Open event you could run your queries.

If its not pushing it is there anyway i can get Access to update my table
every 15-20 minutes?

Well, yes... you Could have the Timer event of this auto form set to rerun the
query. For 15 minutes use a Timer property of 900000 (milliseconds). The
problem would be that if you are USING the local table the code won't work
because it will be unable to delete the (in-use) table to make a new one.

This whole excercise sounds like the wrong way to do things to me... MakeTable
queries are VERY rarely appropriate in a production environment, they're
inefficient, hard to control, and cause bloating. At the very least consider
having a permanent local table and, rather than making a new table every time,
empty the local table and Append data into it. Better yet would be just to use
the linked table!

John W. Vinson [MVP]

 

[jjsaw5 via AccessMonster.com]

I understand where are coming from, but the users are only pulling certain
information from these tables. The access to the database that i have linked
is read only so, they want to be able to edit the information from the tables,
and they only want a certain status of work Example: they want all completed
work in a table.

But thank you for the help i'll try it out!

Hello again,

[quoted text clipped - 4 lines]

Does anyone know how i can do this, or if it is even possible?

The first question is... WHY would you want to do this? If you have the linked
tables, why do you want to copy the data redundantly into a local table?

It may be appropriate... but you may want to consider whether this whole
exercise is necessary!

That said: yes, you can have a Form specified in the Tools... Startup menu
that opens when Access opens. In its Open event you could run your queries.

If its not pushing it is there anyway i can get Access to update my table
every 15-20 minutes?

Well, yes... you Could have the Timer event of this auto form set to rerun the
query. For 15 minutes use a Timer property of 900000 (milliseconds). The
problem would be that if you are USING the local table the code won't work
because it will be unable to delete the (in-use) table to make a new one.

This whole excercise sounds like the wrong way to do things to me... MakeTable
queries are VERY rarely appropriate in a production environment, they're
inefficient, hard to control, and cause bloating. At the very least consider
having a permanent local table and, rather than making a new table every time,
empty the local table and Append data into it. Better yet would be just to use
the linked table!

John W. Vinson [MVP]

 

[John W. Vinson]

I understand where are coming from, but the users are only pulling certain
information from these tables. The access to the database that i have linked
is read only so, they want to be able to edit the information from the tables,
and they only want a certain status of work Example: they want all completed
work in a table.

But thank you for the help i'll try it out!

I'd use the Append query rather than a MakeTable query; and rather than having
it refresh the data on a timer (which would almost certainly disrupt what the
user is doing when it runs), put a command button on the form to run a Delete
query to empty the table, and an Append query to repopulate it. Then the user
could reload current data on *their* schedule.

John W. Vinson [MVP]