Script to Export Data to SQL Server

[Bonediggler1]

Hi--

I need to be able to export data from Access to existing SQL Server tables. I do not have the use of DTS/SSIS as this is a SQL Server back-end only.

Any scripts along these lines would be helpful.

Thank you!!

 

[dhookom]

Have you considered linking to the SQL Server tables and using append queries?

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

Your best bet is probably running this on the SQL Server side, probably using OpenRowset.

this is a SQL Server back-end only

All SQL Servers are "back end", but you could download and install Microsoft SQL Management Studio Express, or just run the SQL statement with the command line interface (SqlCmd).

 

[Bonediggler1]

Thank you both.

It doesn't look like an append query can be used to update a linked SQL Server table. However, OpenRowset does look promising. Perhaps incorporating this somehow into a stored procedure could help streamline getting the data from Access to SS. We'll see...

As for the back-end comment, I was implying in a round-about way that I don't have Management Studio and thus cannot automate data uploads via DTS etc. The express version is also missing this. The interface we are using is Toad for Data Analysts, which has a lot of neat querying and development tools but also lacks a DTS/SSIS equivalent.

Anyway, this is my first development project in SQL Server. The learning curve is pretty steep...!

Thanks again.

 

[dhookom]

It doesn't look like an append query can be used to update a linked SQL Server table.

Why not? I have successfully done this. There are some hurdles and limitations but it is possible in most cases.

Duane
Hook'D on Access
MS Access MVP