Transfer data from SQL Server to MS Access using query

Question

Post reply

Transfer data from SQL Server to MS Access using query

aykrishna

SSC Enthusiast

Points: 115
More actions
February 13, 2004 at 10:20 pm

#65535

Hello Guys,
Is it possible to transfer data from SQL server to MS Access usign a query. For manual transfer I use dts wizard. But how to do the same task programmatically.
Will be very thankful to you.
--akki.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

Yes

I have done it in otherway round

i.e SQL -> ACCESS (From VB/ADO)

'Make the conaccess,conSql Connection strings

rstaccess.Open "EventService", conaccess, adOpenForwardOnly, adLockReadOnly

rstSQL.Open "EventService", conSql, adOpenDynamic, adLockOptimistic

Do While rstaccess.EOF = False

rstSQL.AddNew

For I = 1 To 6

rstSQL.Fields(I) = rstaccess.Fields(I)

rstaccess.MoveNext

Loop

rstaccess.Close

rstSQL.Close

For each field command is not posible if there are AutoNumbers in the field.

My Blog: http://dineshasanka.spaces.live.com/

aykrishna SSC Enthusiast Points: 115 More actions · Answer 2

Thanks. But thought that there would be a better way !!

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 3

Hi,

Yes, there is another way;

1)Create a system DSN to your SQL server in control panel ODBC

2) Add your SQL server table or view as a linked table(right click,Link tables,file type = ODBC databases and select your DSN)

3) Create a query in Access like so:

SELECT * INTO MyAccessTable

FROM MySQLView

Run the query.There is no need to create the table in Access first.

HTH

Chris

aykrishna SSC Enthusiast Points: 115 More actions · Answer 4

aykrishna

SSC Enthusiast

Points: 115

February 15, 2004 at 9:20 pm

#494126

Thanks , I will give it a try.

dean gross SSCrazy Points: 2632 More actions · Answer 5

You can connect your Access database to the SQL Server, create a query based on the SQL tables or views, then convert the query to a Make Table query, run it to create and populate a new Access table, then create a Delete query to empty out the new table, then create and append query to repopulate the table and then create a macro to run the delete and append queries in sequence. Make a form with button that calls the macro and you will have a quick and dirty way to get a batch of data from SQL server into an Access mdb.