Access to Access Automatic Updates

[J Streger]

I have a question regarding getting two access databases to talk
to one another. This is a situation where I am getting data from a table in
an access database, but the database is being used strictly for data storage
purposes. The table has no primary key listed and the columns are either text
or number formatted, even dates. The database is one generated by a 3rd party
program, so modifying it is not something we want to do, as if the program
ever changes their database format, we don’t want to have to re-code
everything.

So I created a new access database, and a table that contains
most of the info from the 3rd party one, but adds two columns to denote what
we have done with the information that are not in the 3rd party database. Now
I can write a C# script to update my DB with the info from the 3rd party one,
but I wanted to know if there is an automatic way built into Access that is
easier than reinventing the wheel. Also the information needs to be updated
every minute (between the two DB's)


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Klatuu]

You did not describe how you will know what data has changed in the external
mdb that needs to be updated, so I will leave it up to you. As to the how,
it is not that difficult.

You describe the external mdb as being data only. That leads me to believe
your mdb is not split. It should be. An mdb that contains data should
contain data only. Do some research on splitting your database. It is the
correct way to configure it.

Here are the things you need to do.
Link the tables in the external mdb to your application.
If you have a form that stays open as long as your application is open, use
it for the updates. If not, you will need to create a form that opens on
startup and remains open as long as the application is running. You can hide
the form.
Use the form's timer event to perform the updates.
Set the form's timer interval to 60000. That is one minute.
The Timer event will fire once per minute and execute your update code.

The only issue you have is that if your application is a multi user
application, you don't want this running on every user's computer. You may
want to create a stand alone app that runs only one computer. Use the same
technique. Just link to all the tables involved regardless of what mdb they
are in.

 

[J Streger]

Essentially I want to synch a table from one DB to a table in another. The
external DB will get queried every minute to refresh the internal DB table.
The records are static. Once added to the external DB by another program,
they do not change. So all I need to do, is grab any new records from the
external DB and add them to my new table. This would be a server side
function that is running 24/7 and will also perform maintainence on the DB's.
(once I get the link up, maintenaince code will be easy)

Understand that I am writing a C# application where each client will be
doing queries on the DB every minute. They are not interacting with any
forms/macros/other Access pieces other than querying/updating a table. That
code is already written and working, but I need the batabase maintainence
code.

So the way I see it, I link the tables, and write a script to run every
minute and update the link in access. And that should pull the new records,
correct?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Klatuu]

What do you mean by "database maintenance code"?
Your theory is essential correct. I guess you have it figured out how
determine which records are new.

 

[J Streger]

Initially I was thinking of writing code in C# to maintain the two databases.
I will be pruning the records from the 3rd party DB at regular intervals, and
this pruning needs to flow down to the internal DB. I'm not really
confortable with DB management, although I know basics. So the code I speak
of is my original idea of using C# to update the two DB's, but Access
probably has this minute update feature that I can utilize, only issue is the
tables are not the same.

I can link the table from the external DB to my DB, but from there I am
confused how I synch that data into my table. I can't add columns to the
linked table. Obviously it will probably be better to use Access' functions
rather than writing one of my own, but I know how to do the latter, not the
former.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Klatuu]

If you are familiar with SQL, you could use an Append Query to move the data
between the tables.

The concept of Linking tables is pretty straight forward. Since this will
be a stand alone application, you can just link the tables from both
databases. From the Access menu, it is just Files, Get External Data, Link.
Then navigate to the database from the File Open dialog and open it. You
will get a tabbed dialog. Select tables, and select the file or files you
want from that mdb. You will need to do it for mdb that has tables you need
to include. The links are presistent, so each time you open the app, it will
link to the mdbs.

In your form Timer event, use the CurrrentDb.Execute method to preform the
Append.

Now, with Access, there is no Server side code. Access is not true Client
Server, it is actually File Server. That is, all code runs on the client.
The mdbs containing data on the server are opened and manipulated from the
Client.

 

[J Streger]

Thank you very much. I think I Understand what I need to do. Now I just
need to figure out how to write an append query to determine which records do
not exist in
in the internal DB compared to the External one, when 4 columns are needed
to establish a primary key. But I'll ask that in the correct forum.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003