Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Unanswered: ADO connection strings; Using SQL statements etc.

    Hi,

    I'm searching my way around in Access, did the same for ASP a few months ago, and I got things working, but I have the annoying habit of wanting to know EXACTLY what is happening when executing some code

    Problem with ASP and now more or less the same with VBA (in Access) is the connection to a DB. At the moment, I am using this piece of simple code in a Access-module to look up a simple record in a simple database:

    Public Function MyLookUp(strField As String, strTable As String, strWhere As String) As String

    Dim dbcurr As Database
    Set dbcurr = CurrentDb

    Dim rsRecords As Recordset
    Set rsRecords = dbcurr.OpenRecordset(strTable, dbOpenDynaset)

    rsRecords.FindFirst ("BA_woonplaats = '" & strWhere & "'")

    If Not rsRecords.NoMatch Then
    MyLookUp = rsRecords.Fields(strField).Value
    Else: MyLookUp = ""
    MsgBox ("No match found in database")
    End If


    End Function
    However, I can't use SQL-statements in this way, as I understood, but only the built-in VBA-commands like findfirst etc. Now, I CAN find some code similar to the "Set rsRecords = dbcurr.OpenRecordset(strTable, dbOpenDynaset)"-bit that should work with SQL, but the problem is: I have no clue what I'm doing. I can't really grasp the concept of the dbCurr as database and dbCurr.openRecordset and all that. What's HAPPENING when I do that? Does someone maybe have a real good beginners tutorial or some lines of code with explanation about this? In ASP, I too just copy-pasted some code which accesses an Access-DB, without REALLY knowing what was happening (but after 2 days of trying to understand, you're happy enough that the thing actually WORKS )

    I can find ADO-tutorials too, but theyre mostly for ASP-use. What I need is an explanation, line by line, whats happening in the code when I make a connection to a DB... Yeh I know, I making things difficult for myself :P

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the code you posted is DAO, not ADO.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    the code you posted is DAO, not ADO.

    izy
    Oops!!!! Some days you get the easy ones ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by izyrider
    the code you posted is DAO, not ADO.

    izy
    I know, read my post again: This code works, but I can't use SQL-statements in this code (apparently because I need ADO for this).

    How do I make ADO and SQL work in Access-VBA?
    I can find all sorts of tutorials on all items, but nothing much that puts them together.

    Let me rephrase it: I want to use SQL-statements in an Access-module to retrieve data from the db, I understood one needs to use ADO for this.

    1. What exactly is ADO?
    2. What is the code needed to 'run' SQL in an Access-Module?
    3. Most importantly: Explain the code: What does it do, line by line?

    But I'm happy with step 1 & 2 atm, since I don't have that working yet.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by avlan
    I know, read my post again: This code works, but I can't use SQL-statements in this code (apparently because I need ADO for this).

    How do I make ADO and SQL work in Access-VBA?
    I can find all sorts of tutorials on all items, but nothing much that puts them together.

    Let me rephrase it: I want to use SQL-statements in an Access-module to retrieve data from the db, I understood one needs to use ADO for this.

    1. What exactly is ADO?
    2. What is the code needed to 'run' SQL in an Access-Module?
    3. Most importantly: Explain the code: What does it do, line by line?

    But I'm happy with step 1 & 2 atm, since I don't have that working yet.
    ADO : ActiveX Data Object

    Plenty of code posted here using ADO ... Try searching ... (Hint: I've posted code ...) As for #3: Get a book or open up the help pages on ADO ...

    As for your assertion of NOT being able to use SQL in DAO is patently false (even in your routine). The question is how are you gonna generate it?

    P.S. - Just declare a local string to assign the SQL to and then you can run it ... Ex: "SELECT * FROM SomeTable;"
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Yeh sorry, I was a bit blunt in my reply, thx for advice..

    It said somewhere that (complex?) SQL-statements could not be used in the posted code? :| If I can do anything in DOA, why use ADO?

    I've read about ADO, I kind of understand it, but what I miss is the connection VBA - ADO in Access, how/why to do it. Help-files (especially in windows) tend to describe the term in a useless way, the help-file for a car would be "It has wheels and can contain up to 5 people", which doesnt help me at all in understanding what a car does and why on earth I should use it when I have a bike and could go by train... If you understand what I'm getting at :P

    I'll search the forum for ADO, thx

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by avlan
    Yeh sorry, I was a bit blunt in my reply, thx for advice..

    It said somewhere that (complex?) SQL-statements could not be used in the posted code? :| If I can do anything in DOA, why use ADO?

    I've read about ADO, I kind of understand it, but what I miss is the connection VBA - ADO in Access, how/why to do it. Help-files (especially in windows) tend to describe the term in a useless way, the help-file for a car would be "It has wheels and can contain up to 5 people", which doesnt help me at all in understanding what a car does and why on earth I should use it when I have a bike and could go by train... If you understand what I'm getting at :P

    I'll search the forum for ADO, thx
    DOA? Dead-On-Arrival ... That does sum up some of MS's stinkers ... As for why: eventually (MS has been promising for ages ... but we'll see) DAO is gonna die ... You'll not be able to use it.
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Haha thats the only reason? Lame
    So they kind of do the same, DOA and ADO, make some sort of connection to a db..

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by avlan
    Haha thats the only reason? Lame
    So they kind of do the same, DOA and ADO, make some sort of connection to a db..
    That is exactly correct. They use different protocols and technology to do it ... MS recommends migrating to ADO ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Mmm I always found the need for ODBC-connections (which is another sort of DB-connection I understand) odd... I'm like, the DB is in C:\temp\databases, this is what it's called, we're all friends here, lets access it with a statement like "get from database @ c:\temp\databases all records where field = '5'"...

    But thats probably to simple

    Right, ADO it is then, I'm not wasting any more time on DOA.. Anyone know universal (i.e. always working) connection-strings to a db I can copy-paste into my module?

    And... I'm confused: ODBC, OLE DB, ADO, DOA... Whats what why how huh?

  11. #11
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Right...

    ADO is really just a wrapper for Microsoft's OLE/DB (OLE for Databases) interfaces. OLE/DB is a set of more complicated (but higher performance) interfaces that were introduced before ADO. OLE/DB was meant to be a wholesale replacement for ODBC, but it was complex as well. To answer this critism, Microsoft developed ADO which is a good marriage of ease of use and performance. ADO support runtime binding to columns, but at the cost of multiple round-trips to the database and no strong typing of column types. (...)
    The most confusing part of the differences between ODBC and ADO is that most often, users of ADO use ODBC to connect to their database. Huh? That's right. OLE/DB (which ADO wraps) has the concept of a provider/consumer. ADO consumes (and is therefore the consumer of data) OLE/DB providers. But Microsoft didn't want to ship OLE/DB without losing programmers, so they wrote an ODBC provider for ADO. So when someone uses the ODBC provider to access their data through ADO, their performance suffers. It suffers because your code talks to ADO, which then talks to OLE/DB which then talks to ODBC, who then finally gets to talk to the database. By using a non-ODBC provider you can reduce the layers of abstraction and since the providers have been written for more recent versions of the database, usually the performance is much better overall.
    Gotta love MS :|

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try typing
    ?currentproject.Connection
    in the immediate panel of a code window

    it is doubtfull that anyone can give you a fully working proven connection string. because the coinnection is dependent on what data back end you are using, what security and other settings

  13. #13
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    DAO is going to die - yup - heard it since the release of Access 2000, but think about it - in versions 2000 and 2002, DAO was not among default libraries when starting a new database, to use DAO, one needed to check the reference manually. But - in 2003 - it's back again...

    Form recordset in access mdbs, all versions are DAO - so some DAO programming is still needed, unless one explicitly assigns ADO recordsets (which, well, in lack of better words, a hassle in 2000) APD's default to ADO form recordsets.

    My guess - DAO will die at the same time as ADO - where both libraries are replaced with ADO.Net, yet another completely different animal.

    When working exclusivly with Access tables, I think DAO is still the fastest, and certain properties native to Access is of course only available through DAO, not ADO. This is probably one of the reasons several really good Access developers have never bothered converting to ADO (I'm not among them, neither am I that good, and I did convert to ADO some time ago, probably for all the wrong reasons)

    For some more connection strings, check out
    http://www.able-consulting.com/ADO_Conn.htm

    as pointed out by others:

    dim strsql as string
    dim rs1 as dao.recordset
    dim rs2 as adodb.recordset
    dim db as dao.database
    dim cn as connection

    strsql = "select * from sometable where somefield = somevalue"
    set db=currentdb
    set rs1=db.openrecordset(strsql)

    set rs2=new adodb.recordset
    with rs2
    .activeconnection=currentproject.connection
    .locktype = adLockOptimistic
    .cursortype = adopenkeyset
    .open strsql,,,,adcmdtext
    end with

    should open a recordset based on dynamic sql
    Last edited by RoyVidar; 02-01-05 at 14:40. Reason: hmpf typos...
    Roy-Vidar

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Connecting via ODBC

    You can try something like this (using ADO):

    Function GetCustomerViaADOAndDSN(PKID As Variant)
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Open "DSN=MyCustomerDSN", "sa", ""
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from MyCustomerTable where PKCustomerID = """ & PKID & """"
    rs.ActiveConnection = cnn
    rs.Source = strSQL
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockReadOnly
    rs.Open
    If rs.EOF And rs.BOF Then
    MsgBox ("Customer not found.")
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    Else
    Forms!CustomerForm!PKCustomerID = rs!PKCustomerID
    'Return other rs fields to form here.
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    End If
    End Function
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by RoyVidar

    dim strsql as string
    dim rs1 as dao.recordset
    dim rs2 as adodb.recordset
    dim db as dao.database
    dim cn as connection
    I just read in some other thread it's not wise to use ADO and DOA together, but it seems that's what happens here? Any reason?

    And what does "dim rs2 as adodb.recordset" do? It declares rs2 as a recordset which uses ADO? What does that mean, if it 'uses' ADO? ADO is an Object, what does this object do? How does it work? Is it an 'object' that translates VBA-talk to DB-talk? Why can't I talk DB-talk directly then, isn't that much quicker? And if ADO is a 'translate-Object', then why do I have to declare rs2 as an 'ADO'-recordset? Isn't it a recordset declared in VBA, so its a VBA-recordset, that gets its data from the DB via ADO?

    That kind of fundamental questions I'm thinking about

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •