Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nattyb
Starting Member
3 Posts |
 Posted - 2005-05-06 : 12:27:36
|
| Hi all,havent posted on here before so here goes..i have an access DB. at a certain point via a web interface, i need to move 'certain data' (certain fields) - not whole tables but maybe most of the data in some of them - into a new sql server db.no 'transformation' as such needed, just field blah in table blah1 into the same field in a new table.i have googled a bit for an answer to this, but am coming up either blank, or simply confused..my sql skills are a bit lacking.the question is, what is the best way to do this.i have seen DTS mentioned and this seems a posibility, but as this needs to be triggered from the access db, maybe not...i also saw someone mention 'attaching' the sql tables to the access db, and then being able to run querys to do the moves.as i dont want to be moving whole tables, i figured this may be the way to go, but then i realise that sql svr is not on the same box as all the access stuff. im not sure that this is in fact a problem..so.. any suggestionsany help would be much appreciated.tianat |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-05-06 : 12:37:38
|
| Did you install the SQL Server client side tools?Where is SQL Server set up? On a server. What version is it?I would use DTS for this...Is this a one time conversion, or a refresh process?Brett8-) |
 |
|
|
nattyb
Starting Member
3 Posts |
Posted - 2005-05-06 : 16:19:41
|
| hi brett,thanks for the reply. ok i realise that was a bit of a sketchy description.. :-) but it was a bit more of a 'test the water' type of ask..the sql server is 2000 or above afaik. which means dts is an option .. yes ?the the access database is always changing, smae box, different db.although the data to be grabbed is always the same.ill have a bit of a read on msdn i think :-) |
|
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2005-05-06 : 18:59:13
|
| There are many permutations and several general approaches. DTS, however is what I would use.If the data you are copying over is never changing, then using DTS for a one time copy would be fairly easy. You could use the DTS wizard to specify the Access db as your source and SQL as your destination, etc. etc.If the data in Access does change regularly, then I would schedule the DTS job (which you could save a the end of completing the Wizard) to run on the desired schedule.If I were you, I would do some testing with the DTS Wizard in Enterprise Manager. You could get to it by right clicking on 'tables', choosing 'all tasks' and then 'import data'. Also, reference the Books Online (the SQL Server help - very useful at times).But, in summary, you may be best to use DTS. You could also create the links in Access to SQL, as you suggested, that may be more complicated to maintain. But, the question becomes, are you more comforatble with Access or SQL (or you want to learn more about SQL Server / DTS ? ):) |
|
|
|
nattyb
Starting Member
3 Posts |
Posted - 2005-05-09 : 12:57:49
|
| hi again peeps,ok i have started this DTS business to get our data across.1 small problem, is that i would like to pass an argument to the DTS to specify what to copy over.im guessing that this cannot be done. so am i left with having to grab loads more data than i need and then fiddling it when it arrives ?i will try to explain a little more..i have afew new tables on the sql end. 1 of which is the main table which WANTS TO have a single row appended when this is run. but sadly its not ever going to be the max id row (of the source table) or anything useful that. this row holds they key to the second part of the dts (or second dts run via a SP)maybe i can trim the amount of records coming in that i dont need, by selecting on the stopdate (which is a field that will be set to the time that this whole thing will be run), or something similar. and then use the SP to drop all the extra records i dont need.there is of course another problem with all this.. which is that the sql DB is never moving , the main access db is never moving, but the smaller access db is in a different place every time.this is not a perfect set up for dts it seems ?im thinking i may be scuppered, but i am hoping that someone can point me in the direction of a workable solution..tianat |
|
|
|
|
|
|
|
|
Access to SQL Server