For expets in ADO

[Mega_Man]

I have a database created in Access 97 which I use a VB front end to access. I am using the ADOX object with the connection string as jet 3.51. I continuously get the error that the provider does not support the operation I am trying to do.
According to the Microsoft knowledge base, this is because I have to use the jet 4 engine by recreating my users in access 2000.
This is no good because my program opens an instance of access for certain tasks.
Is there another way round this. Can I use the ADOX object with version 3.51 of the jet engine??

Mega.

[kovan]

you can use jet engines 4.0 with access 97
so there shouldnt be ay problems doing so

[Mega_Man]

If that is the case, how do I go about converting my existing access MDB & MDW files to jet 4.
Is there a utility to do this?

Mega.

[Mega_Man]

Please answer..

[kovan]

you dont CONVERT anything
you just use the Jet Engine 4.0 files
in your project
go to
project>references>select ADO 2.5 (2.6) if you have it
then use a jet engine string to opent he connection

[Mega_Man]

I am already using the ADO 2.6 reference but I still get the error.
DO I have to install any files that relate to Jet 4.0 ??

[Mega_Man]

Ok, update.
I have installed the jet 4 files (Service pack 5). I have made sure that all the references are okay in the code.
And yet....... I still get the error.
Someone is going to have to tell me how to resolve this step by step.

I searched the MS knowledge base and it suggests recreating the user MDW file in Access2000. that's ok except I dont have 2000. Another article states that this a bug..
How dam hard is it to find a article that provides any usefull information.

Please help....
Mega.

[JMvVliet]

The solution is very simple.

User in your connection string Microsoft.jet.oledb.4.0 instead of Microsoft.jet.oledb.3.51

Good luck

[Mega_Man]

Sorry but I have already done that.

[marex]

Most database problems are been solved when you
install the latest version of MDAC.

There is a mdac2.7 beta available.
Also use look at your jet 4.0 version.
I'm using jet4.0 SP5 and have no problems.

You can find the downloads on the microsoft site.

cheers
Ray

[Mega_Man]

Ok, so far today, I have installed

Jet 4 (service pack 5)
Mdac 2.7
VB6 runtimes again.

I have checked I am using the right reference (DDL & Security, ADO 2.6 etc...)
And I still get the same message
"Object or provider is not capable or performing requested operation"
I have opened the access database and also added these references to some of the modules I am using to see if that made any difference..

Any more suggestion, This was so easy in DAO...

Incidentally, I am using a database called "Asset.mdb" and a workgroup security file called "Asset.mdw". They are both on a network drive and my connection string looks like this.

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Asset.mdb;user id = " & User & ";" & _
"password=" & Password & ";" & _
"jet oledb:system database=" & App.Path & "\Asset.mdw"

This looks right to me.. yes?
Mega.

[schuurke28]

could you supply the code u use to make your connection?

[Mega_Man]

I have placed the connection string two posts above.
I also tried creating a brand new database and used the example of ADOX from the MSDN. and guess what?....... Yes, I still get the same error
I appreciate the help all you guys are giving but does any one have a sample code that they know defenitely works that they could attach to a post and also tell me exactly what references you have included in your project.
I would be most grateful, I am 100% certain that the problem lies with installed / not installed components but I could make sure for certain if I had working code.

Alternatively or in addition, I am yet again open to suggestions.

Thanks again.
Mega.

[Mega_Man]

I have submitted this again in the hope that I will get an answer today that will help resolve this problem.. I am now all coded out

[Mega_Man]

I am using microsoft activex data objects 2.6
and ADO etx 2.6 for ddl and security.

I tried using the code that you suggested and I still get the same error.
I also tried removing the references and re-adding them using the 2.1 as you suggest and I still get the same message.

Mega.

[parkes]

Could you post the exact error message.

[Mega_Man]

The error is always the same.

Run-time error '3251':
Object or provider is not capable of performing requested operation.

The code I am using is as follows

Dim cat As ADOX.Catalog
Dim usrNew As ADOX.User
Dim usrloop As ADOX.User
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Properties("Jet OLEDB:System database") = "j:\nisdasset\nisdasset.mdw"
conn.ConnectionString = "Data Source=j:\nisdasset\securenisdregister.mdb;" & "User Id=" & User & ";Password=" & Password
conn.Open
Set cat = New ADOX.Catalog
cat.ActiveConnection = conn

Set usrloop = cat.Users(User) <== This is where the error occurs

[parkes]

Weird, its hard to try and fix an error for someone, when I don't get it

I've attached a text file that may provide the reason why you are getting this error, it come from TechNet

[Mega_Man]

Thanks for that, I will have a look and get back to you.

Mega.

[Mega_Man]

The article you sent would seem to suggest that the error is commonplace when using the jet provider.
Unfortunately, it does not provide a resolution to the problem.
Thanks anyway.


Mega.

[Mega_Man]

Ok, I decided to try something else,
I managed to get a hold of Office 2000 and I created a workgroup with some security and the same name.
The connection string and ADOX objects worked this time.
Problem is that I cannot use a Access 2000 MDW file with a Access 97 MDB file and the users who I am writing this for all have access 97.. So, there's a little useless update for you.

Mega.

[Stevie]

I got the same problems when trying this in my last project. Only problem is beacuse the company I'm at is very strict with software on the system, I can't upgrade from MDAC 2.1 or SP3.

So I got round the problem by adding in the DAO library and using DAO just for the security bits, where I wanted to check users and groups etc.

[Mega_Man]

Yes, unfortunately, it looks as though I am going to have to do the same.

btw, what sort of company do you work for. If you are designing software, surely they should have the most up to date available.
Don't they know that service packs are released for a reason?

Mega.

[Stevie]

I work for one2one (mobile phones) but all of the network is handled by ComputerAid, so designing software is a nightmare because I do not have the rights to install stuff on other peoples machines. Installing the exe and such files is ok, but because I have insufficent rights I can't overwrite any DLL's, so to get any software out I have to go through wads of red tape.


ComputerAid also only allow certain versions of things on the network that they have tested thoroughly ie MDAC 2.1 etc.

I actually now have 2.5 on my machine, but if they new that they would not support my machine if it fell over!

It is very restrictive in large companies where the IT is outsourced.

[Mega_Man]

Ahh, I see where you're coming from.
I also work for a large company but as we are in charge of all the I.T. here, I pretty much get to install what I want hehe.

Mega.