Access Database and SQL Server database


I have an access database server and a sql server DB. The  Structure of both DB is same .

The data is updated or inserted in access DB.

I want to update SQL Server DB on a any event say button click. All updated data or new data in access DB should be reflected on SQL Server DB .

Can anyone guide on the approach to be attempted.



Mark this as an answer if you find my it helpful.

7/31/2007 8:25:00 PM 4679 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 42

ONe approach would be to use the System.Data.OleDb library and store connection strings for each database in your web.config file.  Each time you need to insert or update:

1. Write a method that does the insert / update that takes a connection string as an input parameter and uses the OleDB library since it can work with both types of database without coding changes.

2. Each time you need to insert / update, call your function and pass in the Access connection string.

3. Then call the same insert / update function this time passing in the SQL Server connection string.

Is there a reason why you need to different database to contain the exact same data?  It seems wasteful in terms of both storage capacity and performance.

7/31/2007 8:43:44 PM


Thanks for your response. Actually Access DB is the main huge DB  where our DB maintainance team put their data.

For our dev website we are using SQL Server and converting all application on SQL Server.Its a kind of subset to Access DB. They are not interested in entering data manually twice or use any web form feilds to populate Sql server DB.So we are trying to find a decent solution to this.



Mark this as an answer if you find my it helpful.

8/1/2007 1:35:32 PM

Similar Artilces:

Converting Access database to the Sql Server database
how can I convert or transfer my Access database to SQl Server database ? Thanks in advance.HighOnCodingWanna get high! Try "the Upsizing wizard". Look in Access help...

