Dim cnt As New ADODB.Connection

[mole999]

Up until two days ago this was working

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

 

[Weaver]

Might be a missing extension causing a problem with early binding. You could try late binding

dim cnt as variant
Set cnt = CreateObject("ADODB.Connection")

 

[Norie]

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]

Might be a missing extension causing a problem with early binding. You could try late binding

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]

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]

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.

 

[Norie]

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]

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