Karen Hill wrote:
Hi.
I have an ODBC Driver for my database. I would like to be able to
connect to that database via VBA code (not by linking the tables) using
ADO to set the is it RecordSource ?? / RecordSet?? of a form. I would
like to be able to edit data on that form and have it automatically
save the changes.
What would an example of that code look like?
Thanks in advance Access Gurus!
Sub Form_Open(Cancel As Integer)
' Purpose:
' Assign an ADO recordset to the Recordset property of an
' MS Access 2000 form.
' Note:
' This code must be in the Form's Form_Open() event procedure.
' Source:
' MS KB Article Q227053
' Modified:
'
Const strServer = "MyServer"
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "MSDataShape"
'The code below uses a sample server name, user ID, and password.
'Be sure to use your actual server name, user ID, and password.
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA " & _
"SOURCE=" & strServer &
";DATABASE=Pubs;Trusted_Connection=Yes;" 'UID=sa;PWD=;"
.CursorLocation = adUseServer
.Open
End With
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM Authors"
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs
' If the rs.Source was a JOINed query the table w/ the unique
' records would be set to .UniqueTable.
Me.UniqueTable = "Authors"
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)