有很多方法可以做到这一点。我建议使用类似的方法将数据从Excel推送到sql Server。
Sub ButtonClick()
'TRUSTED CONNECTION
On Error GoTo errH
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strPath As String
Dim intImportRow As Integer
Dim strFirstName, strLastName As String
Dim server, username, password, table, database As String
With Sheets("Sheet1")
server = .Text@R_615_2419@1.Text
table = .Text@R_615_2419@4.Text
database = .Text@R_615_2419@5.Text
If con.State <> 1 Then
con.Open "Provider=sqlOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
'con.Open
End If
'this is the TRUSTED connection string
Set rs.ActiveConnection = con
'delete all records first if check@R_615_2419@ checked
If .Check@R_615_2419@1 Then
con.Execute "delete from tbl_demo"
End If
'set first row with records to import
'you Could also just loop thru a range if you want.
intImportRow = 10
Do Until .Cells(intImportRow, 1) = ""
strFirstName = .Cells(intImportRow, 1)
strLastName = .Cells(intImportRow, 2)
'insert row into database
con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
intImportRow = intImportRow + 1
Loop
Msg@R_615_2419@ "Done importing", vbInformation
con.Close
Set con = Nothing
End With
Exit Sub
errH:
Msg@R_615_2419@ Err.Description
End Sub
@H_404_4@您也可以尝试使用Where子句。
Sub InsertInto()
'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strsql As String
'Create a new Connection object
Set cnn = New adodb.Connection
'Set the connection string
cnn.ConnectionString = "Provider=sqlOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Excel-PC\sqlEXPRESS"
'cnn.ConnectionString = "DRIVER=sql Server;SERVER=Excel-PC\sqlEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes"
'Create a new Command object
Set cmd = New adodb.Command
'Open the Connection to the database
cnn.Open
'Associate the command with the connection
cmd.ActiveConnection = cnn
'Tell the Command we are giving it a bit of sql to run, not a stored procedure
cmd.CommandType = adCmdText
'Create the sql
strsql = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"
'Pass the sql to the Command object
cmd.CommandText = strsql
'Execute the bit of sql to update the database
cmd.Execute
'Close the connection again
cnn.Close
'Remove the objects
Set cmd = Nothing
Set cnn = Nothing
End Sub
@H_404_4@
602392714
清零编程群