Dim cnt As New ADODB.Connection

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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
76,295
Office Version
  1. 365
Platform
  1. 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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
76,295
Office Version
  1. 365
Platform
  1. 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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,872
Messages
5,655,719
Members
418,233
Latest member
hussaind

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top