Create table in code (Access2000-03)

[MS_fan]

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

[HansV]

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".

[MS_fan]

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

[HansV]

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.

[HansV]

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.

[MS_fan]

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?

[HansV]

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?

[MS_fan]

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!

[HansV]

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