ACC 97 Crush

H

Hermann Scheibe

Hi NG,

I've been having a really weird problem with ACC97.
Follow procedure does crush at the last line:

Sub Crush()
Dim qdf As DAO.QueryDef
' only for sample, IRL is the table already present
CurrentDb.Execute "CREATE TABLE Tmp(ID GUID CONSTRAINT pkTmp PRIMARY KEY,
V INTEGER)"
CurrentDb.TableDefs.Refresh
' I'm use CurrentDb but IRL it's may be a Connection to ODBC
Set qdf = CurrentDb.CreateQueryDef(Empty, _
"SELECT * FROM Tmp WHERE ID = ? ORDER BY V")
'Bo-o-ohm
Debug.Print qdf.Parameters.Count

End Sub

Can anybody reproduce the prb?

Thanx
Hermann
 
D

Dirk Goldgar

Hermann Scheibe said:
Hi NG,

I've been having a really weird problem with ACC97.
Follow procedure does crush at the last line:

Sub Crush()
Dim qdf As DAO.QueryDef
' only for sample, IRL is the table already present
CurrentDb.Execute "CREATE TABLE Tmp(ID GUID CONSTRAINT pkTmp
PRIMARY KEY, V INTEGER)"
CurrentDb.TableDefs.Refresh
' I'm use CurrentDb but IRL it's may be a Connection to ODBC
Set qdf = CurrentDb.CreateQueryDef(Empty, _
"SELECT * FROM Tmp WHERE ID = ? ORDER BY V")
'Bo-o-ohm
Debug.Print qdf.Parameters.Count

End Sub

Can anybody reproduce the prb?

Thanx
Hermann
Click to expand...

Yes, it crashes hard for me, too. I think it's because the '?'
character is not being interpreted as a parameter placeholder in Jet
SQL. If you rewrite the SQL as

SELECT * FROM Tmp WHERE ID = [Enter ID] ORDER BY V

the crash doesn't occur, and 1 is printed in the debug window.
 
D

Dirk Goldgar

Dirk Goldgar said:
Hermann Scheibe said:
Hi NG,

I've been having a really weird problem with ACC97.
Follow procedure does crush at the last line:

Sub Crush()
Dim qdf As DAO.QueryDef
' only for sample, IRL is the table already present
CurrentDb.Execute "CREATE TABLE Tmp(ID GUID CONSTRAINT pkTmp
PRIMARY KEY, V INTEGER)"
CurrentDb.TableDefs.Refresh
' I'm use CurrentDb but IRL it's may be a Connection to ODBC
Set qdf = CurrentDb.CreateQueryDef(Empty, _
"SELECT * FROM Tmp WHERE ID = ? ORDER BY V")
'Bo-o-ohm
Debug.Print qdf.Parameters.Count

End Sub

Can anybody reproduce the prb?

Thanx
Hermann
Click to expand...

Yes, it crashes hard for me, too. I think it's because the '?'
character is not being interpreted as a parameter placeholder in Jet
SQL. If you rewrite the SQL as

SELECT * FROM Tmp WHERE ID = [Enter ID] ORDER BY V

the crash doesn't occur, and 1 is printed in the debug window.
Click to expand...

I should amend this -- the crash doesn't occur if the same code is
executed in Access 2002, so I guess the problem is specifically with Jet
3.5 (and possibly earlier versions), and doesn't occur in Jet 4.0.
 
H

Hermann Scheibe

Ye, a SQL-syntax error was nice for it, but we have a crush application ,
i think it is a BUG !
If you rewrite the SQL as

SELECT * FROM Tmp WHERE ID = [Enter ID] ORDER BY V

the crash doesn't occur, and 1 is printed in the debug window.
Click to expand...

I should amend this -- the crash doesn't occur if the same code is
executed in Access 2002, so I guess the problem is specifically with Jet
3.5 (and possibly earlier versions), and doesn't occur in Jet 4.0.
Click to expand...

Yes, you are right, only ACC97 customers have problems with software,
another Office installations are not crushed.

The code is from a module what does used with both Jet and ODBC connections.
Thank you for tip with [Enter ID] but AFAIK it's not ODBC conform,
and this does't work for ODBCDirect connections.
A workaround was to brunch module source code :-(( for connections type ,
detect and change Jet versions to

PARAMETERS p <type>;
SELECT * FROM Tmp WHERE ID = p ORDER BY V

and add new function to resolve the parametrs type.
This will be changed to use your variant, and avoid type
resolve call for parameter.Thank your for the tip.

What do you mean, have we a chance that this BUG will be fixed by MS ?
I will place a comment in the module source, to review brunch code later.

Thank you very much,

Hermann
 
D

Douglas J. Steele

Are you saying that the query has a connection? In other words, it's a
pass-through query? In that case, you can't use parameters at all.

Pass-through queries run at the server, and therefore know nothing about
what values have been passed.
 
H

Hermann Scheibe

Dear Doug Steele,

Are you saying that the query has a connection?
Click to expand...

Yes, i've, wortly: query for a connection(Connection.CreateQueryDef),
please don't mix with QueryDef.Connect.
In other words, it's a pass-through query?
Click to expand...

AFAIK only for Jet workspaces.
In that case, you can't use parameters at all.

Pass-through queries run at the server, and therefore know nothing about
what values have been passed.
Click to expand...
In my knowledge pass-through doesn't deal with query object.
It 's only art of query execution - my subjectiv point of view.
But let us try a example...[SNIP]
Demonstration: use parametrized ODBCDirect query

Sub test()
With DBEngine.CreateWorkspace("Test", "sa", vbNullString, dbUseODBC)
With .OpenConnection(Empty, dbDriverComplete, False, "ODBC;DRIVER={SQL
Server}; SERVER=local;DATABASE=pubs; UID=sa;")
With .CreateQueryDef(Empty, "SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=?")
Debug.Print .Connect
.Parameters(0).value = "authors"
With .OpenRecordset()
If Not .EOF Then Debug.Print .Fields(0), " table found"
End With
End With
End With
End With
End Sub

It's run fine.

Regards,
Hermann
 
D

Dirk Goldgar

Hermann Scheibe said:
What do you mean, have we a chance that this BUG will be fixed by MS ?
I will place a comment in the module source, to review brunch code
later.
Click to expand...

I doubt that a bug in Access 97 or Jet 3.5 is going to be fixed by
Microsoft at this point.
 
H

Hermann Scheibe

Hello Dirk,
in message
[SNIP]
I doubt that a bug in Access 97 or Jet 3.5 is going to be fixed by
Microsoft at this point.
Click to expand...
Thank for your answer.
Your information is very excellent.
Where can i get info for the fix-process ? MS Knowledge Base ?

Regards,
Hermann
 
T

Tony Toews

Dirk Goldgar said:
I doubt that a bug in Access 97 or Jet 3.5 is going to be fixed by
Microsoft at this point.
Click to expand...

How old is Jet 3.5 SP3? Sep 28th, 1999. Yup, even with all the
security fixes MS have put in place for Jet 4.0 they've never gone
back to fix Jet 3.5. So I'd agree with you. MS isn't going to fix
Jet 3.5.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Top