By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Syncing Access table with

P: n/a
I have an access Database. It has several tables, but one (possibly
two) in particular, need to be kept in Sync with an authoritative
source of information on an SQL Server.

The table in question is the Customer Details. i.e. customer ID NUmber,
Name, address, phone number etc.

While within the Access application, we manage a bunch of other data,
this really needs to be aligned with the info on the SQL Server. As
such, we won't allow changes to the local customer table. IN fact,
initial setup will be an import from the SQL Server. However, for
ongoing data integrity, we need to "check the SQL Server" for updates,
whenever we start up the access application.

How, the situation will exist where there may not be connectivity to
the SQL Server Database. SO, in effect the Access Detail table, needs
to be a "cache" of the SQL server table. (we are talking about around
3000-5000 entries)

So, the line of thinking, is

1. application starts up
2. attempts connect to SQL DB.
3. If it can, it downloads that data to the access application.
(there are two thoughts - download the entire table irrespective
from sql to access or run through all records and look for changes, and
download. I would err on the side of the first...
If it can't connect, it uses it's most recent copy of the downloaded
table, sitting in the Access Application.

Does this make sense - can anyone think of other ways to approach this,
and better still, codelets that do it...

q

Dec 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Mister Q wrote:
Does this make sense - can anyone think of other ways to approach this,
and better still, codelets that do it...
Research ODBC connections and linked tables. You could have a live link
from your mdb to the MS SQL table that is read only.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Dec 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.