Como hacer UpdateDatabase de sql7 en vb

Buenos dias:
tengo unos codigo para realizar UpdateDatabase desde base de datos access pero quiero transformalo para que me funciones desde sql7.
Este es codigo que tengos que lo he hecho desde varios codigos que me han dados algunos amigos.......
Option Explicit
Private Const Provider As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Dim mstrConnectionStringDB1 As String
Dim mstrConnectionStringDB2 As String
Dim mastrTables() As String
Dim mastrTablesDB1() As String
Dim mastrTablesDB2() As String
Dim mstrTableToBeUpdated As String
Dim mstrOldTable As String
Private Sub cmdUpdate_Click()
Dim intLoop As Integer
mstrConnectionStringDB1 = Provider & "Data Source = " & App.Path & "\" & Text1.Text
mstrConnectionStringDB2 = Provider & "Data Source = " & App.Path & "\" & Text2.Text
Call LoadTablesInArrays
For intLoop = 0 To UBound(mastrTables())
mstrTableToBeUpdated = mastrTables(intLoop)
Call ImportTable
Call CreateQuery
Next intLoop
MsgBox "Update Succesfully Terminated"
End Sub
Private Sub LoadTablesInArrays()
Call TablesDB1
Call TablesDB2
Call Merge2StringArraysInOne
End Sub
Private Sub Merge2StringArraysInOne()
Dim intLoop1 As Integer
Dim intLoop2 As Integer
Dim intFound As Integer
Dim strTableNameToBeMatched As String
For intLoop1 = LBound(mastrTablesDB1) To UBound(mastrTablesDB1)
strTableNameToBeMatched = mastrTablesDB1(intLoop1)
For intLoop2 = LBound(mastrTablesDB2) To UBound(mastrTablesDB2)
If strTableNameToBeMatched = mastrTablesDB2(intLoop2) Then
ReDim Preserve mastrTables(intFound)
mastrTables(intFound) = strTableNameToBeMatched
intFound = intFound + 1
Exit For
End If
Next intLoop2
Next intLoop1
End Sub
Private Sub TablesDB1()
Dim oConn As ADODB.Connection
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim intNumberOfTables As Integer
Set oConn = New Connection
oConn.ConnectionString = mstrConnectionStringDB1
oConn.Open
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = oConn
Set oTable = New Table
For Each oTable In oCat.Tables
If oTable.Type <> "SYSTEM TABLE" And oTable.Type <> "SYSTEM VIEW" And Left$(oTable.Name, 3) = "tbl" Then
ReDim Preserve mastrTablesDB1(intNumberOfTables)
mastrTablesDB1(intNumberOfTables) = oTable.Name
intNumberOfTables = intNumberOfTables + 1
End If
Next
Set oCat = Nothing
Set oTable = Nothing
oConn.Close
Set oConn = Nothing
End Sub
Private Sub TablesDB2()
Dim oConn As ADODB.Connection
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim intNumberOfTables As Integer
Set oConn = New Connection
oConn.ConnectionString = mstrConnectionStringDB2
oConn.Open
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = oConn
Set oTable = New Table
For Each oTable In oCat.Tables
If oTable.Type <> "SYSTEM TABLE" And oTable.Type <> "SYSTEM VIEW" And Left$(oTable.Name, 3) = "tbl" Then
ReDim Preserve mastrTablesDB2(intNumberOfTables)
mastrTablesDB2(intNumberOfTables) = oTable.Name
intNumberOfTables = intNumberOfTables + 1
End If
Next
Set oCat = Nothing
Set oTable = Nothing
oConn.Close
Set oConn = Nothing
End Sub
Private Sub ImportTable()
Dim cnn1 As ADODB.Connection
Dim cmdQuery As ADODB.Command
Dim strCnn As String
Dim Rs1 As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim sTable As String
Dim First As String
Dim UserPath As String
Dim NewTable As String
Dim SelectString As String
Dim FromString As String
Dim FromSource As String
Dim SQLtext As String
Dim Msg As String
Dim Destination As String
On Error GoTo ErrorHandler
sTable = mstrTableToBeUpdated
Set cnn1 = New ADODB.Connection
cnn1.Open mstrConnectionStringDB1
Set cmdQuery = New ADODB.Command
Set Rs1 = New ADODB.Recordset
Rs1.ActiveConnection = mstrConnectionStringDB1
Set cmdQuery.ActiveConnection = cnn1
SelectString = "SELECT * INTO"
FromString = "FROM"
FromSource = App.Path & "\" & Text1.Text
NewTable = mstrTableToBeUpdated & "1"
mstrOldTable = NewTable
Destination = App.Path & "\" & Text2.Text
SQLtext = SelectString & Space(1) & "[" & Destination & "]" _
& "." & NewTable & Space(1) & FromString & Space(1) _
& "[" & FromSource & "]" & "." & sTable
cmdQuery.CommandText = SQLtext
Set Rs1 = cmdQuery.Execute()
If Err.Number = 0 Then
End If
cnn1.Close
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Else
Msg = "Unexpected error #" & Str(Err.Number)
Msg = Msg & " occurred: " & Err.Description
MsgBox Msg, vbCritical
End Select
Resume Next
End Sub
Private Sub CreateQuery()
Dim oConn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim oCat As ADOX.Catalog
Dim strSQL As String
strSQL = "INSERT INTO " & mstrTableToBeUpdated & " SELECT " & mstrOldTable & ".* FROM " & mstrTableToBeUpdated
Set oConn = New Connection
Set cmd = New ADODB.Command
Set Rs1 = New ADODB.Recordset
Set oCat = New ADOX.Catalog
oConn.ConnectionString = mstrConnectionStringDB2
oConn.Open
oCat.ActiveConnection = oConn
strSQL = "DELETE " & mstrTableToBeUpdated & ".* FROM " & mstrTableToBeUpdated
cmd.CommandText = strSQL
oConn.Execute strSQL
strSQL = "INSERT INTO " & mstrTableToBeUpdated & " SELECT " & mstrOldTable & ".* FROM " & mstrOldTable
cmd.CommandText = strSQL
oConn.Execute strSQL
strSQL = "DROP TABLE " & mstrOldTable
cmd.CommandText = strSQL
oConn.Execute strSQL
Set oCat = Nothing
Set cmd = Nothing
Set oConn = Nothing
End Sub
Espero que me puedas ayudar a transformarlo para conectarme con sql7

1 respuesta

Respuesta
1
¿Qué es lo que necesitas exactamente?
¿Conectarte a SQL7? O ¿Transformar algo?
Buenas Tardes
lo que necesito hacer que ese codigo me conecte con una tabla de sql7 y que realize el UpdateDataBaseTablasDatos en esas tabla de sql7 xq esta corriendo desde microsoft access....
Para saber como conectarte a SQL y otras fuentes de datos, te dejo el siguiente Link
http://www.connectionstrings.com/

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas