ADO connect example?

[CedarTree]

Anyone please have sample coding to get me started doing the following using ADO:
>> Connecting to SQL server table
>> Reading records in a loop
>> Reading a specific "query" record
>> Updating a record

THANKS!!! I googled a lot but can't find a comprehensive / user-friendly site (at least not yet).

 

[Ranman256]

If you are in Access, you don't need ADO. All data is available via recordset.
set rs = currentdb.openrecordset("select * from table")

if you are in another app, then use ADO, like this Excel example:

Public Sub CopyRST()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB
Dim vProvid

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

DB = "\\server\folder1\myDatabase.mdb"
vProvid = "Microsoft.Jet.OLEDB.4.0" ' or for Sqlsvr: "SQLOLEDB"


With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Data Source=" & DB & ";Jet OLEDB"
End With

'Set rs = con.Execute("qsMyDataQuery")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
con.Close
End Sub

 

[CedarTree]

Thanks - client side is Access, but I want to connect to SQL server via ADO.
Also, is there quick coding to take a SQL table and insert that data into a local access table using ADO?

Maybe a related question is... can you link tables into Access via ADO versus DAO?

 

[CJ_London]

just curious - why use ADO? although you can assign the recordset to a form, you will need your own filter and sort routines if they are required as the ones supplied as standard with forms only work with DAO. And reports won't work with an ADO recordset at all. ADO has its uses but not necessarily the best way to connect to sql server.

But to answer your first question, would need to know what sql server expects in order to connect but might be something as discussed in this link
https://stackoverflow.com/questions...-with-ado-to-sql-server-windows-authenticated. You may also want to use a disconnected recordset.

reading records in a loop is the same as you would with a dao.recordset

while not rs.eof
    'do something
    rs.movenext
wend

don't know what you mean by 'Reading a specific "query" record'

updating a record - same as dao so long as the recordset is not disconnected - actually not quite sure what the context of the question is

, is there quick coding to take a SQL table and insert that data into a local access table using ADO?

you would need to loop through the ado recordset and run multiple append queries - won't take long to code, but will take time to work, particularly for large datasets

 

[CedarTree]

Re: filling in a local table, using DAO/linked table, I often run a query that grabs the SQL server table data, and inserts into the local table, in one fell swoop, e.g., see below. Can't do that in batch in ADO? Has to be record by record? Imagine that would be slower then...

    sql="SELECT * FROM " ... [SERVER TABLE WITH CONDITIONS]
    dbTemp.QueryDefs("Temp1").sql = sql
    dbTemp.QueryDefs("Temp1").ReturnsRecords = True
    sql = "INSERT INTO " + sLocal + " SELECT * FROM Temp1"
    Set qdf = dbTemp.CreateQueryDef("", sql)
    qdf.Execute
    qdf.Close
    Set qdf = Nothing

 

[CedarTree]

Re: reading a specific record, I can grab a recordset with DAO easily enough matching certain criteria, e.g., I create a recordset as I please so I can loop through specific records, and I call that recordset using this:

Function fnServerRecordset(pSQL As String) As DAO.Recordset

    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = gsConnection
    qdf.ReturnsRecords = True
    qdf.sql = pSQL
    Set fnServerRecordset = qdf.OpenRecordset
    qdf.Close
    Set qdf = Nothing
    
End Function

 

[CJ_London]

Can't do that in batch in ADO? Has to be record by record? Imagine that would be slower then...

yup

 

[Pat Hartman]

Access internally is DAO. The recordsets for forms/reports are DAO NOT ADO.

If you are doing this because you want to use unbound forms, you are using the wrong front end software. Access is a Rapid Application Development (RAD) tool. It's best feature is bound forms. They are what make Access Access and if you don't use them, you are taking all the dead weight and tossing away the good stuff.

If you follow good client/server design methods, you will be fine using linked tables and bound forms.

 

[GPGeorge]

Access internally is DAO. The recordsets for forms/reports are DAO NOT ADO.

If you are doing this because you want to use unbound forms, you are using the wrong front end software. Access is a Rapid Application Development (RAD) tool. It's best feature is bound forms. They are what make Access Access and if you don't use them, you are taking all the dead weight and tossing away the good stuff.

If you follow good client/server design methods, you will be fine using linked tables and bound forms.

Sometimes a question about "how to do XXXX" leads to the answer, "DON'T do XXXX because there's a better way."

As Pat says, this is one of those times. ADO is a great tool for some tasks, but it's not the end goal, so to speak, for all tasks. Bound forms are the shiny part of Access, moving all of the nitty gritty out of your way so you can concentrate on other, more productive things.

That said, I have used ADO to update certain fields in a remote SQL Server database in a handful of situations, almost always, though, those have been cases where I did not want to bind a form to the table but did need to pass certain pieces of data back to that table.

On the other hand, I also think that part of the growth of an Access developer can be exploring all sorts of tools, e.g. learning about ADO and where and when it can be a useful tool. As just noted, the two cases that I can recall using ADO were both where we were aggregating data locally following the import of raw data. That aggregated data needed to be store permanently in a remote SQL Server database for use in a website. Not the raw data, but the aggregated data. Pushing that data via ADO was probably the best solution since it did not require linking to the remote tables.

I'll go see if I can find some of that code--it's been more than 5 years since I retired so it's not handy at the moment. In the meantime, someone will probably have an ADO example already....

 

[Pat Hartman]

As George said, there is nothing wrong with using ADO when the tables are ODBC but not to avoid bound forms. Also, an action query is always preferable to a code loop to update a table.

 

[CedarTree]

