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

Dee


Darlene D. Murphy
deemurphy_us@yahoo.com
0
deemurphy
2/14/2008 7:13:20 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
900 Views

Similar Articles

[PageSpeed] 29

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 Answer and I will do the same
when you help me...this will help everybody!
0
dtrent
2/14/2008 10:42:34 PM

How about DTS (if SQL 2000) or  SSIS package (for SQL 2005)  the access db to a working SQL dba db and do SQL/SP ...

You can schedule the DTS/set up alerts/notifications ...


0
raghu1
2/15/2008 2:23:28 AM

 The database for access is very large and was made in queres.  When I try to use DTS is states that it can not take all the the tables at one time.

 

Dee 


Darlene D. Murphy
deemurphy_us@yahoo.com
0
deemurphy
2/16/2008 12:46:44 PM

How big is the access MDB ?  How many tables/views ?  Looks like it is time to migrate to a bigger system. If this is a one-off, you might want to have more than 1 DTS package.

0
raghu1
2/16/2008 4:43:16 PM

 The access database is 337,992 KB, since December, but it has grown larger and I do not have the size here at home with me.  There are 5 tables with over 1000 quereys.

 

Hopes this helps.

Dee 


Darlene D. Murphy
deemurphy_us@yahoo.com
0
deemurphy
2/16/2008 6:09:00 PM

 This is say  400 MB round up, if my math is correct.Even if this has doubled let us say  it is now 1 GB. Bringing 5 tables should not be a big problem at all. It is the 1000+ queries. You probably have to create views for this. I think there is an option for this both ways:

Upgrade option in Access

DTS into SQL SERVER.

Which version of SQL SERVER you are planning to upgrade to : SQL 2000 or SQL 2005 ? 

0
raghu1
2/17/2008 1:46:31 PM

 I will be upgrading to sql 2005.  Our web content specialist use access to create our store, They do it via queries, so this will have to be upgraded nightly with

an update option.

 

Thanks

Dee 


Darlene D. Murphy
deemurphy_us@yahoo.com
0
deemurphy
2/17/2008 1:50:56 PM

You need to build a SSIS package. This is way improved over DTS, you can schedule it: updating the SSIS is much easier than a DTS. I would suggest start with the BOL:  there are good working examples.

0
raghu1
2/17/2008 2:13:04 PM

 Which Book online should I look in?

 

Thank you

Dee 


Darlene D. Murphy
deemurphy_us@yahoo.com
0
deemurphy
2/17/2008 2:41:45 PM
The one that comes with SQL. Any questions ask away...Smile
0
raghu1
2/17/2008 9:45:36 PM
Reply:

Similar Artilces:

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 (...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

Can you convert access database data over to an SQL Database?
I would like to start using SQL, how would I convert my data in MS Access over to an SQL Server Database? Q.Xykal:how would I convert my data in MS Access over to an SQL Server Database? In Access, there is an upsizing/conversion tool you can run to create an equivalent SQL Server DB.Atlernatively you can also use SQL Server to import all the tables/data from Access.  This is very good news. Thank you....

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

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...

Access Database and SQL Server database
Hi, 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. Thanks   Mark this as an answer if you find my it helpful.Thanks,Anubhuti 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 ...

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...

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 <davidb@goodnet.com> wrote in article <34738A77.2C758640@goodnet.com>... > I am having trouble migrating a Microsoft ...

DataSet rows being deleted, but after the update , the sql database is not updated. The delete rows still in the database.
 Stepping thru the code with the debugger shows the dataset rows being deleted.   After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ).    The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work.   The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.     cDependChildTA.Fill(cDependChildDs._ClientDependentC...

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 http://www.freelance.supertoddler.com/Default.aspx?tabid=37 Rob Visit www.dnnnotes.com - 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. ...

How to create SQL Mobile 2005 database from a MS SQL 2005 database?
Hi guys,I'm trying to find solutions to help me in creating a SQL Mobile 2005 database from a SQL 2005 database, with the same table structure and data.May I know how can I achieve this?Thanks.Regards,Jenson Hi Jenson, Visual Studio 2005 or SQL Server 2005 is able to open SQL Mobile 2005 database. However you need either SQL or .NET code to convert SQL Mobile 2005 database this way. Here is an example from MSDN forums which is to convert to xls instead: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78640&SiteID=1Zhao Ji MaSincerely,Microsoft Online Community Suppo...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Transfer tables and other contents from access database to sql database
Hi All, can anyone please help me to transfer tables and other contents from access database to sql database? I have access database file, can I also directly convert it into a sql database file? You can upsize your Access database to an SQL Server database (In Access, Tools>>Database Utilities>>Upsize Wizard.... follow the screen. Or you can use SQL Server's Import and Export wizard to import your access database to SQL Server.   You cannot convert your Access database file to SQL Server data file directly.Limno  Read this http://support.microsoft.com/kb/23...

Can connect to Database, but how do I insert values into SQL Database?
I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.'Create(connection)Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)'open connectionconn.Open()However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.Sorry, I'm really new with VWD. You need to read about ADO.NET before you do anything. Google it.ASP.NET Hosting (HostingFest)...

Web resources about - How can I update a SQL database with an Access database - asp.net.sql-datasource

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 ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

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 ...

IDG Contributor Network: Avoiding ginormous transaction logs with SharePoint databases
... why do we see these humongous transaction logs so often? I am going to blame it on a “misunderstanding” of how Microsoft describes a full database ...

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

Change your passwords: NexusMods suffers database breach
... security breach by posts on reddit. Cybersecurity firm REN-ISAC had apparently contacted several university IT departments about a database ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

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

Resources last updated: 12/13/2015 1:14:56 PM