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 |
|
fafful
Starting Member
4 Posts |
 Posted - 2003-02-12 : 10:36:50
|
| I would like to use VB6.0 to move Data in a table from Access to SQL 2000 Table and from SQL2000 Table to Access 2000 by the click of a button. The Structure of Both Tables are similar. I am aware the DTS could and probably 'Openrowset' could as well. But is there any other way using code to move the Data |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-02-12 : 10:57:58
|
| If this is s/t that's done often, add the Access MDB to SQL Server as a linked server, and add your SQL table into Access as a linked table. That way, you won't have to update the table in Access, and you can run an INSERT query on the SQL table that references the Servername.dbname.ownername.tablename Access table. This method is best if the tables have same struct. If they don't, then don't do the Access linked table- just insert into the Access table the same way as in the SQL table, but with reversed queries.Sarah Berger MCSDEdited: Almost forgot... You can code all this in VB using the regular ADO objects, and executing the queries on the connection or command objects.Edited by - simondeutsch on 02/12/2003 10:59:51 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-12 : 11:02:03
|
| You could also use linked tables in Access and just use simple append queries. I use them all the the time, it works well. Not sure for huge amounts of data, but I've appended 100,000 records both ways and it works fine.Then, you can also write a simple front end form in access and use some simple vb or ADO, as Sarah suggested, to control this.Keep it simple, I always say!- Jeff |
|
|
|
|
|
|
|
|
Copy Access data to SQL Table