November 20, 2001 at 10:48 pm
Has anyone used the Transfer object in association with the Database object using VB, I need to transfer some tables from one database to the another on the same SQL Server. I know there is something simple that I am missing.
November 21, 2001 at 10:59 am
I have not. Andy is out of touch for the holidays for the most part, but I will try and contact him.
Steve Jones
November 21, 2001 at 11:02 am
Thanks, I will be looking forward to any insights he might have.
November 21, 2001 at 6:35 pm
I have not tried it yet. Im catching up on email, give me a day or so and I'll see what I can come up with.
Andy
November 22, 2001 at 3:28 pm
Here is sample code to copy the structure of a table from Northwind to Pubs and then append the data.
Dim oServer As SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim otransfer As SQLDMO.Transfer
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
Set oDatabase = oServer.Databases("Northwind")
Set otransfer = New Transfer
With otransfer
.AddObjectByName "Categories", SQLDMOObj_UserTable
.CopyData = SQLDMOCopyData_Append
.DestServer = "."
.DestDatabase = "Pubs"
.DestUseTrustedConnection = True
End With
oDatabase.Transfer otransfer
Set oDatabase = Nothing
oServer.DisConnect
Set oServer = Nothing
It's an interesting object, lots of times it might be handy! I'll probably do an article or two in the next couple months just to document things I find as I explore it more. Depending on what you're trying to accomplish it may be helpful to walk through the wizard to think about exactly which options you're setting, then explore how to do it code. An easier way is to create a package that does the task for you and tweak until it's correct, then save out as VB code.
Andy
November 23, 2001 at 1:31 pm
Worked like a charm! That saves me alot of coding.
November 23, 2001 at 1:55 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply