Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Drop Table

  1. #1
    Join Date
    Feb 2005
    Posts
    33

    Unanswered: Drop Table

    Hi everybody,

    we sync data from ms-sql to clients with a ms-access db.
    I have to drop a table at only one client via sql.
    Is there a way to do that with a condition ?
    My wish would be something like that:
    drop table [mytablename] where '%OBUSERID%'='xyz'
    %OBUSERID% is a system variable in the sync software and is filled with the username of the current client who is doing the sync
    xyz is the name of the user whose table I want to drop

    I think a where clause is not supported with the drop command.
    I must not drop this table for other users.
    I can only use SQL syntax.
    The command should not result in an error.
    Is there a trick I could use ?

    Best regards
    and thanks for your help

    BTW: does anybody know a good website with a ms access sql tutorial (or special sql features only) for non-Access-Users (only odbc sql commands on a mdb file) ?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Are you dropping the table in SQL Server or in Access?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2005
    Posts
    33
    in Access (on the client)
    Last edited by ifx; 03-21-07 at 09:25.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Access has ownership of tables and permissions as to who can see,edit,modify, and delete them ... However, there is no DROP ability as you've defined in your psuedo SQL ... You can drop the specified table name or not.

    Can you explain the setup of your sync process a bit more? Are you synching to a single Access database or are you synching to multiple dbs?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ifx
    BTW: does anybody know a good website with a ms access sql tutorial (or special sql features only) for non-Access-Users (only odbc sql commands on a mdb file) ?
    1) http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
    2) http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
    3) http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

    Perhaps you could revoke drop permissions to all users for this table, grant them to 'xyz', drop the table (ok - you will get an error for the other users) and return the permissions as they were.

    ....nah - that's a load of crap. What ^^^^he^^^^ said
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2005
    Posts
    33
    Thanks for your replies.

    Quote Originally Posted by M Owen
    Can you explain the setup of your sync process a bit more? Are you synching to a single Access database or are you synching to multiple dbs?
    There are multiple clients. Each client has a local Access database and has to sync his changes to/from the server (MS-SQL). Each client starts his own sync process (once a day or whatever).
    There is no permission problem. The sync software (installed on the server and on each client) is allowed to create or drop tables on the clients.
    The problem is that I must not drop this specific table on all clients but only on one specific client. I have no further access to the clients Access databases than by the sync process via sql commands.

    Is there any IF/THEN-structure possible ?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Case statements?
    Case When '%OBUSERID%'='xyz' Then ... Else .... End

    Worth a shot... Maybe...
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Since you multiple access dbs being synched, about the only way to set this up realistically is to make a table that is accessed by all clients that gets polled by the client to see if they need to perform an action ...

    Table: ActionTable

    ActionType - DROP, etc ...
    UserID
    TableName
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2005
    Posts
    33
    Quote Originally Posted by georgev
    Case When '%OBUSERID%'='xyz' Then ... Else .... End
    It doesn't work (seems that CASE is not supported in Access).

    Could something possible like the following ?

    CREATE PROCEDURE droptablefromxyz (userid text(16)) AS
    if userid='xyz'
    then drop table [mytablename]
    end;
    EXECUTE droptablefromxyz '%OBUSERID%';
    DROP PROCEDURE droptablefromxyz;

    But I don't know the correct syntax and if procedures can do more than regular sql statements.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ifx
    It doesn't work (seems that CASE is not supported in Access).
    And there was me thinking this was an SQL thing...

    Try If (or Iif in queries) statements.
    Give that a try.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2005
    Posts
    33
    Quote Originally Posted by georgev
    Try If (or Iif in queries) statements.
    Hi georgev,
    thanks for your suggestions.

    "iif" is a function within select statements (so I cannot use it).
    "if" doesn't work (seems to be not supported, but I am not sure if I tried all syntax possibilities).

    I only checked out that the following works with MS-SQL but not with MS-Access:
    if '%OBUSERID%'='xyz' drop table [mytablename]
    Last edited by ifx; 03-21-07 at 13:12.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, so I can try understand your method of sync a bit better, would you care to post an example of the code you have used to drop a table in the past (for everyone).
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ifx
    I only checked out that the following works with MS-SQL but not with MS-Access:
    if '%OBUSERID%'='xyz' drop table [mytablename]
    Yeah - the problem you have is there can be no multiblock statements in JET\ Access SQL code. There can be no conditional statement branches. Thus a JET Proc is about as useful as a chocolate fireguard.

    Cutting to the chase - why can't you do this in VB?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought we were doing it VB - hence the confusion
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2005
    Posts
    33
    Quote Originally Posted by georgev
    would you care to post an example of the code you have used to drop a table in the past (for everyone).
    I haven't done it, cause it was not necessary, but it would be:
    drop table [mytablename]


    Quote Originally Posted by pootle flump
    why can't you do this in VB?
    As I already mentioned I can only use SQL statements.
    I have to enter them in the sync software on the server as additional sql which will be executed on the client after/before each sync process.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •