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

    Create the table dilemma (Access2000-03)

    I am having a Report based off of Oracle view that linked into Access dtatbase.
    I wrote Select, Crosstab and Make Table queries for this Report.

    When I want to run this Report for the second time I am getting a messg that sysem can not lock table XXX because it is in use by another user or process. And there is no one but me who knows about where Reprot is. Closing and re-opening .mdb will not help the issue.
    After some 30 min or so I can run Report again. I am looking for the properties that maybe holding connection.

    I was told to create table in VBA and kill it after each use. Do you agree?
    And how difficult is it?

    Thanks so much.

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

    Re: Create the table dilemma (Access2000-03)

    Deleting a table in VBA isn't difficult, but I fear that you'll run into the same problem, i.e. that you can't delete the table because Access thinks it's in use. Make sure that all forms and queries that use the tables are closed.

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

    Re: Create the table dilemma (Access2000-03)

    How can I make sure everyting is closed?
    Like I said it is 3 queries involved - Select, Crosstam and Make Table.
    So Report is pulling dtat from Table that is a result of Make Table query.
    Thanks

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

    Re: Create the table dilemma (Access2000-03)

    A make-table query doesn't keep tables in use after it has run.
    A report doesn't keep tables in use after it has been closed (or when printing is finished).
    You have to watch out for queries, forms or reports that use the table(s) and that are still open.
    And if you open recordsets in DAO code, you have to make sure that they are closed when you are done with them.

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

    Re: Create the table dilemma (Access2000-03)

    OK, tha only code I am using is on click of the button
    Private Sub cmd_School_Click()

    Dim strWhere As String
    On Error GoTo ErrHandler

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "4-qry_Make_R_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

    Forms!Campus_Reports_Switchboard!cmb_School.Requer y

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

    and combo box filled as row Source
    SELECT DISTINCT [Campus_by_School].[SCHOOL] FROM [Campus_by_School] ORDER BY [SCHOOL];

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

    Re: Create the table dilemma (Access2000-03)

    Does it help if you close, then reopen the form after viewing the report?

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

    Re: Create the table dilemma (Access2000-03)

    Not even closing a whole database helps.

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

    Re: Create the table dilemma (Access2000-03)

    If Access complains about a table in the Access database itself being in use, someone else MUST still have the database open.
    If it complains about a linked Oracle table, it's a timing problem in Oracle, which has to be solved by your Oracle DBA.

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

    Re: Create the table dilemma (Access2000-03)

    I thank you for this. I couldn't explain it to people. We are not programmers but HR here and we are forced into Report creation so...fun but tough!

    Good Happy Holidays to you, Hans!

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

    Re: Create the table dilemma (Access2000-03)

    The fourth Thursday in November is Thanksgiving only in the USA, it'll be a normal day for me, but thanks anyway!

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

    Re: Create the table dilemma (Access2000-03)

    More or less reposted in <post:=678,850>post 678,850</post:>. This thread is locked to prevent further duplication. Please post all replies in the new thread.

Posting Permissions

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