Solved

Insert row into MS Access database table form SQL server table

Posted on 2004-03-23
13
832 Views
Last Modified: 2007-12-19
Hi there,
I am trying to insert data into a MS access table from an SQL server table. Do i need to add this as a linked server or is there a query i can run? I am coming across many errors when i try to create a linked server and also if try to do a pass through query.

Can anyone demonstrate some code that works corectly for this task?

thanks
richard
0
Comment
Question by:crich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 13

Accepted Solution

by:
danblake earned 25 total points
ID: 10658884
You could create a DTS Package, using the Import/Export wizard -- to export to the Access table.

Ensure Access is installed on the destination machine (to ensure you will be able to update the Access database).
0
 

Assisted Solution

by:Choppertoo
Choppertoo earned 25 total points
ID: 10659035
A DTS package would be able to export SQL rows to an MS Access Table. Or you could work it the other way around and use MS Access to pull the records friom SQL server using OLEDB/ODBC
0
 
LVL 13

Expert Comment

by:danblake
ID: 10659119
Yes.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 34

Expert Comment

by:arbert
ID: 10659304
You could also use a linked table in access or OPENDATASOURCE executed on SQL Server (without a linked server)

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\yourdatabase.mdb";User ID=Admin;Password=;')



0
 

Author Comment

by:crich
ID: 10764474
I have already tried these pieces of code and thet are not suitable for various reasons. Any other ideas?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10764655
I think you need to be more precise on what you are trying to achieve, and why these items of code are not suitable for us to provide you with a good answer that you are looking for.
0
 

Expert Comment

by:Choppertoo
ID: 10765060
Ok questions?

Does the data have to be pushed from the SQL server to MS Access?

How are you identifying the record that is to be appended to MS Access?

If you have to push the data from SQL server to MS Access then there are two methods;

first is a linked server ... a good article on how to do this here;
http://www.databasejournal.com/features/mssql/article.php/3085211

The second way is via a DTS package .... using a sql statement to select the records you wish to append to MS access ... again an article on how to do this is here;
http://www.databasejournal.com/features/mssql/article.php/3089011

My own personal preference however, would be to pull the data from SQL server using MS Access as a client. Incorporating a boolean "sent_to_access" flag on a field in the SQL table would mean that a view could be constructed on SQL server showing records not yet sent to access, it would then be a simple matter of linking the SQL table to MS Access (from MS Access as an ODBC connection) creating an append query in MS Access to insert the records into a MS Access table and then running an update query to set the "sent_to_access" flag to true.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10765083
Can anyone demonstrate some code that works corectly for this task?
We cannot unless we get a list of errors from your particular enviroment, there may be a specific fix that is missing for your enviroment.  It is better that we choose an approapriate technology/solution that will work given any necessary fixs.  
0
 
LVL 34

Expert Comment

by:arbert
ID: 10765527
"I have already tried these pieces of code and thet are not suitable for various reasons."

I agree with the above comments.  We need to know why they are not suitable....I would also so NO REFUND of points as the above answers did assist with your original question.
0
 

Expert Comment

by:Choppertoo
ID: 10765616
Agreed, though it appears that he is trying to create an access database as a linked server and says " I am coming across many errors " what are these errors?
A little bit more detail could help people solve your problem .... so I have to agree - no refund of points as the original question is so vague  and has not been expanded upon after requests from experts. How can anyone help if the problem itself hasn't been identified?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10766049
How about points all round ?

We've given 3 different mechanisms from three different people !
0

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question