Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Location
    Centeral Florida
    Posts
    5

    Unanswered: Newbie to ADO can't get db to open

    I'm trying to convert some old code to ADO, and am having trouble getting the database to open. Here is the code I'm trying to use:

    Dim rcdsrc As String
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    rcdsrc = "C:\Documents and Settings\Owner\My Documents\Area Codes\dbEurope.mdb"

    cnn.Open "Provider=Microsoft.JET.OLEDB.4.0;data source=" + rcdsrc


    Dim rs1 As New ADODB.Recordset
    rs1.Open "tblSpain", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    The code is attached to the click event of a command key. When I click the key, I get the message "3705 - Operation is not allowed when the object is open."

    The database "dbEurope.mdb" is not open. I'm running this from a totally separate database.

    Sure would appreciate some guidance from someone out there.

    Thanks,

    Melbourne684

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

    ADO code

    At a quick glance, after Dim rs1 As New ADODB.Recordset you need a "Set rs1 = new ADODB.Recordset".

    Not sure about the rest though.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If you need more info on connection strings, check this link
    http://www.carlprothman.net/Default....orMicrosoftJet

    I am not a fan of implicit instantiation, and would prefer explicit. Currentproject.connection is a connection to the current project/database so it isn't needed here. Does this work?

    Dim rs1 As ADODB.Recordset
    dim cnn as ADODB.Connection
    set cnn = New ADODB.Connection
    rcdsrc = "C:\Documents and Settings\Owner\My Documents\Area Codes\dbEurope.mdb"
    cnn.Open "Provider=Microsoft.JET.OLEDB.4.0;data source=" + rcdsrc
    set rs1 = New ADODB.Recordset
    rs1.Open "tblSpain", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    Do you need tabledirect? This is usually only needed when using .Seek.
    Roy-Vidar

  4. #4
    Join Date
    Jun 2006
    Location
    Centeral Florida
    Posts
    5

    Ah Ha! It lives...

    Thanks for the response, PKStormy and RoyVidar. I was getting a little bit frustrated with this one.

    Roy's code got this thing running just fine. As far as your questions as to what I need and don't need, I've got a long way to go before I can answer that. I haven't worked with ACCESS, or VB, or any db stuff since 1997, and this is my first week digging back into it. I thought that I may as well try to get somewhat acquainted with ADO, as it seems to be moving all of the older stuff out of the way.

    Many, many thanks for your help!

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by melbourne684
    I thought that I may as well try to get somewhat acquainted with ADO, as it seems to be moving all of the older stuff out of the way.
    I hope not! So far i have not come across a situation where DAO didn't work just dandy for what i wanted to do .....

    tc
    __________________________________________
    Still using DAO and happy with it!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    to tc and all DAOist colleagues out there.
    anyhow, epoch-to-date, there are precisely zero access programmes that don't use DAO.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Erm - ADPs?
    Roy-Vidar

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Roy - i will wash my mouth with beer as punishment.
    probably you are right (but i don't use ADPs so i don't know)
    ...and you could probably have added formless backends, class-provider libraries, and other stuff.

    restatement:
    any .mde or .mdb with a form in it is using DAO.

    izy
    currently using SS 2008R2

Posting Permissions

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