Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alienation
Starting Member
4 Posts |
 Posted - 2008-01-24 : 10:47:14
|
| Hey guys. I need a little help once again, here's the problem:We are using an attendance system software that adds records in an Access 97 table whenever someone logs in or out of the room (Yes, it's old and I hate it, but it came with the hardware).So we plan to create a program that lets admins check those login/logout data in an SQL Server 2005 database using a Visual Basic as the frontend.Our only problem is, how do we transfer data from the Access 97 tables to our SQL tables *everytime* it gets updated? We don't need every detail the Access97 table has (it has IDNo, CardNo, Time, Location, plus other useless stuff). We just need to extract the IDNo(int) and Location(varchar) field(we'll just use getdate() for the Time since the update is done in realtime anyway).So in a way, we are trying to find out a way to technically sync both tables, just like Database Mirroring style.Any suggestions? |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-01-25 : 01:02:52
|
| I am not sure about this one. But for some reason I thought you could use an Access Macro as a trigger to fire an insert.Chris Shawwww.SQLonCall.com |
 |
|
|
alienation
Starting Member
4 Posts |
Posted - 2008-01-25 : 10:45:54
|
| Hmm, I still can't quite get how to do this. Just to clarify:I want to read records from an Access Table(IDNo, CardNo, Time, Location, other details)and insert only 2 data to a SQL Server 2005 table(IDNo, Location) everytime the access table adds a record. Note that this process must be done in realtime.How do I create an event trigger from access to send the data to a SQL table?ORCan I just link the Access table to our SQL Database and just extract data from there?Any method would be fine. Hope to get a solution soon. |
|
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-01-25 : 20:16:40
|
| I am not sure that Access 97 supports this, in 2007 it appears that it will support a trigger. If it does one option would be to create the trigger in the Access database on the source table. The trigger would insert the record into a linked table in your access db that is linked to your SQL Server. I am not sure if this is what you are looking for, but it looks like it might work. Search for Create Trigger in your Access help file.Chris Shawwww.SQLonCall.com |
|
|
|
|
|
|
|
|
Adding/Appending Records Frequently in SQL