By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Compacting access database problem

P: n/a
Hello, I have written a small programme to compact an access 97
database.
Dim je As New JRO.JetEngine

' Compacts database Data.Mdb to Data2.mdb.
je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\DataOld.Mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\Data.Mdb"

I have tried running the code on a number of different databases and
it works fine. The code as been run in debug mode and as an exe file
without any problems. I then ran the code on another database but it
does not complete and errors up with: You do not have the necessary
permissions to use the '' object. Have your system administrator or
the person who created this object establish the appropriate
permissions for you.
Does anybody know why. Is it possibly to do with security. If I
Compact the database from Access manually it works fine. If I use code
in access and run the code from a makro, when the database with the
code in it opens, it also works successfully. The above code works on
all other databases. I have a feeling that there is some security
issue can anybody shed any light on this problem? Thanks.
Post a follow-up to this message
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 30 Sep 2004 01:13:30 -0700, db*******@hpj.co.uk (lappy) wrote:
Hello, I have written a small programme to compact an access 97
database.
Dim je As New JRO.JetEngine

' Compacts database Data.Mdb to Data2.mdb.
je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\DataOld.Mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\Data.Mdb"

I have tried running the code on a number of different databases and
it works fine. The code as been run in debug mode and as an exe file
without any problems. I then ran the code on another database but it
does not complete and errors up with: You do not have the necessary
permissions to use the '' object. Have your system administrator or
the person who created this object establish the appropriate
permissions for you.
Does anybody know why. Is it possibly to do with security. If I
Compact the database from Access manually it works fine. If I use code
in access and run the code from a makro, when the database with the
code in it opens, it also works successfully. The above code works on
all other databases. I have a feeling that there is some security
issue can anybody shed any light on this problem? Thanks.

Hi
I assume this is from VB. As this is an Access 97 you are compacting
you could try
DBEngine.CompactDatabase "c:\myDir\db1.mdb", "c:\myDir\db2.mdb"

with reference set for DAO 3.6 for example. At least this should give
you a better error message.

You could also check compacting the offending database from the
command line.

It's true you are not allowed to compact a database if it is secured
and you don't have the correct permissions.
David

Nov 13 '05 #2

P: n/a
d.***************@blueyonder.co.uk (David Schofield) wrote in message news:<415c8c45.612914414@localhost>...
On 30 Sep 2004 01:13:30 -0700, db*******@hpj.co.uk (lappy) wrote:
Hello, I have written a small programme to compact an access 97
database.
Dim je As New JRO.JetEngine

' Compacts database Data.Mdb to Data2.mdb.
je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\DataOld.Mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Dump\Data.Mdb"

I have tried running the code on a number of different databases and
it works fine. The code as been run in debug mode and as an exe file
without any problems. I then ran the code on another database but it
does not complete and errors up with: You do not have the necessary
permissions to use the '' object. Have your system administrator or
the person who created this object establish the appropriate
permissions for you.
Does anybody know why. Is it possibly to do with security. If I
Compact the database from Access manually it works fine. If I use code
in access and run the code from a makro, when the database with the
code in it opens, it also works successfully. The above code works on
all other databases. I have a feeling that there is some security
issue can anybody shed any light on this problem? Thanks.

Hi
I assume this is from VB. As this is an Access 97 you are compacting
you could try
DBEngine.CompactDatabase "c:\myDir\db1.mdb", "c:\myDir\db2.mdb"

with reference set for DAO 3.6 for example. At least this should give
you a better error message.

You could also check compacting the offending database from the
command line.

It's true you are not allowed to compact a database if it is secured
and you don't have the correct permissions.
David


Do you know the syntax for the permissions. I know the database has
user admin and no password. How would I put this into my code?
Nov 13 '05 #3

P: n/a
On 1 Oct 2004 08:53:46 -0700, db*******@hpj.co.uk (lappy) wrote:
d.***************@blueyonder.co.uk (David Schofield) wrote in message news:<415c8c45.612914414@localhost>...
On 30 Sep 2004 01:13:30 -0700, db*******@hpj.co.uk (lappy) wrote:
>Hello, I have written a small programme to compact an access 97
>database.
> Dim je As New JRO.JetEngine
>
> ' Compacts database Data.Mdb to Data2.mdb.
> je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
>Source=C:\Dump\DataOld.Mdb", _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
>Source=C:\Dump\Data.Mdb"
>
>I have tried running the code on a number of different databases and
>it works fine. The code as been run in debug mode and as an exe file
>without any problems. I then ran the code on another database but it
>does not complete and errors up with: You do not have the necessary
>permissions to use the '' object. Have your system administrator or
>the person who created this object establish the appropriate
>permissions for you.
>Does anybody know why. Is it possibly to do with security. If I
>Compact the database from Access manually it works fine. If I use code
>in access and run the code from a makro, when the database with the
>code in it opens, it also works successfully. The above code works on
>all other databases. I have a feeling that there is some security
>issue can anybody shed any light on this problem? Thanks.

