Public Sub ExecTans(ByVal sqlStrList As String()) Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserLog.mdb") Dim trans As OleDb.OleDbTransaction = Nothing Try If (conn.State = ConnectionState.Closed) Then conn.Open() End If Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand() cmd.Connection = conn cmd.CommandType = CommandType.Text trans = conn.BeginTransaction cmd.Transaction = trans Dim i As Integer For i = 0 To sqlStrList.GetUpperBound(0) cmd.CommandText = sqlStrList(i) cmd.ExecuteNonQuery() Next trans.Commit() Catch ex As OleDb.OleDbException trans.Rollback() Throw (New Exception(ex.Message)) Finally conn.Close() End Try End Sub
也可以先将Acess中的数据先读出来,保存到datatable 然后插入到SQL Server Public Sub Insert() Dim dt As DataTable = New DataTable() Dim dr As DataRow dt.Columns.Add(New DataColumn("name")) Dim j As Integer For j = 1 To 10 dr = dt.NewRow() dr(0) = "name" + j.ToString() dt.Rows.Add(dr) Next DataGrid2.DataSource = dt DataGrid2.DataBind() Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserLog.mdb") conn.Open() Dim myAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter() Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Insert into mm (name) values (@name)", conn) cmd.Parameters.Add("@name", "name") cmd.Parameters.Item("@name").SourceColumn = dt.Columns("name").ColumnName myAdapter.InsertCommand = cmd myAdapter.Update(dt) conn.Close() End Sub
Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserLog.mdb")
Dim trans As OleDb.OleDbTransaction = Nothing
Try
If (conn.State = ConnectionState.Closed) Then
conn.Open()
End If
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
trans = conn.BeginTransaction
cmd.Transaction = trans
Dim i As Integer
For i = 0 To sqlStrList.GetUpperBound(0)
cmd.CommandText = sqlStrList(i)
cmd.ExecuteNonQuery()
Next
trans.Commit()
Catch ex As OleDb.OleDbException
trans.Rollback()
Throw (New Exception(ex.Message))
Finally
conn.Close()
End Try
End Sub
然后插入到SQL Server
Public Sub Insert()
Dim dt As DataTable = New DataTable()
Dim dr As DataRow
dt.Columns.Add(New DataColumn("name"))
Dim j As Integer
For j = 1 To 10
dr = dt.NewRow()
dr(0) = "name" + j.ToString()
dt.Rows.Add(dr)
Next
DataGrid2.DataSource = dt
DataGrid2.DataBind()
Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserLog.mdb")
conn.Open()
Dim myAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Insert into mm (name) values (@name)", conn)
cmd.Parameters.Add("@name", "name")
cmd.Parameters.Item("@name").SourceColumn = dt.Columns("name").ColumnName
myAdapter.InsertCommand = cmd
myAdapter.Update(dt)
conn.Close()
End Sub
程序实现楼上都说了,手工导入就是右键-〉所有任务-〉导入数据-〉选择access数据源-〉后面的不用我说了吧?
呵呵
Response.Write((p.Substring(p.LastIndexOfAny("\"), p.Length - p.LastIndexOfAny("\"))).Replace("\", ""))
DropDownList1.Items.Add(New ListItem((p.Substring(p.LastIndexOfAny("\"), p.Length - p.LastIndexOfAny("\"))).Replace("\", ""), "1"))
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
bulk insert 的用法可以参考sql联机丛书