Question

VB如何执行SQL脚本

Constr = "Provider=Microsoft.jet.oledb.4.0;Data Source=D:\赢家\赢家系列\大赢家\Data\3Ddyj.mdb"
Conn.Open Constr
Conn.Execute "DELETE Issue_3D.LotteryID FROM Issue_3D WHERE (((Issue_3D.LotteryID)=50001 Or (Issue_3D.LotteryID)=50002 Or (Issue_3D.LotteryID)=50003 Or (Issue_3D.LotteryID)=50004 Or (Issue_3D.LotteryID)=50005 Or (Issue_3D.LotteryID)=50006 Or (Issue_3D.LotteryID)=50007 Or (Issue_3D.LotteryID)=50008 Or (Issue_3D.LotteryID)=50009 Or (Issue_3D.LotteryID)=50010 Or (Issue_3D.LotteryID)=50011 Or (Issue_3D.LotteryID)=50012 Or (Issue_3D.LotteryID)=50013 Or (Issue_3D.LotteryID)=50014 Or (Issue_3D.LotteryID)=50015 Or (Issue_3D.LotteryID)=50016 Or (Issue_3D.LotteryID)=50017 Or (Issue_3D.LotteryID)=50018))" '删去id=50001-50018的数据
下面要执行*.sql 脚本，脚本内容“DELETE Issue_3D.LotteryID FROM Issue_3D WHERE 。。。。。”
如何写程序？

Answer 1

1.
Public Sub ExecuteSQLScriptFile(cn as ADODB.Connection,sqlFile As String)
Dim strSql As String, strTmp As String

Open sqlFile For Input As #1
strSql = ""
Do While Not EOF(1)
Line Input #1, strTmp
If UCase$(strTmp) = "GO" Then
cn.Execute strSql
strSql = ""
Else
strSql = strSql & strTmp & vbCrLf
End If
Loop
If strSql <> "" Then cn.Execute strSql
Close #1
End Sub

2.
Public Sub ExecuteSQLScriptFile(cn as ADODB.Connection,sqlFile As String)
Dim sql as string

sql="master.dbo.xp_cmdshell ' osql -U username -P password -i " & sqlFile
cn.execute sql
End Sub

Answer 2

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Dim SQL as string

cn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;Data Source=D:\赢家\赢家系列\大赢家\Data\3Ddyj.mdb"
cn.Open

rs.CursorLocation = adUseClient
SQL = "select * from Expense"
rs.Open SQL, cn, adOpenKeyset, adLockPessimistic

-----------------------------------------

在rs.open SQL中就可执行语句！！