SQL Server linked tables - importing from

[natty]

hi there

we (unfortunately) use access as our DBs for our little surveys. when we think that a survey will be particularly busy, we run a little script that dumps all the tables on our SQL server box, and makes linked tables in the Access DB.
this all works fine, and does give us a certain amount of performance increase

however, when it comes to pulling all the data back into access, we are at the mo, doing this all by hand

I am wondering if it is possible to do this with VBA, or i would be better serverd by writing a little asp script and doing it like that

what needs to be done - the steps we take when doing it manually are these

1. delete all the linked tables
2. import all the data from the SQL databse back into access (there is a DSN to the SQL server instance)
3. stopping the DB on the sql box

actuallly , looking at it like this, seems pretty simple, but VBA, or asp ?
any thoughts?

i was thining i could just select * into newtable for each of the ones i need
but can you set up a DB connection to another DB easily in VBA?

any help much appreciated

thanks

nat

 

[llkhoutx]

Don not import the SQL Server data into Access. Link the SQL Server tables to the Access database.

 

[natty]

hi there

thanks for the reply

but

i may not have made this clear
but the tables are ALREADY linked, and i want to unlink them, and transfer the data across..

does what i mentioned seem like its the way to do it?

thanks

nat

 

[georgedwilkinson]

Your request is unclear to me or I would have responded when I first saw it.

Have you looked at the ADODB object? You can define your connection and manipulate data across that connection, all in code. There is lots of documentation on using it for the type of thing it sounds like you're doing.

Of course, the easiest thing to do is leave all the linked tables in place and use SQL to copy the data across.

 

[Kenln]

Did you get your answers.

I think I found something in a book. I am playing with (modifying) it if you still need it?

If so are you:
Trying to move linked SQL tables to local (within the db) tables keeping the same names???

Let me know,