Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create table in code (Access2000-03)

    At the click of the button I need to run code that will take value from combo box and base don the value will select dtat for the report.
    Main table that contains data is created by running MakeTable query.
    If I uinclude MakeTable query into code as

    'DoCmd.OpenQuery "4-qry_Make_School_Report"

    then table Make_School_Report will stay open and never close.

    How should I make this table so it will not interfere with anything? Run Macro in code? DoCmd.OpenMacro etc...
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create table in code (Access2000-03)

    A make-table query only creates a table, it doesn't open that table. It doesn't matter whether you run the make-table interactively or using VBA code. So I don't understand what you mean by "table Make_School_Report will stay open and never close".

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table in code (Access2000-03)

    It is my old issue.
    I need to run MakeTable query that all other queries will be pulling and manipulating data from.
    So at the beginning of running Report I need to run MakeTable query.

    I have a form. Form had a button. On a click of this button I am running a code you shared with me:
    Private Sub cmd_School_Click()

    Dim strWhere As String
    On Error GoTo ErrHandler

    DoCmd.SetWarnings False
    'DoCmd.OpenQuery "4-qry_Make_Campus_School_LOB_Report"


    If Not IsNull(Forms!Campus_Reports_Switchboard!cmb_School ) Then
    strWhere = "SCHOOL=" & Chr(34) & Forms!Campus_Reports_Switchboard!cmb_School.Column (0) & Chr(34)

    End If


    DoCmd.OpenReport ReportName:="Campus_by_School", View:=acViewPreview, WhereCondition:=strWhere


    Exit Sub


    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore this
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    __________________________

    So I needed to have this MakeTable query to be run somehow so I stuck it here
    DoCmd.SetWarnings False
    'DoCmd.OpenQuery "4-qry_Make_Campus_School_LOB_Report"

    Now after Report is up and then closed and I want to run ANOTHER Report it says 'Table Campus_School_LOB_Report can not be locked because it is used by another person or process'
    When I take this line away 'DoCmd.OpenQuery "4-qry_Make_Campus_School_LOB_Report"
    none of these messages appear, so how do I run that query because I really have to...

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create table in code (Access2000-03)

    So this is basically the same problem as <post:=677,752>post 677,752</post:>? We concluded there that it's not an Access problem but that the Oracle people will have to get their act together.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create table in code (Access2000-03)

    Normal behavior is that a make-table does its work, then releases the new table. You mention that even quitting Access will not release the table, so it is not something in Access that keeps the table in use. Since you also mentioned that linked Oracle tables are involved, I can only conclude that Oracle is the culprit. I don't know much about Oracle so I cannot help you with that.

    There is no point in having two threads about exactly the same question, so I will lock the other one now.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table in code (Access2000-03)

    It is the same yes but I was thinking if I AM using this DoCmd.RunQuery - it is still Oracle people stuff?
    Because when I am not running this all is working fine...
    So you do not see anything wrong with me running this query in code as i am running it? Thanks

    I am trying to reach anybody on Oracle site for a whole day but nothing is happening.
    So I can tell them to break timing right?

    Thanks so much

    P.S. Hans,
    I just remember why I thought it was not Oracle fault...
    I took all the tebles and made them local, not linked and thing still happening...sorry i forgot.
    Now who'd fault is it?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create table in code (Access2000-03)

    It's difficult if you don't provide the relevant details.

    You mentioned before that closing the database wasn't enough to release the table. You had to wait a long time. Is that still the case now that the tables are local?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table in code (Access2000-03)

    HAns,
    believe it or not I had re-create this quesry as Append and now the problem is gone.
    I would like to know whats up? Anyone ever heard of Make Table query acting like that?
    Yes and btw today I ran all my Reports in 3 minutes - ALL! In 3 minutes! I dunno - I am frustrated because it is not my fault and I can't prove it!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create table in code (Access2000-03)

    No idea why, but be happy that you found a workaround!

Posting Permissions

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