November 23, 2008 at 2:57 pm
From sql server I have a linked server connection setup to an access database in my local machine. Is there a way from sql server to monitor changes to an access table?
November 24, 2008 at 10:15 am
I'd be more concerned about why you would continue to maintain the data in Access instead of migrating it up to SQL Server instead, and just letting Access be the "front end". Microsoft has a great migration tool that's far better than the "upsizing wizard", and it's available for free download from their website. It's called SSMA, or SQL Server Migration Assistant.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
November 24, 2008 at 10:36 am
I'd tend to agree with smunson. Once you convert the data to SQL Server, you could use something real-time like triggers to allow you to "react" to data changes as they occur. In order to do that, however, the tables need to be native to SQL Server.
If you can't avoid it though, you may just need to set up a scheduled task to go look at the relevant tables in Access. You would then need some kind of createtime or updatetime column (or some such similar idea) to tell you which rows are "new".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 24, 2008 at 10:49 am
You could import the Access db periodically and look for changes.
Either in Access or SS, I'd be wary of "real time" alerts. You often find that you didn't mean to get 12 alerts when someone changes a field back and forth because of typos. Better to periodically check for changes and then batch them up into one notification.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 24, 2008 at 1:43 pm
Yeah, I'm all for upsizing into SQL but the access db isn't owned by me so I have limited access to it...which means read only access....else I would have converted it long ago.
Thanks for all the suggestions. Maybe some kind of hybrid solution will work else I may end up writing a windows service to look at the data periodically for changes.....
Thanks again.
November 24, 2008 at 1:54 pm
Oh, ok... sounds like maybe just getting a scheduled SQL Agent job to run and execute an SSIS or DTS package to copy the contents up to SQL Server, where all the change checking can then be done. How soon do you need to know of any given changes? That question's answer would then determine the frequency the job runs with.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
November 24, 2008 at 1:58 pm
Hmmm... almost missed it... read only access to a database hosted on YOUR local machine? Has anyone considered the backup requirements for the data in this database? Why would anyone willingly host it locally for anything but the shortest possible timeframe without a formal backup process in place?
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply