ADODB code problem converting to Access 2007

[Georgios Liakopoulos]

Hello everyone,
I converted Access 2003 mdb file to accdb successfully. But then, when I
run the following code (it runs when entering a search form) I get a
'Run-time error ... Object invalid or no longer set' message. This was
not happening in Access 2003. Can anyone help?

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cmdsearchsubform = New ADODB.Command
SQLString = "SELECT * FROM FullQuery;" '----full query is a overview
query of fields from different
tables---
cmdsearchsubform.CommandText = SQLString
viewname = "viewsearchsubform" & usercode '---different users can run
their own searches----
*cat.Views.Append viewname, cmdsearchsubform '---code to create the
query---
cat.Views.Refresh

The debugger stops in the line with the asterisk (off course there is no
asterisk in the code!)

Thanks for reading

 

[David-W-Fenton]

Hello everyone,
I converted Access 2003 mdb file to accdb successfully. But then,
when I run the following code (it runs when entering a search
form) I get a 'Run-time error ... Object invalid or no longer set'
message. This was not happening in Access 2003. Can anyone help?

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cmdsearchsubform = New ADODB.Command
SQLString = "SELECT * FROM FullQuery;" '----full query is a
overview
query of fields from different
tables---
cmdsearchsubform.CommandText = SQLString
viewname = "viewsearchsubform" & usercode '---different users can
run
their own searches----
*cat.Views.Append viewname, cmdsearchsubform '---code to create
the
query---
cat.Views.Refresh

The debugger stops in the line with the asterisk (off course there
is no asterisk in the code!)

Why are you using ADO to create a saved QueryDef? This is an obvious
case where DAO is going to be vastly superior, seems to me.

It's certainly a helluva lot less convoluted code.

 

[Georgios Liakopoulos]

Why are you using ADO to create a saved QueryDef? This is an obvious
case where DAO is going to be vastly superior, seems to me.

It's certainly a helluva lot less convoluted code.

Thank you David for your reply,
Yes, I could do it with DAO but isn't ADO more powerful, modern etc.?
Isn't worth it to use ADO instead?

 

[John W. Vinson]

Yes, I could do it with DAO but isn't ADO more powerful, modern etc.?

That was the original idea but it didn't work out. Microsoft is now
recommending DAO instead.

Isn't worth it to use ADO instead?

No. ADO is *dead*. DAO is more powerful, better integrated with Access,
simpler to use, and the option recommended by Microsoft.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

Yes, I could do it with DAO but isn't ADO more powerful, modern
etc.? Isn't worth it to use ADO instead?

No, ADO is not "more powerful, modern etc." ADO is no longer in
development, as it's been replaced on MS's agenda by ADO.NET, which,
despite the similar name (and some common syntax), is a completely
different animal, and not usable in Access applications.

With a Jet/ACE back end (MDB/ACCDB), ADO is going through extra
layers to work with Jet/ACE, whereas DAO is Jet/ACE's native data
interface. Likewise, there are features of Jet/ACE not supported in
ADO (because ADO is a generic data interface layer, like ODBC on
steroids), and then you need to use additional libraries. For
instance, to compact a database, you have to use JRO, because ADO
has no support for that.

DAO is in current development, being kept up-to-date with the new
versions of ACE (and backwardly compatible with Jet 4 and before).
Classic ADO (which is all Access can use) is never ever going to be
updated again. It's dead as a doornail.

And it was never faster.

It was also never the better choice for Jet, despite what Microsoft
implied. The push for ADO in Access by MS was made on political not
technical grounds. The reason MS wanted ADO was so it could retire
DAO and Jet. But they realized after they attempted this that it
just didn't work very well, and backtracked and eventually gave
Access it's own private version of Jet (with A2007, now called ACE)
that would be developed to meet the needs of Access and its larger
ends (which include close integration between Access and Sharepoint,
which has driven a lot of the new features in the database engine,
not all of which are particularly useful by themselves).

In short, the idea that ADO is better or the future is about 10
years out of date.