datasheet view for query using ADODB

[NigelShaw]

Hi

another adodb question

i can open a recordset created with a sql string using DAO quite easily by using the OpenQuery and / or runSQL method but how can i do this when i collect a recordset using ADODB?

i tried using the same options but i get nothing.

here is my code for opening the connection and collecting the recordset so far

Sub SQLCollectData()

    '##set up your variables##
    Dim m_oRecordset As ADODB.Recordset
    Dim m_sConnStr As String
    Dim sSQL As String
    Dim oConnection1 As ADODB.Connection
    Dim strSource As String
    Dim strSourceEnviron As String
    Dim strSourceServer As String
    Dim strCatalog As String


    '##collect environment data##
    strSourceEnviron = VBA.Environ("computername")
    strSourceServer = "\CMJ"
    strSource = strSourceEnviron & strSourceServer
    strCatalog = "SalonIris"

    '##create connection to SQL server##
    m_sConnStr = "Provider='SQLOLEDB';Data Source='" & strSource & "';" & _
    "Initial Catalog='" & strCatalog & "';Integrated Security='SSPI';"

    Set oConnection1 = New ADODB.Connection
    oConnection1.CursorLocation = adUseClient
    oConnection1.Open m_sConnStr

    '##Create specific SQL statement##
    'sSQL = "SELECT fldFirstName, fldLastName, fldActive " & _
    '"FROM tblEmployees WHERE fldActive = 1"

    '##Create specific SQL statement##
    sSQL = "SELECT fldFirstName, fldLastName, fldDateScheduled, fldCheckedIn, fldCNClosed " & _
    "FROM tblTicketsSummary WHERE fldCheckedIn <> 0"

    '##Run SQL##
    Set m_oRecordset = New ADODB.Recordset
    m_oRecordset.Open sSQL, oConnection1, adOpenStatic, _
    adLockBatchOptimistic, adCmdText

    m_oRecordset.MarshalOptions = adMarshalModifiedOnly

    Set m_oRecordset.ActiveConnection = Nothing

    '##count records for testing##
    With m_oRecordset
        j = .RecordCount
        MsgBox j

        '##Set up loop through records##
        For i = 1 To j

            '##collect data from table or query##
            strGetData = !fldFirstName
            strGetData = strGetData & " " & !fldLastName

            'MsgBox strGetData
            .MoveNext
        Next i

    End With

    '##clean up##
    m_oRecordset.Close
    oConnection1.Close

    Set m_oRecordset = Nothing
    Set oConnection1 = Nothing

    Exit Sub

End Sub

help appreciated as usual



Nidge

 

[boblarson]

Why are you wanting to use ADO to do this?

 

[Galaxiom]

The single quote string delimiters in the connection string are not required. This is not like a where clause in a query with a field being compared to a string. The entire line is a string.

If you are connecting to SQL Server 2005 use the SQL Native Client:
Provider=SQLNCLI

For SQL Server 2008 use:
Provider=SQLNCLI10

 

[NigelShaw]

Why are you wanting to use ADO to do this?

Hi Bob,

im connecting to SQL server 2005 through Access and couldnt quite figure out a way to do the same with DAO.

The single quote string delimiters in the connection string are not required. This is not like a where clause in a query with a field being compared to a string. The entire line is a string.

If you are connecting to SQL Server 2005 use the SQL Native Client:
Provider=SQLNCLI

For SQL Server 2008 use:
Provider=SQLNCLI10

i got the connection text straight from Microsoft so didnt actually generate this myself. i had 2 sorts originally but opted for this one in the end.

can my request be achieved?


regs


Nigel

 

[DCrake]

did notice this

Set m_oRecordset.ActiveConnection = Nothing

'##count records for testing##
With m_oRecordset

First you close the connection

Next you want to use the With ActiveConnection

If you have just closed it why are you attempting to use it? Surely you would close the connection after you have finished with it. Or am I missing something?

 

[NigelShaw]

Hi Dave

I see your point but that is how it was set out in the helpfile. It does still collect and display data just as it is.

For the purpose of correct procedure, I'll amend it to close when everything is finished.


Cheers

Nigel

 

[boblarson]

I think you want this (and the binding to a form is what you'll need). I don't know of any way to display an ADO recordset except through a form.

By the way, did you know you can use DAO with SQL Server too?

 

[NigelShaw]

Hi Bob

i didnt know you could use DAO with SQL server? ill be honest, DAO is much easier for me as it is more familiar whereas ADODB is a whole new thing in itself!!

do you know of any examples od where i could look to see about collating the data without having to manually create a link first? i need to make this automated through code so if it was being used on another machine, it could just connect.

its for an internal project that gets distributed to external shops so they can update their wholesale prices/


thanks


Nigel

 

[boblarson]

I'll have to do a search as I don't have it available at the moment (am on the bus going to work).

 

[Banana]

In most simplest terms, you could just link the table to SQL Server and then query it using DAO just in the same manner you had done with any Access tables.

Typically, I use ADO only when I actually need ADO's feature (e.g. wanting to use a stored procedure as a recordsource and still be able to update the form, disconnected recordset, etc.). If you aren't needing anything that's SQL Server-specific then DAO will be much easier and stable.

 

[NigelShaw]

Hi Banana

I don't want to link to SQL through the normal access feature and need to be able to do it purely by coding hence the switch over to adodb. I had originally linked to the SQL server but that takes a certain manual input to create the connection which is fine for me but when we send this out to the other shops who have no experience of doing this, the system stops.

I need to connect to SQL server and collect data pretty much like my code above but ideally in DAO as I'm familiar with that if it is at all achievable


Cheers


Nigel

 

[Banana]

Well, there's basically two different approaches:

1) If your intention is to never show the ODBC prompt, you can ensure that you supply everything required to connect so the user is never prompted to enter in anything. This is easier done with Windows Authentication, but of course, that is not always available. It's also possible with SQL Server Authentication but consideration has to be given toward securing the password.

2) You can just open a new database and reference it in the code.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.OpenDatabase("",,dbDriverNoPrompt,"ODBC;<complete ODBC connection>)

'Open a recordset
Set rs = db.OpenRecordset("<SQL statement or name of table in SQL Server>")

....

You may want to make the db Variable a public or better yet, a property procedure so you don't have to reopen it every time, much like how we always have CurrentDb().

HTH.

 

[NigelShaw]

Hi

Couldn't seem to get the connection working I'm afraid. I found a very similar sample on Microsoft that had OpenDatabase ("publishers") and the line included ODBC;DATABASE=publishers but it wasn't clear how the connection would be made to SQL server as the demos all connect to .mdb

My server is Nigel-laptop-7/CMJ
I have a dns called Iris and the SQL database is called SIrisDb

I can certainly connect no probs with my ado code but using this method seems to restrict the datasheet view.

Regs


Nigel

 

[boblarson]

Again, if you want to use ADO you are free to do so but you will need to bind it to a form for viewing. So you would need to set the form's record source and each of the control's control sources.

I think you need to get a good connection string going. Have you tried looking at the http://www.connectionstrings.com site to find the one for SQL Server 2005 and use that?