By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,159 Members | 1,941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,159 IT Pros & Developers. It's quick & easy.

How to move table data in one server to another server

P: 4
Hi,
I need to move one table in one server(Live) to another server(Development) through Visual basic coding. Please send soultion for this
Feb 9 '07 #1
Share this Question
Share on Google+
3 Replies


dorinbogdan
Expert 100+
P: 839
Supposing that the table exists on the destination database:

Private Function ExportTable(tbl as String)


End Function
Feb 9 '07 #2

dorinbogdan
Expert 100+
P: 839
I'm sorry for the previous incomplete message.
I'll write a sample code in few moments...
Feb 9 '07 #3

dorinbogdan
Expert 100+
P: 839
The solution I suggest is to use 2 ADODB.Recordset objects, and 2 ADODB.Connection objects (it requires to add a reference to Microsoft ActiveX Data Objects in Project/References)
You just have to update the connection strings strConn1 and strConn2.
(I couldn't check the code, since I don't have VB6 installed).
Don't forget to create the destination table with same structure as source table.

Expand|Select|Wrap|Line Numbers
  1. Private Function ExportTable(strTable As String) 
  2. Dim i As Integer
  3. Dim rs1 As Object 'ADODB.Recordset
  4. Dim rs2 As Object 'ADODB.Recordset
  5. Dim cn1 As Object 'ADODB.Connection
  6. Dim cn2 As Object 'ADODB.Connection
  7. Dim strConn1 As String
  8. Dim strConn2 As String
  9. On Error Goto EH
  10.     ExportTable = False
  11.     strConn1 = "Driver={SQL Server};Server=Live;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
  12.     strConn2 = "Driver={SQL Server};Server=Development;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
  13.     set cn1 = CreateObject("ADODB.Connection")
  14.     set cn2 = CreateObject("ADODB.Connection")
  15.     set rs1 = CreateObject("ADODB.Recordset")
  16.     set rs2 = CreateObject("ADODB.Recordset")
  17.     cn1.Open  strConn1
  18.     cn2.Open  strConn2
  19.  
  20.     rs1.Open "Select * From " & strTable , cn1, adOpenStatic, adLockReadOnly
  21.     rs2.Open "Select top 0 * From " & strTable , cn2, adOpenDynamic, adLockPessimistic
  22.     rs1.MoveFirst
  23.     If Not (rs1.EOF and rs1.BOF) Then  
  24.         While not rs1.EOF
  25.             rs2.AddNew
  26.             For i = 0 to rs1.Fields.Count-1
  27.                 rs2.Fields(i) = rs1.Fields(i) 
  28.             Next
  29.             rs2.Update
  30.             rs1.MoveNext 
  31.         Wend
  32.     End If
  33.     rs1.Close
  34.     rs2.Close
  35.  
  36.     ExportTable = True
  37.  
  38. EH:
  39.     MsgBox Err.Number & ": " & Err.Description 
  40. End Function
  41.  
Feb 9 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.