Dim cnt As New ADODB.Connection

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,421
Office Version
2019, 2016, 2013
Platform
Windows
Up until two days ago this was working

Code:
Dim cnt    As New ADODB.Connection
work has been doing changes over the WAN moving all users from 2003 to 2007, and although I have a dual install i suspect that some of the changes are removing files from paths that still exist.

I cannot compile any longer this statement without getting
Compile error:
User-Defined Type Not Defined

The libraries look ok, a full shut down and restart hasn't cleared the problem.

Previously compiled and the code still works.

When I retype in 2007 I get to "Dim cnt As New" then ADODB is not an offered option, frankly i'm stuck.

Any other way that this could be written to retrieve data from an SQL database, speed isn't an object, just trying to work around our techs, until they understand what they are doing

I've searched the forum but not found an answer
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Might be a missing extension causing a problem with early binding. You could try late binding
Code:
dim cnt as variant
Set cnt = CreateObject("ADODB.Connection")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,732
Office Version
365
Platform
Windows
You say the 'libraries look ok'?

What do you mean exactly and how did you check?

Are you working with 2 versions of the file, one 2003 and one 2007?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,421
Office Version
2019, 2016, 2013
Platform
Windows
Might be a missing extension causing a problem with early binding. You could try late binding
Code:
dim cnt as variant
Set cnt = CreateObject("ADODB.Connection")

Thanks i'll give that a go Monday when I have access to the data source.

I'm guessing there are always other ways of writing these, just no apparent site that does a comparison that I can follow
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,421
Office Version
2019, 2016, 2013
Platform
Windows

ADVERTISEMENT

You say the 'libraries look ok'?

What do you mean exactly and how did you check?

Are you working with 2 versions of the file, one 2003 and one 2007?
Tools > References

Visual Basic for Applications
Microsoft Excel Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Activex Data Objects

(this is the home system, and work appears similar)
Can't add anything at work, no item is shown as missing

So I have to be creative, if one way stops working then find another way

files created as .xls, and becoming familiar with .xlsm as necessary for other work. (Though I should have two full installs of each programme, so currently I shouldn't be getting any issues as i have run them together for six months), yet they have only started migrating everybody in the last week
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
I'd be curious how you've checked libraries too and what makes you sure they are okay. Normally, if you have set a reference to the ADO library there wouldn't be this problem.

That said, there are arguments for late binding and maybe that's best anyway.

Nevermind - saw your post above. Though obviously your home system is not your work system. It may be worth looking again ... the error says you don't have the ADO library referenced.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,732
Office Version
365
Platform
Windows
mole

As xenou has said there are arguments for and against late-binding.

One of the advantages of late-binding is that if you do find yourself working with different versions then you shouldn't need to worry about references.

A disadvantage is that you don't have access to Intellisense, object libraries (which include constants etc)...

One way to work is to develop in early binding and then change to late binding later when distributing or whatever.

Might be an extra bit of work but could also help avoid quite a bit of grief.:)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,421
Office Version
2019, 2016, 2013
Platform
Windows
mole

As xenou has said there are arguments for and against late-binding.

One of the advantages of late-binding is that if you do find yourself working with different versions then you shouldn't need to worry about references.

A disadvantage is that you don't have access to Intellisense, object libraries (which include constants etc)...

One way to work is to develop in early binding and then change to late binding later when distributing or whatever.

Might be an extra bit of work but could also help avoid quite a bit of grief.:)
always willing to learn new techniques, maybe my word searches are wrong so i cannot home into information that would help describe.

I learn best from examples, and then can see that although different they end with a similar result
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,132
Messages
5,509,344
Members
408,727
Latest member
Cantello

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top