请问access数据库的sql语句的执行函数与sql server数据库的执行函数的代码可不可以一样?
Public Sub Initialize()
Dim strapp As String
strapp = App.path
If Right(strapp, 1) <> "\" Then
strapp = strapp + "\"
End If
CONNECTSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\leon\temp\Plugins\ENU\System_.mdb"End SubPublic Function GetRecordSet(ByVal sqlstr As String) As ADODB.Recordset
Set Db = New ADODB.Recordset
MouseLeft = False
If Trim(sqlstr) = "" Then
Set GetRecordSet = Nothing
Exit Function
End If
If Db.State = adStateOpen Then
Db.Close
End If
Db.Open sqlstr, CONNECTSTR, adOpenKeyset, adLockOptimistic
Set GetRecordSet = Db
End Function以上是我用access数据库时连接数据库和sql语句执行函数的代码,现在我建立了
一个sql server数据库,什么都没有变,只是数据库改成了sql server的,服务器(AA)
,该怎么改以上的代码?
Public Sub Initialize()
Dim strapp As String
strapp = App.path
If Right(strapp, 1) <> "\" Then
strapp = strapp + "\"
End If
CONNECTSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\leon\temp\Plugins\ENU\System_.mdb"End SubPublic Function GetRecordSet(ByVal sqlstr As String) As ADODB.Recordset
Set Db = New ADODB.Recordset
MouseLeft = False
If Trim(sqlstr) = "" Then
Set GetRecordSet = Nothing
Exit Function
End If
If Db.State = adStateOpen Then
Db.Close
End If
Db.Open sqlstr, CONNECTSTR, adOpenKeyset, adLockOptimistic
Set GetRecordSet = Db
End Function以上是我用access数据库时连接数据库和sql语句执行函数的代码,现在我建立了
一个sql server数据库,什么都没有变,只是数据库改成了sql server的,服务器(AA)
,该怎么改以上的代码?
ACCESS:
StrSql = "Select * From Table Where Field Like '*" & FindString & "*'"
SQLSERVER:
StrSql = "Select * From Table Where Field Like '%" & FindString & "%'"
Set rsSearchAll = GetRecordSet(sqlSearchAll)
Call Init_MSHFSearch
With MSHFSearch
If Not rsSearchAll.EOF Then
.ColAlignment = 2
.ColAlignmentFixed = 4
.Rows = 2
Do While Not rsSearchAll.EOF
.TextMatrix(.Rows - 1, 0) = Trim(rsSearchAll("ta.id"))
.TextMatrix(.Rows - 1, 1) = Trim(rsSearchAll("tracer_style"))
.TextMatrix(.Rows - 1, 2) = Trim(rsSearchAll("tracer_size"))
.TextMatrix(.Rows - 1, 3) = Trim(rsSearchAll("tracer_colour"))
.TextMatrix(.Rows - 1, 4) = Trim(rsSearchAll("tracer_Cust"))
.TextMatrix(.Rows - 1, 5) = Trim(rsSearchAll("tracer_PO_No"))
.TextMatrix(.Rows - 1, 6) = Trim(rsSearchAll("tracer_Lot_NO"))
.TextMatrix(.Rows - 1, 7) = Trim(rsSearchAll("tracer_Qty"))
.TextMatrix(.Rows - 1, 8) = Trim(rsSearchAll("tracer_Type"))
如果将这一段access数据库的sql语句转成sql server 语句,咋转?
Access和SqlServer中的很多系统自带函数是不一样的
Dim mycon As ADODB.Connection
Dim rst As ADODB.Recordset
Set mycon = New ADODB.Connection
mycon.ConnectionString = connstring
mycon.Open
Dim stokens() As String
On Error GoTo exectuesql_error
stokens = Split(sql)
If InStr("INSER,DELETE,UPDATE", UCase(stokens(0))) Then
mycon.Execute sql
Else
Set rst = New ADODB.Recordset
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
End If
exectuesql_exit:
Set rst = Nothing
Set mycon = Nothing
Exit Function
exectuesql_error:
Resume exectuesql_exit
End Function