New errors access MS SQL Server Database on (CREATE DATABASE and Batch SQL Statement)
------=_NextPart_000_002B_01C2F2CB.0C201FF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running ActiveState perl 5.8.0 on a WIN2K box with MS SQL Server = 2000. Recently I updated DBD-ODBC to v1.05 and DBI to v1.35 Now I get the following errors executing some sql commands CREATE SQLSTMT:[CREATE DATABASE POMS_SER ON (NAME =3D 'POMS_SER_DAT', = FILENAME =3D 'd:\Database\POMS\POMS_SER_Data.MDF', SIZE =3D 10MB) LOG ON = ( NAME =3D 'POMS_SER_LOG', FILENAME =3D = 'd:\Database\POMS\POMS_S...

COnverting SQL SERVER 2000 Database into Access database
Hi, How can I import SQL Database 2000 into Access database. I have created a SQl script file of my database. thanks, bye,HighOnCodingWanna get high! the SQL Script isn't that much use in this case - use DTS to export the DB from SQL to AccessRTFM - straight talk for web developers. Unmoderated, uncensored, occasionally unreadableJason Brown - MVP, IIS whats the DTS can u explain me please ??HighOnCodingWanna get high! Data Transformation Services - the primary way of miporting and exporting data to and from SQL Server - I assume you've installed the SQL Server Client...

Error 7405 accessing SQL Server database by database link
I'm persistently getting the above error when executing a stored procedure that uses a dabase link to another SQL Server database. I can run the procedure in MS SQL Server management studio fine. I'm setting ANSI_NULLS and ANSI_WARNINGS ON before creating the procedure. Procedure code: SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SSP_WAR_RPT_MANUAL_RECLAIM] @adt_sbi_from datetime, @adt_sbi_to datetime, @as_vendor varchar(15) output, @as_campaigns Char(1) AS BEGIN SET ANSI_NULLS O...

Is there a way to downsize a SQL Server database to an Access Jet database?
I won't bore you with the reasons why at this stage, but I have a need to downsize a SQL Server database to a Microsoft Access Jet database for use as a back end to an existing Access 2003 application. Is there any way to do this?  My SQL Server database sits on a webserver - a shared SQL Server 2005 facility provided by my Windows hosting ISP. I only have SQL Server Express and SQL Server Management Studio Express on my own PC (plus Visual Web Developer 2008 Express). David Hi David, As far as I know, there is no such tool to migrate from SQL Server to Access.I think we c...

accessing sql server database from remote server
 HiHow can I access SQL server database on remoter server. I means that I have my is on server A and database is on server B?   you have to set  connectionstring in app that includes "datasource=<ip of other server B>".  also, server a needs to have permisions on database on server b.and of course sql server has to allow remote conections both tcp\ip and named pipes Hi,abidshahzad To access SQL server database on remote server u must know these things server: Ip address of the remote server database: database on the remote server u w...

How to access
HI, My ASP.NET pages are on webserver and Access database is in File server. I have mapped Fileserver path on my webserver. But still it is not able to find Accessdatabase in connection string! I have also created ODBC conenction in webserver pointing to Fileserver but still error.!!    Please help me out ASAP.    Give permission to ASPNET account to access/read/modify that file share. This will fix your error, Raghu(MCSD.NET, MCAD.NET, MCDBA)[Don't forget to click on Mark as answer on the post that helped you ] Hi, I gave rights still I get followin...

accessing ACCESS database
Hi, I am trying to write a script that copies an information from ACCESS but it gives me the following error: Can't call method "Sql" on an undefined value at line 4. Here's the script: use Win32::ODBC; $DSN="db1"; $db = new Win32::ODBC("db1"); $db->Sql("SELECT owner_first_name, owner_last_name FROM Owners WHERE ID=11"); if ($db->Error()) { print "Error: $db->Error"; } statement:\n$DBI:errstr\n"; ($ownerfname,$ownerlname) = $db->FetchRow(); print "Database results: first ...

how to access other database in this database ?
Hi,Everyone,I used to use oracle 8i,but now for some reasons,I must change to ASE11.5,at the beginning,I don't know how to access tables among databases,in oracle, if I grant "select" to some user,this user will access other user's table in username.tablename format,I want to know how to implement this functions,please help me! thanks! Very similar in sybase ASE. grant SELECT on <user>.<tbl> to <newuser> for new user: select * from <user>.<tbl> Patrick wrote: > Hi,Everyone,I used to use oracle 8i,but now for some rea...

updating Sql database from linked Access database
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database. What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (...

How can I update a SQL database with an Access database
I need to update an SQL database with an access database that is built on queries.  I need to do this nightly.   Can someone explain how to write the query for this and then I could add it to my nightly maintenance.   Thank you DeeDarlene D. you can reference your Sql Database through Access either with linked tables or ADO.  As long as you have update rights on the Sql Tables, you can use your Access queries to update the Sql tables.Good Luck and happy programming!Let's build community! If my post helped you, mark it as the Answ...

Conversion of a DNN Access database to SQL database
We have an Access version of the Dot Net Nuke database. We are upgrading to a server with SQL. We'd like to transfer the content from the Access database to the SQL database. Is this possible? Is there a utility available for this conversion. What should we be aware of as we try this? Take a look at Rob Visit - index of DNN threads - hand picked quality threads. TextLayout Module - create multiple column layouts easily Thank for your excellent process! I was able to successfully move the data. ...

migrate access database to sql anywhere database
I am having trouble migrating a Microsoft Access database to SQL Anywhere database. Has anyone done this? I would like to use this database in sql anywhere to utilize the native drivers. Thankyou in advance david You can use the pipeline to migrate all tables. If you have a tool like Powerdesigner, you can reverse engineer the database structure. BTW, SQLAnywhere uses ODBC. There are no native drivers. Terry Dykstra (TeamPS) David Bowles <> wrote in article <>... > I am having trouble migrating a Microsoft ...

Access database under SQL server
Is it in any way possible to bring MS Access databases working under SQL Server ? In order to use a truly client/server as SQL Server in a website application. You can use the Access Upsizing wizard, or DTS to move an Access database to SQL Server. Is that what you are trying to do?Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog In my case the solution I to turn an Access database into an project within MS Access. Under Server Explorer within Visual Studio you can manage to make a data connection and bring it under the MS SQL Server. It seems to work so fa...

Web resources about - Access Database and SQL Server database -

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

MapR And Big Data In The World's Largest Biometric Database Project
India?s Aadhaar project is one of the world?s most ambitious big data projects, aiming to collect, store, and utilize biometric information from ...

Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database
Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database

Presidential Candidates Condemn Donald Trump's 'Database' Comment
... and Democratic presidential candidates have condemned Donald Trump for his recent statements suggesting the United States should start a database ...

Resources last updated: 11/27/2015 3:11:39 PM