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

Solved it myself, I was missing these two lines:

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