Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

URGENT: error 3075 while trying to delete records

Discussion in 'Information Technology' started by Broadway, Nov 9, 2005.

  1. Broadway

    Broadway
    Expand Collapse
    Guest

    i am trying to use a list from a combo box, which has names that coincide
    with table names to delete records whose one field does not match the name of
    the table (the table name is the unit the person is in, and there is a field
    for unit in the table).
     
  2. Loading...

    Similar Threads Forum Date
    URGENT PLZZ HELP: KAAM LUST BODY MASSAGE SPA ISSUE Sikh Youth Jul 9, 2015
    Sikh News Spate Of Attacks By Islamist Insurgents Rattle Sikh Community In Pakistan Breaking News Apr 19, 2015
    SciTech Urgent Need to Remove Space Debris Breaking News Apr 25, 2013
    Urgent! Need help with some gender equality questions! Questions and Answers Jan 7, 2013
    General URGENT! Video on reality of sexual grooming gangs in the UK - please share Hard Talk Nov 21, 2012

  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Can you post the delete statment you are using?

    It can happen when you try th filter the records you want to delete, no by
    the type of the field
    For example

    For number
    "Delete * From Tablename Where FieldName = " & Parameter
    For string
    "Delete * From Tablename Where FieldName = '" & Parameter & "'"
    For date
    "Delete * From Tablename Where FieldName = #" & Parameter & "#"


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Broadway" wrote:

    > i am trying to use a list from a combo box, which has names that coincide
    > with table names to delete records whose one field does not match the name of
    > the table (the table name is the unit the person is in, and there is a field
    > for unit in the table).
     
  4. Broadway

    Broadway
    Expand Collapse
    Guest

    I knew you were going to ask for the code, i just didn't want anyone to see
    how horrible it really is. Good luck deciphering it. I hope you can help me
    make it work!

    Dim a As String
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
    If obj.Name = units Then
    a = a
    Else
    DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    ".UNIT=" & "'" & units & "'" & "));"

    End If
    Next obj


    "Ofer" wrote:

    > Can you post the delete statment you are using?
     
  5. Ofer

    Ofer
    Expand Collapse
    Guest

    Where the unit parameter define?
    What value do you insert in it?

    And try this, add square brackets to the table name

    For Each obj In dbs.AllTables
    If obj.Name <> units Then
    DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE (([" &
    obj.Name & "].UNIT=" & "'" & units & "'" & "));"
    End If
    Next obj

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Broadway" wrote:

    > I knew you were going to ask for the code, i just didn't want anyone to see
    > how horrible it really is. Good luck deciphering it. I hope you can help me
    > make it work!
    >
    > Dim a As String
    > Set dbs = Application.CurrentData
    > For Each obj In dbs.AllTables
    > If obj.Name = units Then
    > a = a
    > Else
    > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    > ".UNIT=" & "'" & units & "'" & "));"
    >
    > End If
    > Next obj
    >
    >
    > "Ofer" wrote:
    >
    > > Can you post the delete statment you are using?

    >
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    The AllTables collection includes every table in the database, including
    system tables, many of which (all of which as far as I am aware) do not
    include any field named 'UNIT'.

    If this code is running in an MDB, you could skip system tables like so ...

    Public Sub ListNonSystemTables()

    Dim aob As AccessObject
    For Each aob In CurrentData.AllTables
    If LCase$(Mid$(aob.Name, 2, 3)) <> "sys" Then
    Debug.Print aob.Name
    End If
    Next aob

    End Sub

    .... assuming, of course, that you don't have any tables you *do* want
    included with the letters "sys" in that position in the name.

    --
    Brendan Reynolds

    "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    news:BCDD0AC8-0F0F-477E-AE93-0EED6383D818@microsoft.com...
    >I knew you were going to ask for the code, i just didn't want anyone to see
    > how horrible it really is. Good luck deciphering it. I hope you can help
    > me
    > make it work!
    >
    > Dim a As String
    > Set dbs = Application.CurrentData
    > For Each obj In dbs.AllTables
    > If obj.Name = units Then
    > a = a
    > Else
    > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    > ".UNIT=" & "'" & units & "'" & "));"
    >
    > End If
    > Next obj
    >
    >
    > "Ofer" wrote:
    >
    >> Can you post the delete statment you are using?

    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Probably a good idea to put it around the table name everywhere:

    DoCmd.RunSQL "DELETE * FROM [" & obj.Name & "] " & _
    WHERE [" & obj.Name & "].UNIT=" & "'" & units & "'"

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ofer" <Ofer@discussions.microsoft.com> wrote in message
    news:2938AF9D-A2A2-4411-BBCE-43B2C631168F@microsoft.com...
    > Where the unit parameter define?
    > What value do you insert in it?
    >
    > And try this, add square brackets to the table name
    >
    > For Each obj In dbs.AllTables
    > If obj.Name <> units Then
    > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE (([" &
    > obj.Name & "].UNIT=" & "'" & units & "'" & "));"
    > End If
    > Next obj
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Broadway" wrote:
    >
    > > I knew you were going to ask for the code, i just didn't want anyone to

    see
    > > how horrible it really is. Good luck deciphering it. I hope you can

    help me
    > > make it work!
    > >
    > > Dim a As String
    > > Set dbs = Application.CurrentData
    > > For Each obj In dbs.AllTables
    > > If obj.Name = units Then
    > > a = a
    > > Else
    > > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    > > ".UNIT=" & "'" & units & "'" & "));"
    > >
    > > End If
    > > Next obj
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Can you post the delete statment you are using?

    > >
     
  8. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    Might be easier to use "MSys", wouldn't it? And with the InStr function:

    Public Sub ListNonSystemTables()

    Dim aob As AccessObject

    For Each aob In CurrentData.AllTables
    If InStr(1, aob.Name, "MSys") = 0 Then
    Debug.Print aob.Name
    End If
    Next aob

    End Sub


    "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    news:%23D4XW$R5FHA.3636@TK2MSFTNGP09.phx.gbl...
    > The AllTables collection includes every table in the database, including
    > system tables, many of which (all of which as far as I am aware) do not
    > include any field named 'UNIT'.
    >
    > If this code is running in an MDB, you could skip system tables like so

    ....
    >
    > Public Sub ListNonSystemTables()
    >
    > Dim aob As AccessObject
    > For Each aob In CurrentData.AllTables
    > If LCase$(Mid$(aob.Name, 2, 3)) <> "sys" Then
    > Debug.Print aob.Name
    > End If
    > Next aob
    >
    > End Sub
    >
    > ... assuming, of course, that you don't have any tables you *do* want
    > included with the letters "sys" in that position in the name.
    >
    > --
    > Brendan Reynolds
    >
    > "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    > news:BCDD0AC8-0F0F-477E-AE93-0EED6383D818@microsoft.com...
    > >I knew you were going to ask for the code, i just didn't want anyone to

    see
    > > how horrible it really is. Good luck deciphering it. I hope you can

    help
    > > me
    > > make it work!
    > >
    > > Dim a As String
    > > Set dbs = Application.CurrentData
    > > For Each obj In dbs.AllTables
    > > If obj.Name = units Then
    > > a = a
    > > Else
    > > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    > > ".UNIT=" & "'" & units & "'" & "));"
    > >
    > > End If
    > > Next obj
    > >
    > >
    > > "Ofer" wrote:
    > >
    > >> Can you post the delete statment you are using?

    > >

    >
    >
     
  9. Broadway

    Broadway
    Expand Collapse
    Guest

    Thanks to everyone who helped with the problem, the program is working great
    now. Watch for my next question, I'm sure it will be something easy like
    this one was too. Appreciate it!
     
  10. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Although it is probably seldom used, you can give a table a name beginning
    "USys", and Access will hide it when system tables are hidden. The code I
    posted is designed to treat "MSys" and "USys" tables the same. Also, I did
    some testing once and the results indicated that one of the two prefixes
    "MSys" and "USys" was case-sensitive, while one wasn't. I can't remember now
    which was which, but that is the reason for the use of the LCase$ function
    in the code I posted. Basically, it is designed to treat as a system table
    any table that would by treated as such by the Access user interface.

    Strictly speaking, we should be testing for "MSys" or "USys" and ignoring
    "ASys", "BSys", "AnythingButMOrUSys", I've just never bothered to do that as
    it has never been a problem for me - I've never had tables with names
    falling into that pattern.

    Other than that, though, you are of course correct that a simple test for
    "MSys" will work in most databases.

    --
    Brendan Reynolds

    "Ron Hinds" <__ron__dontspamme@wedontlikespam_garageiq.com> wrote in message
    news:u6zDlxY5FHA.1536@TK2MSFTNGP12.phx.gbl...
    > Might be easier to use "MSys", wouldn't it? And with the InStr function:
    >
    > Public Sub ListNonSystemTables()
    >
    > Dim aob As AccessObject
    >
    > For Each aob In CurrentData.AllTables
    > If InStr(1, aob.Name, "MSys") = 0 Then
    > Debug.Print aob.Name
    > End If
    > Next aob
    >
    > End Sub
    >
    >
    > "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    > news:%23D4XW$R5FHA.3636@TK2MSFTNGP09.phx.gbl...
    >> The AllTables collection includes every table in the database, including
    >> system tables, many of which (all of which as far as I am aware) do not
    >> include any field named 'UNIT'.
    >>
    >> If this code is running in an MDB, you could skip system tables like so

    > ...
    >>
    >> Public Sub ListNonSystemTables()
    >>
    >> Dim aob As AccessObject
    >> For Each aob In CurrentData.AllTables
    >> If LCase$(Mid$(aob.Name, 2, 3)) <> "sys" Then
    >> Debug.Print aob.Name
    >> End If
    >> Next aob
    >>
    >> End Sub
    >>
    >> ... assuming, of course, that you don't have any tables you *do* want
    >> included with the letters "sys" in that position in the name.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    >> news:BCDD0AC8-0F0F-477E-AE93-0EED6383D818@microsoft.com...
    >> >I knew you were going to ask for the code, i just didn't want anyone to

    > see
    >> > how horrible it really is. Good luck deciphering it. I hope you can

    > help
    >> > me
    >> > make it work!
    >> >
    >> > Dim a As String
    >> > Set dbs = Application.CurrentData
    >> > For Each obj In dbs.AllTables
    >> > If obj.Name = units Then
    >> > a = a
    >> > Else
    >> > DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
    >> > ".UNIT=" & "'" & units & "'" & "));"
    >> >
    >> > End If
    >> > Next obj
    >> >
    >> >
    >> > "Ofer" wrote:
    >> >
    >> >> Can you post the delete statment you are using?
    >> >

    >>
    >>

    >
    >
     

Share This Page