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

"Hermann Scheibe" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)
> 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

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> "Hermann Scheibe" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)
>> 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
>
> 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.

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote
> "Dirk Goldgar" <(E-Mail Removed)> wrote
> > "Hermann Scheibe" <(E-Mail Removed)> wrote
>> 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
> >
> > 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.

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.
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>

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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Hermann Scheibe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

Dear Doug Steele,

"Douglas J. Steele" wrote
> Are you saying that the query has a connection?

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?

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.
>
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...
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
[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

"Hermann Scheibe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> 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.

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hello Dirk,
"Dirk Goldgar" wrote 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
Thank for your answer.
Your information is very excellent.
Where can i get info for the fix-process ? MS Knowledge Base ?

Regards,
Hermann

"Dirk Goldgar" <(E-Mail Removed)> wrote:

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

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