We help IT Professionals succeed at work.

Insert row into MS Access database table form SQL server table

crich
crich asked
on
Medium Priority
876 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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Yes.
Top Expert 2004

Commented:
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=;')



Author

Commented:
I have already tried these pieces of code and thet are not suitable for various reasons. Any other ideas?

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

Commented:
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.  
Top Expert 2004

Commented:
"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.
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?

Commented:
How about points all round ?

We've given 3 different mechanisms from three different people !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.