By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,481 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,481 IT Pros & Developers. It's quick & easy.

ADO

P: n/a
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!

May 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
An example might look like this. Depending on why you want to do this,
and to what kind of db you are connecting this code should be modified.

Private Sub Form_Open(Cancel As Integer)
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
Set r = New ADODB.Recordset
With c
c.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\northwind.mdb;" _
& "Jet OLEDB:System database=C:\Documents and Settings\Lyle
Fairfield\Application Data\Microsoft\Access\System.mdw"
With r
..ActiveConnection = c
..CursorLocation = adUseClient
..CursorType = adOpenStatic
..LockType = adLockBatchOptimistic
..Open "SELECT * FROM Employees ORDER BY LastName, FirstName"
End With
Set Me.Recordset = r
End Sub

May 4 '06 #2

P: n/a
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)
May 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.