Using VBA, Export Table from one SQL Server to a different SQL Server

[kjv1611]

Is it possible, Using VBA, to export a table from one SQL Server to another SQL Server, without the data ever being pulled into Access?

This may not be possible, but thought I'd ask. Any thoughts, references, suggestions?

Thanks,

 

[smedvid]

Well... right off the bat...

I would recommend setting up a SSIS ETL Process to transfer the data. You can set a flag in a control table that is checked every few minutes by a job, and it the flag is found the Job will start the ETL Process (SSIS formerly DTS)... then delete the flag from the table.

Another approach is to use a SQL Server Stored Procedure that will simply execute a few TSQL commands...
- Trauncate Target Table
- Select * INTO Target Table from Source Table

And, if really needed you can execute the TSQL commands above directly from MS Access via ADO Calls in VBA Code provided you have the necessary SQL Server Permissions and ADO connection set up.

I hope this was helpful...



Steve Medvid
IT Consultant & Web Master

 

[kjv1611]

Thanks for the thoughts. I'll look into those. I'm afraid I may not have appropriate permissions. Of course, I could get those permissions, if that ends up being best and gets approved, but I'm really hoping to get this setup for someone else.. and they may not then have the right permissions. So, I'll look, but may end up just leaving this part manual, and automate the rest... I'll keep thinking though, and looking... Thanks for the suggestions. I'll post back and let you know what if any of that I can use..

Thanks as well for any other thoughts or suggestions..

 

[kjv1611]

Alright, haven't been able to look at this one much for a while, but looking again. I did find this method in VBA:

DoCmd.TransferSQLDatabase[/LINK]

But that one transfers an entire database between SQL Servers. So if that's possible, then SURELY it's possible to move a table???

Thanks for any ideas. I'm still looking, but so far, no go.

 

[kjv1611]

Well, I did find my thread here.

http://www.tek-tips.com/viewthread.cfm?qid=1649722&page=2

..via this search. It was the 3rd result in the set.

It has the question for exactly what I'm looking for, but of course, it's still not answered.

 

[kjv1611]

Alright, I sorted out a solution. I'll basically be using passthrough queries via VBA to do the work. The servers have a connection between them already, so I can just do straight queries.

What I'll do is use Chunking to keep from overloading the connection, since the recordset is currently at 150k records and growing - only 5 or 6 columns, but still 150k records. That's not super terrible, but I'll chunk it just to make sure no issues arise.

So it'll be like this:

--If the table exists on the "to" server, then drop it.
--create the table on the "to" server.
--Insert into the "to" server table, Select * from the "from" from server table, chunked sets..

I tested it with 1,000 rows and it worked perfectly.

What I'll have is a couple of passthrough query objects set up, and I'll modify the SQL of those objects for each step, run, and move on to the next step afterwards.