Currentdb without using 2.5/3.5 ADO Library

All users are currently using Microsft 2.5/3.5 ADO Library.  It doesn't exist on one computer, the developement computer.  As I understand it 2.5/3.5 was replaced by Microsft ADO Library 3.6.  When I check 3.6, the following code doesn't work anymore.

Dim rs As Recordset, db As Database
   Dim X
   Set db = CurrentDb()

How do I resolve this?  I really don't want to rewrite the code to update to 3.6.  The application is massive
GraeperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Locate the Microsoft DAO 3.X reference and check off that.

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Think your getting DAO and ADO mixed up.  Check the references carefully.  You need the DAO lib with that code as MX said.

JimD.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
My guess that you are getting Compile errors ... as you will if you do not have the DAO reference ....with code such as

Dim rs As Recordset, db As Database
   Dim X
   Set db = CurrentDb()

Sadly ... ADO is the default reference when you open/create a new MDB.  DAO is NOT checked by Default.  Bad, bad bad idea on M$ part ...

mx
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TextReportCommented:
You need the Microsoft DAO Library to use the Dim db As Database, you can use 3.5 or earlier but 3.6 is the latest and has been around for ages.
Cheers, Andrew
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<ADO is the default reference when you open/create a new MDB>

Only in 2000 and 2002 ... this was changed in 2003 and both files were referenced, with DAO being listed "higher" on the list.
0
TextReportCommented:
LSMConsulting, that is very ggod to know. Thanks
Graeper, you can use CurrentDB() without having the DAO reference, however, I suspect you will need it for other things.
Cheers, Andrew
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
wow ... I just NOW noticed that in A2003 !!  thx Scott.

0
BadotzCommented:
I'm using XS2003, and this command:

Set rs_temp = CurrentProject.Connection.Execute("select * from vendor")

I do not need to fuss with connections, but am clueless if this is good/bad/slower/faster than explicit variables.
0
GraeperAuthor Commented:
Sorry about the DAO, ADO mixup.  Andrew @ TextReport suggested using 3.6, but I CAN'T use

db = Currentdb()

It works in 3.5 but not 3.6.  I don't have a clue as to how to adjust it, and it occurs thousands of times in the application.
0
TextReportCommented:
You have to SET and object not just assign a value
Set db = Currentdb()

Cheers, Andrew
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your 97 application shouldn't even have a reference to ADO, unless someone explicitly went into the app and made that reference.

As JimD said earlier: Check your references and see if DAO is checked, or if it's listed as MISSING ...

<Set rs_temp = CurrentProject.Connection.Execute("select * from vendor")>

Setting a Recordset in this manner will result in a readonly, forwardonly recordset ... this may be what you want, but in order to provide more functionality from the Recordset you'd need to Set the recordset, and set the Cursor and Lock type (along with any other options needed). From my standpoint, the Execute method should be used for Action queries (INSERT, UPDATE etc) and not to return a Recordset ... but that's just my .02 worth!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
How about just two lines of code:

Dim X
Set X = CurrentDb.OpenRecordset("select * from vendor", dbopendynaset)

mx
0
Rey Obrero (Capricorn1)Commented:

< It doesn't exist on one computer, the developement computer>

what version of access is this computer using?

if it is a2000 and newer just make sure your references
if DAO and ADO are required are in this hierarchy


Microsoft DAO                <--- should be above the ADO
Microsoft ActiveX Data Objects
0
GraeperAuthor Commented:
Using the two lines from Databasemx produces an error
"Automation type not support in Visual Basic" and highlights Currentdb as the problem.
Currentdb isn't supported by Microsoft DAO 3.6 Library and I'm still trying to find a solution

FYI I'm using Access 97 on an XP machine, and have a "MISSING" Microsoft 2.5/3.5 DAO Library on my development computer
0
TextReportCommented:
If you have a reference MISSING that is ticked then it will cause problems.
What DAO options do you have on the problem PC?
Cheers, Andrew
0
TextReportCommented:
OK just installed Office 95 then installed Offince 97, that will teach me for buying an upgrade.
Access 97 comes with DAO 3.5. The MISSING library is the compatability library (not sure which version this came with) and you should be able to change it to Microsoft DAO 3.5 Object Library and the DIM will then work.

CurrentDB is not part of DAO and without a DAO reference MsgBox CurrentDB.Name does work, please note the MISSING reference could stop this from working though.

Dim db as DAO.Database requires a reference to a DAO library.

Cheers, Andrew
0
GraeperAuthor Commented:
I think that's the answer.  I found a reference to DAO 3.5 in the \Program Files\Common Files\Microsoft Shared\\DAO\DAO350.dll, but can't figure out how to reference it since it's not on my reference list.  If I can hook into this file for referece purposes, you are the winner!
0
Rey Obrero (Capricorn1)Commented:
just helping...

tools >references

click browse and locate
\Program Files\Common Files\Microsoft Shared\\DAO\DAO350.dll


click OK
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TextReportCommented:
This is in Access 97? If so if it is selected then it should be at the top if not is should be in alphabetical order after the selected ones and below Microsoft ADO.
Cheers, Andrew
0
BadotzCommented:
@LSMConsulting:

>>Setting a Recordset in this manner will result in a readonly, forwardonly recordset

Perfect, thanks.
0
GraeperAuthor Commented:
I can't believe it was that easy.  I did exactly what you said.  I went into Tool/References and then Browse to C:\\Program Files\Common Files\Microsoft Shared\DAO and there it was.  I unchecked the 3.6 DAO Library and clicked OK for the browsed file and it appeared on the list.  Problem solved.  I am very grateful.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Locate the Microsoft DAO 3.X reference and check off that."

:-)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.