Moving Tables between MS SQL Server And Access

Is there a quick way to move an entire table between MS SQL server and MS Access once you're connected to both from Visual Basic.
Our data is located on our server but I need to program a process to transfer the data off the server to MS Access. Is the only way to do this to create two recordsets and copy one row at a time?

--Ming

Your suggestion will certainly work.

However depending on volume, Key and Index properties it may be worth exporting and importing From/To a basic file format like CSV.

Another would be linking to the appropriate tables in an Access Db and use a number of INSERT INTO / "MakeTable" queries to make a local copy.

I am convinced there is some "snazzy" ADO method of achieving this using dis-connected recordsets. Spent about half an hour trying btn 2 Access Dbs, nearly got it to work!

Don't have any spare time at present but I will be returning to this one.

There are a few methods, but I agree using a SQL statement is probably your best bet. It will be the fastest way.

Depending on the amount of data you are transfering, and if DB connection time is a problem, about the only other way is to pass the data from one database as a deliminated file, pick it up and parse it into the other database tables. I have used both of these methods, but I like to use the SQL method if I can.

I've been trying to do the same thing -- copy tables between 2 MDBs first using a "snazzy" ADO method.

My thought was to try saving the recordset as XML from the 1st DB, opening the XML into a disconnected recordset, connecting the disconnected recordset to the 2nd DB, and then updating the batch. It runs now, but doesn't seem to do anything. Maybe you have to edit the first couple lines of the XML before loading it into the 2nd recordset, but I'm not sure.

I just recently posted a question regarding this that has the code snippet I'm currently using. Look under "Can I copy Recordsets with Rst.Save "file.xml", ..".

Doug Davison
ddavison@horizonhobby.com
Horizon Hobby, Inc.

I'm actually doing the opposite way.. from Access Database to SQL Server. If anyone can give me a example I would appreciate it.

About going the way you want.. it's easier. You can use the INSERT INTO command.

ie INSERT INTO target IN "C:\dir\databasename.mdb" WHERE ...

Look it up in the SQL Help, INSERT INTO Statement, and IN Clause.

That is great!! I have used the INSERT INTO statement many times but completely overlooked the IN statement.

Thanks!!

Doug Davison
ddavison@horizonhobby.com
Horizon Hobby, Inc.

Glad to help..
BTW those are cool things you sell at your website. Especially the heli stuff. Didn't know remotes get that complex!

Yep. We have lots of big boy toys. I'm in the process of building an indoor helicopter right now, but I haven't had a chance to fly it yet. The helicopters require more advanced digital radios then the 1/4 scale planes do, but they are all pretty cool.

You should take a look at our career opportunities if you're in the central Illinois area.

Thanks again for the suggestion,

Doug Davison
ddavison@horizonhobby.com
Horizon Hobby, Inc.