в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:50Z

1 версия

Новая страница

==Build the SQL statement dynamically==



<source lang="vb">

Sub MyFirstConnection2()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strSQL As String
Dim strSearch As String

strSearch = "Lee"

strSQL = "SELECT FirstName, LastName FROM Employees" & _
" WHERE LastName = " & " "" & strSearch & """

Set myConnection = CurrentProject.Connection

Set myRecordset = New ADODB.Recordset
myRecordset.Open strSQL, myConnection

Do Until myRecordset.EOF
Debug.Print myRecordset.Fields("FirstName"), _
myRecordset.Fields("LastName")
myRecordset.MoveNext
Loop
myRecordset.Close
myConnection.Close
Set myConnection = Nothing
Set myRecordset = Nothing
End Sub
</source>

==Creat a SQL statement and append parameter as ceriteria==



<source lang="vb">

Sub FindByPrice2(curPrice As Currency)
Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim strMatches As String
Dim intCounter As Integer
strSQL = "SELECT * FROM tblSales WHERE AmountPaid = " & curPrice
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rec.EOF
Debug.Print rec!SalesID
rec.MoveNext
Loop
intCounter = rec.RecordCount
Debug.Print FormatCurrency(curPrice)
rec.Close
End Sub
</source>

==Creating a Parameter Query==



<source lang="vb">

Sub Create_ParameterQuery()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.rumand
Dim strPath As String
Dim strSQL As String
Dim strQryName As String
On Error GoTo ErrorHandler
strPath = CurrentProject.Path & "\mydb.mdb"
strSQL = "Parameters [Type Country Name] Text;" & _
"SELECT Customers.* FROM Customers WHERE " _
& "Customers.Country=[Type Country Name];"
strQryName = "Customers by Country"
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
Set cmd = New ADODB.rumand
cmd.rumandText = strSQL
cat.Procedures.Append strQryName, cmd
Set cmd = Nothing
Set cat = Nothing
Exit Sub
ErrorHandler:
If InStr(Err.Description, "already exists") Then
cat.Procedures.Delete strQryName
Resume
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub
</source>

==Executing a SQL Statement Containing Parameters==



<source lang="vb">

Public Sub UpdateWithSQL()
Dim cmd As New ADODB.rumand
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim strConn As String
Dim strSQL As String
strConn = "Provider=SQLOLEDB.1;" & _
"Data Source=(local); Initial Catalog=NorthWind;" & _
"Integrated Security=SSPI"
Set conn = New ADODB.Connection
conn.Open strConn
Set cmd = New ADODB.rumand
cmd.rumandText = "UPDATE Products " & _
"SET OrderDate = OrderDate, " & _
"ShipVia = ShipVia, " & _
"Freight = Freight " & _
"WHERE OrderID = OrderID"
cmd.rumandType = adCmdText
cmd.ActiveConnection = conn
Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("OrderID").Value = 1
Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("OrderDate").Value = "10/10/2007"
Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("ShipVia").Value = 2
Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("Freight").Value = "1.5"
cmd.Execute
conn.Close
End Sub
</source>

==Running Parameter Queries==



<source lang="vb">

Sub RunParameterQuery(datStart As Date, datEnd As Date)
Dim cmd As ADODB.rumand
Dim rst As ADODB.Recordset
Set cmd = New ADODB.rumand
cmd.ActiveConnection = CurrentProject.Connection
cmd.rumandText = "Select * from Employees " & _
"Where BirthDate Between ? and ?"
cmd.rumandType = adCmdText
Set rst = cmd.Execute(Parameters:=Array(datStart, datEnd))
Do Until rst.EOF
Debug.Print rst("EmployeeID"), rst("BirthDate")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cmd = Nothing
End Sub
</source>

==User InputBox to read SQL statement parameter==



<source lang="vb">

Sub command_parameters()
Dim conn As New Connection
Dim rec As New Recordset
Dim comm As New Command
Dim ws As Worksheet
Dim i&, countryname$
Set ws = ThisWorkbook.Worksheets("command")
conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
"Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"
Set comm.ActiveConnection = conn
comm.rumandText = "SELECT companyname FROM customers WHERE country = ?"
countryname = InputBox("Please type in a country name (i.e. "germany").")
comm.Parameters(0) = countryname
rec.Open comm
ws.[a1].CopyFromRecordset rec
rec.Close: conn.Close
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/SQL Parameter - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия