1

I am attempting to keep a table in Sql server updated from an access table. Any time a change is made in the access table I would like that change reflected in the sql server table. The two tables can be identical. I have created an ODBC connection from access to sql server and can export the table to sql server; I just don’t know what must be done to keep that table updated. Any suggestions are appreciated.

Should this be implemented from within Access or within sql server?

HK1
  • 11,941
  • 14
  • 64
  • 99
BilliD
  • 577
  • 2
  • 7
  • 17
  • 4
    Why are you using both Access and SQL Server? If you have the SQL Server table, then dispense with Access and do everything on the server. – Gordon Linoff May 08 '12 at 19:49
  • Is your Access table being populated by some 3rd party application that you have no control over? – JeffO May 08 '12 at 20:13
  • 1
    Pretty much every answer here is to use Linked tables. That's what I'm going to recommend too, but I'd like to specifically recommend that you try ODBC linked tables. They are the easiest way to hook Access up to SQL Server. Stay away from synchronization unless you absolutely need it! – HK1 May 09 '12 at 02:14

3 Answers3

3

Can you just add the SQL Server table to the Access database as a linked table? (Useful article on how to add linked tables)? That way users (let's hope there's not many!) of the Access database are in effect editing the SQL Server table directly. If this isn't desirable then how about creating another table in the SQL Server database, and adding this to the Access database as a linked table. Then, add a trigger so that when an insert/update/delete is made to this table the same operation is done on your main table.

Martin Wilson
  • 3,386
  • 1
  • 24
  • 29
  • I looked at your recomended link and that is the process i need but i need it done in reverse. I need a table from access to be placed into sql server, this is already done through ODBC, and stay updated. Then i will create triggers on that table that will update other parts of the sql server database. – BilliD May 09 '12 at 13:52
  • The reason for the two database is because teh sql server runs a work order application and the access runs the web sites. We currently keep access up to date and i would like to avoid entering the same data changes in two applications – BilliD May 09 '12 at 13:59
  • If you have already got a link to an Access database in your SQL Server then presumably this is not working for you for some reason. Could you explain why not? (I am guessing you can't create triggers on a table linked like this). – Martin Wilson May 09 '12 at 14:35
  • I can run queries against the table once it is in sql server but to get the table into sql server i ran an export using the ODBC connection. I dont believe that the table will stay up to date without another export. – BilliD May 09 '12 at 17:33
  • No, it won't. An export just copies the data. I am suggesting you set up a linked table, which is different from an export. – Martin Wilson May 09 '12 at 18:18
  • Thanks Martin, I am creating a linked table in access then creating triggers in access to update the linked table and then more triggers on sql server side to use the linked data. I appriciate all the help – BilliD May 09 '12 at 19:59
1

Access has no "event" that occurs when a row is updated/inserted/deleted that I know of. as JeffO points out data macros that could do what you want.

You could also periodically synch them. There are several techniques to periodically do the synch task (SQL Server Agent, Windows Service, Windows Scheduler, a timer in an application etc.), but still have to deal with all the problems that exist with synchronization if both tables can be modified, the worst being data conflict resolution. There is no easy solution for that.

Perhaps if you explained the problem you have that you are solving with synching data in SQL server and Access someone might be able to point you in the direction of a solution that doesn't have these problems.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Access 2010 has Data Macros, but the record changes would have to occur in Access as the front end. – JeffO May 08 '12 at 20:11
  • @JeffO wow didn't know about that. Out of curiosity do you know if in a split database where does the Data Macro run (front end or back end) – Conrad Frix May 08 '12 at 20:15
1

I think setting up a Linked Server in SQL Server could be easier to implement than an automatic export of data from Access.

According to the MSDN page,

Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

Server-on-SQL-2005-Server/

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137