Hi
I assume this is from VB. As this is an Access 97 you are compacting
you could try
DBEngine.CompactDatabase "c:\myDir\db1.mdb", "c:\myDir\db2.mdb"

with reference set for DAO 3.6 for example. At least this should give
you a better error message.

You could also check compacting the offending database from the
command line.

It's true you are not allowed to compact a database if it is secured
and you don't have the correct permissions.
David


Do you know the syntax for the permissions. I know the database has
user admin and no password. How would I put this into my code?


Hi
Open the offending database. You can see if there is a security
barrier by using Tools: Security: User and Group permissions. On the
permissions tab select the Groups radio button and for each
object-type in turn set Administer permission. If you can do all this
there is no security barrier and the compact should work. (actually I
am not sure now without checking that security does prevent
compacting).

Worth trying is the following: create a new database and import
everything from the problem database into it. See if your compacting
code works on the copy. (Access 97 databases can accumulate odd
intenal errors which repair doesn't cure while still working
apparently normally.)

If security is the problem you need to find the right workgroup
(.mdw) file - one which contains a user with the right permissions -
and then know the username and password. I can look up the syntax if
necessary
HTH
David
Nov 13 '05 #4

P: n/a
d.***************@blueyonder.co.uk (David Schofield) wrote in message news:<415d8f44.679217533@localhost>...
On 1 Oct 2004 08:53:46 -0700, db*******@hpj.co.uk (lappy) wrote:
d.***************@blueyonder.co.uk (David Schofield) wrote in message news:<415c8c45.612914414@localhost>...
On 30 Sep 2004 01:13:30 -0700, db*******@hpj.co.uk (lappy) wrote:

>Hello, I have written a small programme to compact an access 97
>database.
> Dim je As New JRO.JetEngine
>
> ' Compacts database Data.Mdb to Data2.mdb.
> je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
>Source=C:\Dump\DataOld.Mdb", _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
>Source=C:\Dump\Data.Mdb"
>
>I have tried running the code on a number of different databases and
>it works fine. The code as been run in debug mode and as an exe file
>without any problems. I then ran the code on another database but it
>does not complete and errors up with: You do not have the necessary
>permissions to use the '' object. Have your system administrator or
>the person who created this object establish the appropriate
>permissions for you.
>Does anybody know why. Is it possibly to do with security. If I
>Compact the database from Access manually it works fine. If I use code
>in access and run the code from a makro, when the database with the
>code in it opens, it also works successfully. The above code works on
>all other databases. I have a feeling that there is some security
>issue can anybody shed any light on this problem? Thanks.
Hi
I assume this is from VB. As this is an Access 97 you are compacting
you could try
DBEngine.CompactDatabase "c:\myDir\db1.mdb", "c:\myDir\db2.mdb"

with reference set for DAO 3.6 for example. At least this should give
you a better error message.

You could also check compacting the offending database from the
command line.

It's true you are not allowed to compact a database if it is secured
and you don't have the correct permissions.
David


Do you know the syntax for the permissions. I know the database has
user admin and no password. How would I put this into my code?


Hi
Open the offending database. You can see if there is a security
barrier by using Tools: Security: User and Group permissions. On the
permissions tab select the Groups radio button and for each
object-type in turn set Administer permission. If you can do all this
there is no security barrier and the compact should work. (actually I
am not sure now without checking that security does prevent
compacting).

Worth trying is the following: create a new database and import
everything from the problem database into it. See if your compacting
code works on the copy. (Access 97 databases can accumulate odd
intenal errors which repair doesn't cure while still working
apparently normally.)

If security is the problem you need to find the right workgroup
(.mdw) file - one which contains a user with the right permissions -
and then know the username and password. I can look up the syntax if
necessary
HTH
David


Not quite sure what you mean by type in set Administer permission for
each object type. If I do what you said there is nothing to type into.
Nov 13 '05 #5

P: n/a
On 4 Oct 2004 02:56:59 -0700, db*******@hpj.co.uk (lappy) wrote:

Not quite sure what you mean by type in set Administer permission for
each object type. If I do what you said there is nothing to type into.


Hi
This was just to see if security was a problem. I am assuming you are
using full Access 97 and not the run time system

Top menu item "Tools"
Subitem "Security"
Sub-sub item:"User and Group Permissions"

On the permissions tab is a list of user or group names, the form
opens showing user names.The list normally includes Admin which is the
default user.

See what permissions are ticked, and if not all are, tick the
administer one. If you can do this, there is no security to worry
about.

(Strictly you should check this for each object type in the combo box,
not just for tables)
HTH
David

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.