G
Guest
To explain my question, I will give some back ground as to why I am doing
what I am doing in the first place.
Background... I run a lot of querys against odbc oracle tables.
1) I have found that queries run up to 10X faster if I run a parameter
query over and over and fill the parameters thru VBA than if I were to join
the parameters as a table in the query to be run.
2) The oracle tables have type "Decimal" in them. The only way I have
found to create type "Decimal" in a table is to include them and run a make
table query.
Now the problem. I have created a custom function to run a parameter query
with a list of parameters and output the info to a table:
Public Function ParameterQueryList(QueryName As String, ParameterListTable
As String, WriteToTable As String, Optional DeleteOutputTableFirst As Boolean
= True, Optional ShowStatusRecordCount As Boolean = True) As Date
If the "WriteToTable" table was deleted or never existed (1st run) then I
need to create the table with no record. Due to the "decimal" issue above,
the way I create the table is to get the sql info from the parameter query,
edit the sql to a "make table", get one parameter and run the sql, then
delete the data that went to the table.
============================
Private Function CreateTable(QueryName As String, WriteToTable As String,
ParameterListTable As String) As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs_parameterlist As DAO.Recordset
Dim qdmaketable As DAO.QueryDef
Dim OldString As String
Dim ReplaceString As String
Dim NewString as String
Dim answer As String
Dim k As Integer
Set db = CurrentDb()
Set qd = db.QueryDefs(QueryName)
Set rs_parameterlist = db.OpenRecordset(ParameterListTable)
rs_parameterlist.MoveLast
rs_parameterlist.MoveFirst
OldString = qd.SQL
ReplaceString = "INTO " & "[" & WriteToTable & "]" & " FROM "
NewString = Replace(OldString, "FROM ", ReplaceString , 1, -1, vbTextCompare)
Set qdmaketable = db.CreateQueryDef("", NewString)
For k = 0 To qdmaketable.Parameters.Count - 1
qdmaketable.Parameters(k).Value = rs_parameterlist.Fields(k).Value
Next k
rs_parameterlist.Close
qdmaketable.Execute
CreateTable = 1
End Function
====================
The functions have been working for months. But, Now... in one database,
the CreateTable function works properly in 6 different querys, but there is
one query that it just locks. Access just locks up after executing the
"qdmaketable.Execute" statement. The actual call is:
ParameterQueryList("937 112 b) Parameter Query - Current Mo Corp Div
Part","937 111b) Parameter List - Table","937 112 b) Parameter Query -
Current Mo Corp Div Part - Table",Yes,Yes)
When I break out of access and restart the database, the table was created
correctly but with no data (the parameter that was entered shows 1 line of
data when run manually).
If I run the parameter query normally and enter a parameter, it runs fine.
And, Like I said, I have these functions in a library database that I use all
the time with no problem.
I have tried to put ?NewString in the immediate window and copy / paste the
sql into a new query in sql design mode. It comes back with, what looks like
a CR/LF that splits up one of the lines in the query. I have done a:
NewString = Replace(NewString, Chr(13) & Chr(10), " ") to strip out the
CR/LFs but it still does not work.
Any help would be appreciated.
Thanks
Steve
what I am doing in the first place.
Background... I run a lot of querys against odbc oracle tables.
1) I have found that queries run up to 10X faster if I run a parameter
query over and over and fill the parameters thru VBA than if I were to join
the parameters as a table in the query to be run.
2) The oracle tables have type "Decimal" in them. The only way I have
found to create type "Decimal" in a table is to include them and run a make
table query.
Now the problem. I have created a custom function to run a parameter query
with a list of parameters and output the info to a table:
Public Function ParameterQueryList(QueryName As String, ParameterListTable
As String, WriteToTable As String, Optional DeleteOutputTableFirst As Boolean
= True, Optional ShowStatusRecordCount As Boolean = True) As Date
If the "WriteToTable" table was deleted or never existed (1st run) then I
need to create the table with no record. Due to the "decimal" issue above,
the way I create the table is to get the sql info from the parameter query,
edit the sql to a "make table", get one parameter and run the sql, then
delete the data that went to the table.
============================
Private Function CreateTable(QueryName As String, WriteToTable As String,
ParameterListTable As String) As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs_parameterlist As DAO.Recordset
Dim qdmaketable As DAO.QueryDef
Dim OldString As String
Dim ReplaceString As String
Dim NewString as String
Dim answer As String
Dim k As Integer
Set db = CurrentDb()
Set qd = db.QueryDefs(QueryName)
Set rs_parameterlist = db.OpenRecordset(ParameterListTable)
rs_parameterlist.MoveLast
rs_parameterlist.MoveFirst
OldString = qd.SQL
ReplaceString = "INTO " & "[" & WriteToTable & "]" & " FROM "
NewString = Replace(OldString, "FROM ", ReplaceString , 1, -1, vbTextCompare)
Set qdmaketable = db.CreateQueryDef("", NewString)
For k = 0 To qdmaketable.Parameters.Count - 1
qdmaketable.Parameters(k).Value = rs_parameterlist.Fields(k).Value
Next k
rs_parameterlist.Close
qdmaketable.Execute
CreateTable = 1
End Function
====================
The functions have been working for months. But, Now... in one database,
the CreateTable function works properly in 6 different querys, but there is
one query that it just locks. Access just locks up after executing the
"qdmaketable.Execute" statement. The actual call is:
ParameterQueryList("937 112 b) Parameter Query - Current Mo Corp Div
Part","937 111b) Parameter List - Table","937 112 b) Parameter Query -
Current Mo Corp Div Part - Table",Yes,Yes)
When I break out of access and restart the database, the table was created
correctly but with no data (the parameter that was entered shows 1 line of
data when run manually).
If I run the parameter query normally and enter a parameter, it runs fine.
And, Like I said, I have these functions in a library database that I use all
the time with no problem.
I have tried to put ?NewString in the immediate window and copy / paste the
sql into a new query in sql design mode. It comes back with, what looks like
a CR/LF that splits up one of the lines in the query. I have done a:
NewString = Replace(NewString, Chr(13) & Chr(10), " ") to strip out the
CR/LFs but it still does not work.
Any help would be appreciated.
Thanks
Steve