Yeah - I was hoping to leverage ADO only because when I've done THOUSANDS of query updates, ADO is faster in that case. I'm guessing the time difference for just 2 records however is not worth losing the convenience of DAO/query updates.

 

[CJ_London]

In what way is ADO faster? Compared with what doing what?

In my experience it comes down to keeping a connection open and indexing. If you are doing thousands of updates involving indexed fields it might be an idea to remove the index, do your updates then reinstated the index - unless the indexed field(s) form part of the criteria or joins.

You mentioned connecting to sql server - if you are looking for performance a better option might be to use stored procedures or passthrough queries so all the processing is done in sql server. And of course that your db is properly designed and indexed

 

[Pat Hartman]

You can run updatequeries using ADO and in theory, they would be faster than DAO against ODBC data sources but update queries are always faster than code loops.

 

[CJ_London]

@Pat Hartman in theory, they would be faster than DAO against ODBC data sources - Why? Same resources?

 

[Pat Hartman]

DAO is optimized for Jet/ACE. ADO is what the rest of the world uses against SQL Server et al. I rarely use ADO even for SQL Server but I am told it is faster. Just make sure to disambiguate all your database objects because the libraries have common names for some objects. I started doing this over 20 years ago when for one short period, ADO was the default library and it made quite the mess with existing applications breaking every one of them that was using DAO code. Not sure why MS ever thought that would be a good idea but they backed off as soon as they could but for those of us working with Access at the time, the damage was done.

 

[GPGeorge]

Wow, this was put into production 10 and a half years ago. I forgot how long it had been. I did remove some irrelevant bits, but the bulk is needed to send an ADO update to the remote server.

Public Function UpdateEquipmentRecord(strTestType As String, _
    intTestSequence As Integer, _
    strWSUCode As String, _
    decTestValue As Variant, _
    decPctChange As Variant, _
    StrLocation As Variant, _
    StrOperator As Variant) As Integer

    On Error GoTo errHandler

    Dim oCn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim RecordsAffectedCount As Long


    'We probably need no more than Four parameters, but to avoid future refactoring, set up 7

    Dim prm0 As New ADODB.Parameter
    Dim prm1 As New ADODB.Parameter
    Dim prm2 As New ADODB.Parameter
    Dim prm3 As New ADODB.Parameter
    Dim prm4 As New ADODB.Parameter
    Dim prm5 As New ADODB.Parameter
    Dim prm6 As New ADODB.Parameter
    Dim prm7 As New ADODB.Parameter
    Dim paramName As String
    Dim paramType As ADODB.DataTypeEnum
    Dim paramDirection As ADODB.ParameterDirectionEnum
    Dim paramValue As String
    Dim ReturnCode As Integer
      
    Set oCn = New ADODB.Connection
          oCn.ConnectionString = UTV("strHostConnection") 'stored credentials that connect to the remote SQL Server.
    End If
    oCn.Open
    oCn.CommandTimeout = 0
    Set oCmd = New ADODB.Command
    oCmd.CommandTimeout = 0
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "InsertPerformance" ' This is the name of a Stored Procedure that executes the append of the new values.

    oCmd.ActiveConnection = oCn


    'prm1
    paramName = "@TestSequence "
    paramType = adInteger
    paramDirection = adParamInput
    paramValue = intTestSequence


    With prm1
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
        '    .Size = 50
    End With
    oCmd.Parameters(1) = prm1


    'prm2
    paramName = "@WSUCode"
    paramType = adVarChar
    paramDirection = adParamInput
    paramValue = strWSUCode


    With prm2
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
        .Size = 15
    End With
    oCmd.Parameters(2) = prm2

    'prm3
    paramName = "@PerformanceValue"
    paramType = adDecimal
    paramDirection = adParamInput
    paramValue = decTestValue

    With prm3
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
    End With
    oCmd.Parameters(3) = prm3

    'prm4
    paramName = "@TestType"
    paramType = adVarChar
    paramDirection = adParamInput
    paramValue = strTestType

    With prm4
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
    End With
    oCmd.Parameters(4) = prm4

    'prm5
    paramName = "@PercentChange"
    paramType = adDecimal
    paramDirection = adParamInput
    paramValue = decPctChange

    With prm5
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
    End With
    oCmd.Parameters(5) = prm5

    'prm6
    paramName = "@PerformanceLocation"
    '    paramType = adVarChar
    paramType = adDecimal
    paramDirection = adParamInput
    paramValue = StrLocation

    With prm6
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
    End With
    oCmd.Parameters(6) = prm6

    'prm7
    paramName = "@Operator"
    paramType = adVarChar
    paramDirection = adParamInput
    paramValue = StrOperator

    With prm7
        .Name = paramName
        .Direction = paramDirection
        .Type = paramType
        .Value = paramValue
    End With
    oCmd.Parameters(7) = prm7

    'return
    With prm0
        .Name = "rc"
        .Type = adBigInt
        .Direction = adParamReturnValue
    End With

    oCmd.Execute RecordsAffectedCount 
    ReturnCode = oCmd.Parameters(0)
    UpdateEquipmentRecord = ReturnCode

CleanUp:

    On Error Resume Next
    Set oCmd = Nothing
    Set oCn = Nothing

ExitProc:


    Exit Function

errHandler:
    If Err = -2147217873 Then
        MsgBox "That Performance Record has been added for this bat." & vbCrLf & _
            "You can adjust Test Results manually on the test Management screen", vbOKOnly, UTV("MBTItleErr") & _
            "Can't Reimport Duplicate Performance Result"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateEquipmentRecord of Module Module1"
  
    End If
    Resume CleanUp
    Resume
End Function