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:
Dim strSQL as string
Dim dbconn as string
Dim cn as ADODB.Connection
Dim rst as ADODB.Recordset
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
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)?