Need solution to synchronize Ms Access and SQL Server (Express Edition) database.
-
Hi guys, Any 1 got ideas about how to synchronize Ms Access and SQL Server (Express Edition) database. the scenario is I Create a database and a table containing a single text column for both MsAccess as well as SQL Express (DB and table name to be similar). By performing insert / update / delete on SQL Express (Source) and I must ensure that the data is updated accordingly on the database in Ms Access (Destination) as well. Vise versa. So did I need to create 2 connectionstrings , which 1 is OLEDB and another is SQL? As i know we can use same sql statement,just need to be cautious with reserve word for database command such as OLEDBCONNECTION and SQLCONNECTION.
-
Hi guys, Any 1 got ideas about how to synchronize Ms Access and SQL Server (Express Edition) database. the scenario is I Create a database and a table containing a single text column for both MsAccess as well as SQL Express (DB and table name to be similar). By performing insert / update / delete on SQL Express (Source) and I must ensure that the data is updated accordingly on the database in Ms Access (Destination) as well. Vise versa. So did I need to create 2 connectionstrings , which 1 is OLEDB and another is SQL? As i know we can use same sql statement,just need to be cautious with reserve word for database command such as OLEDBCONNECTION and SQLCONNECTION.
Ok..I successfully synchronize access and sql server. Follow this step: 1.OPen your access database. 2. Go to file>get external data>link tables 3. At Files Of Type, 'choose ODBC Databases()' 4. Click new and choose 'SQL Server'. Click advanced and enter this 'DRIVER={SQL Server} DATABASE=yoursqldatabasename'. then click ok and next. 5. Enter your own datasource name ok then finish. 6. Then create datasource window will popup. choose server that locate your sql server database.Click Next 7. You can choose windows authentication or insert sql server authentication login id 8. Change default database to targeted database. Next and finish. 9. OK until popup link tables window. Choose which table you want to synchronize. 10. try update,insert and delete. Make sure both table in access and sql server update accordingly. :cool: