ник: alecks_lp
Re: В хелпах есть все. Ну, почти все Порылся в хелпах. Простят меня модераторы приведу пример целиком
Option Compare Database
Option Explicit
These three examples demonstrate how the Save and Open methods can be used together.
Assume you are going on a business trip and want to take along a table from a database.
Before you go, you access the data as a Recordset and save it in a transportable form.
When you arrive at your destination, you access the Recordset as a local,
disconnected Recordset. You make changes to the Recordset, then save it again.
Finally, when you return home, you connect to the database again and update it
with the changes you made on the road.
First, access and save the Authors table.
BeginSaveVB
To integrate this code
replace the data source and initial catalog values
in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
recordset and connection variables
Dim rstAuthors As ADODB.RecordSet
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQLAuthors As String
Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=sqloledb;Data Source=MySqlServer;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI;"
Cnxn.Open strCnxn
Set rstAuthors = New ADODB.RecordSet
strSQLAuthors = "SELECT au_id, au_lname, au_fname, city, phone FROM Authors"
rstAuthors.Open strSQLAuthors, Cnxn, adOpenDynamic, adLockOptimistic, adCmdText
For sake of illustration, save the Recordset to a diskette in XML format
rstAuthors.Save "c:Pubs.xml", adPersistXML
clean up
rstAuthors.Close
Cnxn.Close
Set rstAuthors = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
clean up
If Not rstAuthors Is Nothing Then
If rstAuthors.State = adStateOpen Then rstAuthors.Close
End If
Set rstAuthors = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
EndSaveVB
At this point, you have arrived at your destination.
You will access the Authors table as a local, disconnected Recordset.
Dont forget you must have the MSPersist provider on the machine that
you are using in order to access the saved file, a:Pubs.xml.
BeginSave2VB
Public Sub Main()
On Error GoTo ErrorHandler
Dim rst As ADODB.RecordSet
Set rst = New ADODB.RecordSet
For sake of illustration, we specify all parameters
rst.Open "c:Pubs.xml", "Provider=MSPersist;", adOpenForwardOnly, adLockBatchOptimistic, adCmdFile
Now you have a local, disconnected Recordset - Edit as you desired
(In this example the change makes no difference)
rst.Find "au_lname = Carson"
If rst.EOF Then
Debug.Print "Name not found."
Exit Sub
End If
rst!city = "Chicago"
rst.Update
Save changes in ADTG format this time, purely for sake of illustration.
Note that the previous version is still on the diskette, as a:Pubs.xml.
rst.Save "c:Pubs.adtg", adPersistADTG
clean up
rst.Close
Set rst = Nothing
Exit Sub
ErrorHandler:
clean up
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
EndSave2VB
Finally, you return home. Now update the database with your changes.
BeginSave3VB
Public Sub Main()
On Error GoTo ErrorHandler
To integrate this code
replace the data source and initial catalog values
in the connection string
Dim Cnxn As New ADODB.Connection
Dim rst As ADODB.RecordSet
Dim strCnxn As String
Set rst = New ADODB.RecordSet
The lock mode is batch optimistic because we are going to
use the UpdateBatch method.
rst.Open "c:Pubs.adtg", "Provider=MSPersist;", adOpenForwardOnly, adLockBatchOptimistic, adCmdFile
Connect to the database, associate the Recordset with the connection
then update the database table with the changed Recordset
strCnxn = "Provider=SQLOLEDB;Data Source=MySqlServer;Integrated Security=SSPI;Initial Catalog=pubs;"
Cnxn.Open strCnxn
rst.ActiveConnection = Cnxn
rst.UpdateBatch
clean up
rst.Close
Cnxn.Close
Set rst = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
clean up
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
EndSave3VB
Демонстрация мощи и гибкости ADO
В комментариях нужды нет