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 |
|
abarsami
Yak Posting Veteran
68 Posts |
 Posted - 2003-06-24 : 15:35:12
|
| I use ado & I am trying to copy data from a sql table to an access table.I open the sql connection (con)So I have the following query, but it doesn't work:con.Execute("insert into Companyx('Provider=Microsoft.Jet.OLEDB.4.0','Data Source=C:\Inetpub\wwwroot\HHD_local\Application\Reports\Txt\access1.mdb') select company_id from Company where company_id=900")Anyone have any clues (is the syntax wrong?) or any other simpler way to copy the data? It has to be a sript. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-24 : 19:40:33
|
| Why are you even doing it that way? What's wrong with DTS? There's no way the code you've written will work. If you're trying to copy data from SQL Server to MS Access, you should, in order of preference:1. Use DTS2. Create linked tables in Access that link to SQL Server3. Create a linked server in SQL Server that links to AccessNo need for ADO anywhere, and ADO is probably the worst method to use for this, if it works at all. |
 |
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2003-06-25 : 17:18:20
|
| I need to be able to do it dynamically ... so if they hit a submit button on website -- it does it. I would use DTS, but this has to happen dynamically -- I can use DTS to do it once, but I don't want to do it every time myself. Also the DTS procedure would need change depending on the client. We have clients who want there own mdb access file on there own computer with specific records from our SQL database. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-25 : 17:28:23
|
| DTS can do everything you want here, including dynamic selection of which tables are transferred to which servers from which .mdb's. Take Rob's advice, it's worth a second look.Jonathan{0} |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2003-06-25 : 18:34:46
|
| I am playing around with dts and have some questions.I got it to work this way: I created an dynamic properties task with 2 tasks and am using data file method with 2 text files.1 has the mdb name & 1 has the source table query that selects certain records from the sql source table.My questions are:1) How do I execute this dts procedure from a web page. Is it the same as stored procedures?So it would be con.execute("do i just put the name of the dts procedure here?")2) The data file method works, but it would be better if I can pass the variables when I call the dts procedure, like you can do with stored procedures.So, I would like to be able to do:con.execute ("dts procedure", "c:\access1.mdb", "select company_id from company where client_id = 100")Do I change the data file option to Global variable, enivronmental variable, or constant for this?Thanks. |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2003-06-25 : 23:15:32
|
| I tried this, but it didn't work... I followed microsoft's documentation.Set oPkg= Server.CreateObject("DTS.Package")oPkg.LoadFromSQLServer "servername", "user", "pass",DTSSQLStgFlag_Default , , , , "SQLtoAccess"oPkg.Execute() |
|
|
|
|
|
|
|
|
Query to copy data from sql table to access table