| Author |
Topic |
|
oob
Starting Member
15 Posts |
 Posted - 2002-05-30 : 11:38:11
|
| I am looking for a way to copy tables from Sql Server to Access without using DTS. I know in access I can use something like "Insert into TestTable(ID, Name) IN [Test.mdb]" _ "Select ID, Name from TestTable "to copy data from one access database to another access database and Im just wondering if there is something similar to copy data from Sql server to access. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-30 : 11:45:14
|
| Sure. Create linked tables in Access. Use the linked table wizard, or just create an ODBC data source (DSN) that links to SQL Server. Then select the tables you want to link. Once that's done, you can query them as if they were regular Access tables, including an append query. |
 |
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-30 : 11:49:32
|
| Thanks for the info. this will work, however the application we are writing is dynamic in the sense that the access db will be changing quite alot. Linking to SQL Server from that access table on the fly would not really be an option for us in this case.Any other Ideas? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-30 : 11:56:15
|
| Make sure you LINK to the SQL Server tables, NOT import them. Once the linked table is established it accesses the SQL Server directly, so the new SQL Server data is always available. Once the linked tables are established you can change the rest of the Access database to your heart's content.Edited by - robvolk on 05/30/2002 11:56:47 |
|
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-30 : 12:01:45
|
| Is using linked tables the only way this can be done? or is there any other way? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-30 : 12:14:37
|
| It is the best way.What exactly are your reservations? I can tell you from personal experience that this is a no-brainer, it's very easy to do and works extremely well. The only problem would arise if you need to distribute this Access database in such a way that doesn't allow the user to directly connect to the SQL Server. If that's the case, then you need to reconsider the design, or use DTS, or not bother copying data from SQL Server at all. |
|
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-30 : 12:20:23
|
| Thanks thats exactly the problem .. will probably have to look at re-design.thanks again for your help. |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-05-30 : 14:09:32
|
| You can programatically link your SQL tables in your Access application. So the fact that you Access application is dynamic doesn't matter.*************************Someone done told you wrong! |
|
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-31 : 07:37:46
|
| Do you know of where I can get an example to do this programatically? |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-05-31 : 08:13:21
|
| I guess the first question I have is, do you have access to the SQL Server wherever this Access application will be distributed? If not, then all you can do is import the data from SQL Server one time, but the problem with this is that if you needed to get data from SQL Server again, you would have to re-distribute the application. However, if you do have access to the SQL Server, then I can give you an example of how to programatically link to them.*************************Someone done told you wrong! |
|
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-31 : 09:08:07
|
| We do have access to SQL Server so the example would be great thanks. |
|
|
|
oob
Starting Member
15 Posts |
Posted - 2002-05-31 : 09:58:35
|
Found documentation on how to do this programatically. Thanks for pointing me in the right direction. |
|
|
|
|
Copying SQL Server to Access