External data

[chris]

In my Project database (in Access 2000) I want to view financial
transaction data related to projects, from our accounts software (in a
subform) through an ODBC link and have no problem doing this. However,
if the external data is "linked" this causes Access some problems when
the external data source is not available (eg computer with database
is taken out of the office and there is no network link to the
external data). "Importing" the external data obviously overcomes this
but then the data is not automatically up to date. Importing would
also be desirable when out of office as a snapshot of the external
data can be taken for reference.

My question is: how can I refresh/reimport data automatically (or
manually through clicking a button to run code to do it) when in the
office? I suppose it would also be advisable to be able to test for
the presence of the link to the external data before trying to run
code to refresh it. How can I do that?
Chris

 

[Mark A. Sam]

Chris,

If this were me, I would maintain the linked tables, and use a temp table to
display the data. If you are only viewing the data this would solve the
problem. You could either run a make table query from the button to update
the data, or run a delete query then and append query, maintaining your
indexes. This is an easy way.

God Bless,

Mark A. Sam

 

[chris]

Thanks Mark,
I haven't much experience of action queries so will need to do a bit
of reading and experimenting to fully understand your suggestion. The
external data comes from various places in our accounts software and
is mostly made available in Access as a query already rather than a
linked table. In one case it has to be accessed through a pass-through
query to get the right results.

At a quick look, it seems that using a make-table query based on the
appropriate query or linked table would be the best solution as the
table created is deleted and recreated each time the query is run. You
say that running a delete and then append query would maintain indexes
but I haven't consciously created any indexes in the linked data so
maybe that isn't an issue.

As the database is multi-user I would need to find some way of running
the update daily while the tables are not being used by anyone. Is
there some way that this action could be triggered each time there is
a change from the being zero users. When logging on is there a
property somewhere which gives the number of current users, which I
could use to trigger an update?

When going out of the office I just make a copy of the whole database
for read only use on my travels. It would be useful if new data could
be added when out but options such as replication of the database seem
to have disadvantages in that the auto-numbering system in tables gets
more complicated and as we use these numbers for other purposes, we
have not gone down that route. I know this is a different subject from
the original question topic but if you have any ideas on solutions to
this, I would be most interested. Perhaps I should just post a new
question on this topic sometime!
Chris

However, perhaps for office use where the link to the external data is
available, it is best to use the linked data directly. It is only when
going out of the office and wanting to take a snapshot of the data
that this becomes an issue

 

[Mark A. Sam]

Chris,

If you don't have indexes, then yes a Maketable query is the way to do it.
When you make the table, however, it will be local. I'm not sure why you
are concerned about the number of users unless you are updating (editing).
This method doesn't allow updating and you haven't suggested that you need
to or can update. The way I understand your situation, users compile the
table at anytime, such as when a form opens, when a button is clicked, or
even from the timer event of a form.

When you are travelling you can connect using VPN. You would have to have
someone set that up. They you could go online via a wireless network or an
Aircard. I have never dealt with replication as it has always seemed
untrustworthy and too complicated for em to understand.

God Bless,

Mark

 

[chris]

Chris,

If you don't have indexes, then yes a Maketable query is the way to do it.
When you make the table, however, it will be local. I'm not sure why you
are concerned about the number of users unless you are updating (editing).
This method doesn't allow updating and you haven't suggested that you need
to or can update. The way I understand your situation, users compile the
table at anytime, such as when a form opens, when a button is clicked, or
even from the timer event of a form.

When you are travelling you can connect using VPN. You would have to have
someone set that up. They you could go online via a wireless network or an
Aircard. I have never dealt with replication as it has always seemed
untrustworthy and too complicated for em to understand.

God Bless,

Mark










- Show quoted text -

Mark
In the office all users are accessing the same tables and forms etc
which are on our server (the database has not been split). The way I
envisaged your solution was that I create a temporary table from the
external accounts data through a maketable query, which would be
stored with other tables (on the server) and be available to all
users? All users would need to access these temporary tables to
display read-only data from accounts in various forms in the database.
I would have thought that if the temporary tables were in use by
anyone, they could not be replaced by running a maketable query.

Perhaps you are suggesting that these temporary tables are stored in a
separate local databases on each user's machine. Is that right? It
seemed simpler in one way to have all users looking at exactly the
same data on the server rather than different copies of the temporary
data on their local machines, which may not be quite the same,
depending on when they were last updated.

This is new territory for me so maybe I have misunderstood what you
are suggesting or am not making it too clear what I am trying to do.

Yes, when travelling I could get remote access to our server via a VPN
etc to get the latest data but probably wouldn't need to as I'm not
likely to be out of the office for many days at a time.

Chris

 

[Mark A. Sam]

Mark
In the office all users are accessing the same tables and forms etc
which are on our server (the database has not been split). The way I
envisaged your solution was that I create a temporary table from the
external accounts data through a maketable query, which would be
stored with other tables (on the server) and be available to all
users? All users would need to access these temporary tables to
display read-only data from accounts in various forms in the database.
I would have thought that if the temporary tables were in use by
anyone, they could not be replaced by running a maketable query.

That right. I didn't think that through, but you can run the query though
code and give different table names for different users then assign the
tablename as the form's recordsource when opening the form. I don't have
the time to work that out, but am sure it can be done.

Perhaps you are suggesting that these temporary tables are stored in a
separate local databases on each user's machine. Is that right?

Not if you are sharing a copy. Yes if you are running local copies.

seemed simpler in one way to have all users looking at exactly the
same data on the server rather than different copies of the temporary
data on their local machines, which may not be quite the same,
depending on when they were last updated.

If they are only looking, then add a method to allow them to update. If the
source is updated, then it makes sense to update the clients.

This is new territory for me so maybe I have misunderstood what you
are suggesting or am not making it too clear what I am trying to do.

Maybe I am not understanding you or maybe I am just looking at a solution
without having worked out the details. When I know something will work,
sometimes it takes being on the machine and working it out. Sometimes I try
something which doesn't work, then I try something else until it does. I
didn't consider the sharing of the database when I responded.

 

[chris]

That right. I didn't think that through, but you can run the query though
code and give different table names for different users then assign the
tablename as the form's recordsource when opening the form. I don't have
the time to work that out, but am sure it can be done.




Not if you are sharing a copy. Yes if you are running local copies.


If they are only looking, then add a method to allow them to update. If the
source is updated, then it makes sense to update the clients.




Maybe I am not understanding you or maybe I am just looking at a solution
without having worked out the details. When I know something will work,
sometimes it takes being on the machine and working it out. Sometimes I try
something which doesn't work, then I try something else until it does. I
didn't consider the sharing of the database when I responded.





- Show quoted text -

Mark
I now need to do some experimenting to see what works in our
circumstances.
Thanks very much for the help and ideas
Chris