Delete table permissions

  • Thread starter Thread starter Joey
  • Start date Start date
J

Joey

Hi. I'm trying to set up a query that will delete my
import error tables. I've managed to find the tables,
using the following SQL:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE ( [Type] = 1 AND ([Name] Like "*ImportErrors*"));

My problem now, is getting the tables to delete from
MsysObjects. I have the following SQL written for that:

DELETE TABLE
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*ImportErrors*") AND
((MsysObjects.Type)=1))
WITH OWNERACCESS OPTION;

Running the above SQL gives me an error saying I don't
have delete permission on MsysObjects. I am the creator
of the database, so I'm confused as to whether the problem
is with my syntax or my permissions. Any help would be
appreciated.

Thanks!
 
Why are you trying to manipulate data in the MsysObjects table? That is a
system table and you should NOT be attempting to do that. Simply delete the
Import Errors table through the user interface.
 
You should not (can not) be messing with the MSys objects. Instead use the
delete method on the TableDefs collection. There's an example in Help.

CurrentDB.TableDefs.Delete "tablename"
 
I'm trying to put together a macro that imports files,
appends them to tables, and then deletes the import error
tables. I haven't been able to get around the import
errors themselves, so I thought I could at least automate
the process of deleting them.

Any suggestions on how else I should do this? I need to
distribute this database to other users, and I'm afraid
they will not remember to delete the tables after every
import.

Thanks.
-----Original Message-----
Why are you trying to manipulate data in the MsysObjects table? That is a
system table and you should NOT be attempting to do that. Simply delete the
Import Errors table through the user interface.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi. I'm trying to set up a query that will delete my
import error tables. I've managed to find the tables,
using the following SQL:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE ( [Type] = 1 AND ([Name] Like "*ImportErrors*"));

My problem now, is getting the tables to delete from
MsysObjects. I have the following SQL written for that:

DELETE TABLE
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*ImportErrors*") AND
((MsysObjects.Type)=1))
WITH OWNERACCESS OPTION;

Running the above SQL gives me an error saying I don't
have delete permission on MsysObjects. I am the creator
of the database, so I'm confused as to whether the problem
is with my syntax or my permissions. Any help would be
appreciated.

Thanks!
Click to expand...


.
Click to expand...
 
Either follow Joan Wild's suggestion or, else, create a DeletObject Macro to
delete the table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Joey said:
I'm trying to put together a macro that imports files,
appends them to tables, and then deletes the import error
tables. I haven't been able to get around the import
errors themselves, so I thought I could at least automate
the process of deleting them.

Any suggestions on how else I should do this? I need to
distribute this database to other users, and I'm afraid
they will not remember to delete the tables after every
import.

Thanks.
-----Original Message-----
Why are you trying to manipulate data in the MsysObjects table? That is a
system table and you should NOT be attempting to do that. Simply delete the
Import Errors table through the user interface.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi. I'm trying to set up a query that will delete my
import error tables. I've managed to find the tables,
using the following SQL:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE ( [Type] = 1 AND ([Name] Like "*ImportErrors*"));

My problem now, is getting the tables to delete from
MsysObjects. I have the following SQL written for that:

DELETE TABLE
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*ImportErrors*") AND
((MsysObjects.Type)=1))
WITH OWNERACCESS OPTION;

Running the above SQL gives me an error saying I don't
have delete permission on MsysObjects. I am the creator
of the database, so I'm confused as to whether the problem
is with my syntax or my permissions. Any help would be
appreciated.

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Ok, sorry, but I'm getting more complicated. I may have 2
different import error tables. One should be
sales$_ImportErrors, and the other should be
trans$_ImportErrors. It's possible that on an import, I
will only get an import error table for one, not both
imports.

I was trying to make something general where I could
choose the table name Like "*ImportErrors*" in case I only
got one or neither of the import error tables, but
unfortunately, I'm not all that advanced when it comes to
modules, VBA, etc. Is this possible with either your or
Joan Wild's suggestion, which by the way, have been very
helpful?

Thanks a lot!
-----Original Message-----
Either follow Joan Wild's suggestion or, else, create a DeletObject Macro to
delete the table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


I'm trying to put together a macro that imports files,
appends them to tables, and then deletes the import error
tables. I haven't been able to get around the import
errors themselves, so I thought I could at least automate
the process of deleting them.

Any suggestions on how else I should do this? I need to
distribute this database to other users, and I'm afraid
they will not remember to delete the tables after every
import.

Thanks.
-----Original Message-----
Why are you trying to manipulate data in the
Click to expand...
MsysObjects
table? That is a
system table and you should NOT be attempting to do
Click to expand...
that.
Simply delete the
Import Errors table through the user interface.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi. I'm trying to set up a query that will delete my
import error tables. I've managed to find the tables,
using the following SQL:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE ( [Type] = 1 AND ([Name] Like "*ImportErrors*"));

My problem now, is getting the tables to delete from
MsysObjects. I have the following SQL written for that:

DELETE TABLE
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*ImportErrors*") AND
((MsysObjects.Type)=1))
WITH OWNERACCESS OPTION;

Running the above SQL gives me an error saying I don't
have delete permission on MsysObjects. I am the creator
of the database, so I'm confused as to whether the problem
is with my syntax or my permissions. Any help would be
appreciated.

Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top