Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Unanswered: Problem connecting to database with ADO

    OK, I am stumped and would appreciate any help that can be given.

    I have an excel file that I use to add and view case notes from a database. The Excel worksheet is sort of the front application, while the database file holds the information.

    So, I originally built it using DAO and it worked fine. The database file is an Access 2003 mdb file. I wanted to try using ADO though, because most of the projects I inherited use it and I figure it's time to learn it.

    So, here's the problem.

    I built the code like this:

    Sub Retrieve_CN_New()
    Dim strSQL as string
    Dim dbconn as string
    Dim cn as ADODB.Connection
    Dim rst as ADODB.Recordset

    dbconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\work\mydb.mdb"
    strSQL = "SELECT Entered_By, Acct_Nbr, Note_date, Note_Type, Case_Note " & _
    "FROM Case_Notes " & _
    "WHERE ((Acct_Nbr = '" & Range("C5") & "')) " & _
    "ORDER BY Note_date DESC;"

    cn.open dbconn

    rst.open (strSQL), cn

    Range("A6").copyfromrecordset rst

    End sub()


    Well, I get to cn.open dbconn and I get this error:

    Run-time error '91':

    Object variable or With block variable not set

    I have checked several wesbites, my Excel 2003 programming book, and microsofts knowledge base and can't find the problem. The syntax looks correct to me. I even tried a couple of different ones that used Microsoft Accesss instead of the OLEDB.

    In the References I have:
    Visual Basic For Applications
    Microsoft Excel 11.0 Object Library
    OLE Automation
    Microsoft Office 11.0 Object Library
    Microsoft Forms 2.0 Object Library
    Microsoft Calendar Control 11.0
    Microsoft ActiveX Data Objects 2.8 Libarary

    I tried the ADO 2.7 library as well. I added the three OLE references that were available.

    I just can't seem to get it to work.

    Any help would GREATLY be appreciated.

    Also, the initail problem I was trying to solve is this, when I append the databse with the sql statement, it causes and error if the user uses an apostrophe in the note. I know DAO can't handle an apostrophe, I figured maybe ADO could. If not, any suggestions (other than telling my users not use apostrophes)?

    Thanks

    Nate

  2. #2
    Join Date
    Feb 2006
    Posts
    4
    Solved it myself, I was missing these two lines:

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset

Posting Permissions

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