Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Using ADO

I have been trying to find the syntax for using ADO with Access. Can someone give me a jump start? Thanks.

Duke

Comments

  • Geoff-SGeoff-S Member Posts: 82
    : I have been trying to find the syntax for using ADO with Access. Can someone give me a jump start? Thanks.
    :
    : Duke
    :
    Hi,
    A lot depends on which version of Access you are using. Prior to Access 2000 (V.9) ADO is not supported, and in that Version there are some serious issues when you need an updatable recordset. From Access XP (V.10) these issues are resolved. But in any case, ADO is used in Access for handling connections to a SQL-Server database, not the native Jet-based Database of Access. Although you can use ADO to connect to a Jet database Microsoft do not recommend this, and state that DAO is the preferred method as it is native to Jet.
    All that aside, if you want to learn more a good place to start would be the following article about binding an Access Form to an ADO record source http://support.microsoft.com/default.aspx?scid=kb;en-us;281998

    and for a general overview comparing DAO and ADO see :-
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconUsingADODAORDOInVisualBasic.asp

    :-)


  • btsebbtseb Member Posts: 67
    : : I have been trying to find the syntax for using ADO with Access. Can someone give me a jump start? Thanks.
    : :
    : : Duke
    : :
    : Hi,
    : A lot depends on which version of Access you are using. Prior to Access 2000 (V.9) ADO is not supported, and in that Version there are some serious issues when you need an updatable recordset. From Access XP (V.10) these issues are resolved. But in any case, ADO is used in Access for handling connections to a SQL-Server database, not the native Jet-based Database of Access. Although you can use ADO to connect to a Jet database Microsoft do not recommend this, and state that DAO is the preferred method as it is native to Jet.
    : All that aside, if you want to learn more a good place to start would be the following article about binding an Access Form to an ADO record source http://support.microsoft.com/default.aspx?scid=kb;en-us;281998
    :
    : and for a general overview comparing DAO and ADO see :-
    : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconUsingADODAORDOInVisualBasic.asp
    :
    : :-)
    :
    :
    :
    @[/red]

    [blue]Here is an example of one way to do it:[/blue]
    [code]
    Option Explicit

    Dim objConn as ADODB.Connection
    Dim objRS as ADODB.RecrodSet
    Dim rsMinimum as ADODB.RecordSet
    Dim strQuery as String


    Private Sub Form_Load()
    Set objConn = New ADODB.Connection
    Set objRs = New ADODB.Recordset
    Set rsMinimum = New ADODB.Recordset

    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;Data Source=" & App.Path & _
    "DataBase.mdb"
    objConn.Open

    With objRS
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "tblTableName", objConn, , , adCmdTable
    End With

    strQuery = "SELECT Min([Customer_num]) as Minimum FROM tblTableName"

    With rsMinimum
    .CursorLocation = adUseClient
    .Open strQuery, objConn, adOpenDynamic, adLockOptimistic, adCmdText
    End With
    End Sub[/code]

Sign In or Register to comment.