I need to disconnect table!!!!!!

[teqtaq]

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_School_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_School_Report"

Now after Report is up and then closed and I want to run ANOTHER Report it says 'Table School_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_School_Report"
none of these messages appear, so how do I run that query because I really have to...

It is even happening when I make an Oracle tables local.

Thanks

 

[dhookom]

Is you application split between front and back-ends? Every place I see where you might run the make-table, you have it commented out. Have you tried separating the make table and openreport into two separate button clicks?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[teqtaq]

I had actually changed Make Table to Append query and keeping my fingers crossed.
I would like to know dhookom,
what is the separating the make table and openreport into two separate button clicks would have done?
Is Make Table queries somehow affecting anything so tables gets no release? I had never gotten intot his stuff before. Is there anywhere to read up on this stuff? Thanks so much for an idea.

 

[dhookom]

Code in Access doesn't necessarily wait for the previous line to complete before the next line is executed. That's why I some times create a distinct time gap between processes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[teqtaq]

Well, please, kill me now! I was able to run all the Reports today in a few minutes! When yesterday I was not able to run 1.
How do I explain it in HR Dept where people aren't technical at all and IT they have is another company who aren't very cooperative with their needs?
Urghhhhhhh
Thanks for the explanations!

 

[Ed2020]

Code in Access doesn't necessarily wait for the previous line to complete before the next line is executed. That's why I some times create a distinct time gap between processes.

Really?? You mean two adjacent lines, in the same procedure?

I've never (knowingly) seen anything like that happening. Do you have any examples, or scenarios where this occurs? Not that I don't believe you, I'm just curious...

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

The code I am thinking of is if you open a form for user input and then expect following lines of code to use a value from a control on the form. This only works if you open the form in dialog mode.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ed2020]

Aha! I understand now.

I thought I'd spent the last few years under a major misconception for a while there!

Ed Metcalfe.

Please do not feed the trolls.....

 

[teqtaq]

But does anyone knows what is the difference between Make Table and Append query so one holds the table open and another releasing it after Append is completed?
I always thought of Make table as if it is the simpliest quesry of all. Thanks

 

[dhookom]

I'm not sure why the difference. An append query just affects data while a make table query adds a new object to your database (and affects data). You may need to add a line of code after the make table query like:

CurrentDb.TableDefs.Refresh

This is just a guess.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[teqtaq]

Worth trying, thanks