[AccessD] Jet version sql error Try 2

Hi Max,

Thanks for the thoughts.  I should have explained the situation a little
better. Did that in my first post.  The table is an application table that
holds mask settings for diffent country codes which I pre set, so I know it
is populated and the recordset returns values. tFM is a user defined type
set at the application level that is loaded with the various masks, once we
determine the computers country setting, e.g. phone number, date, address
etc.  The values returned by the country setting api do not contain any
punctuation.  I have run into this problem before where a user puts a single
quote into something a sql statement must use.  The application is a runtime
which uses a separate install of the Access 2002 runtime and its supporting
dlls.  The issue I beleive is that for some reason the version of jet that
is installed to run on Windows 98 machines interprets my sql string
differenetly than that on later operating systems.  MS has a jet version for
Win 98 machines and one for later versions.  They are both jet 4 SP 8.

This installation runs correctly on versions of windows later than 98 Second
Edition.  I try to allow users to run our application on any machine that
AccessXP would run on natively.  There aren't to many 98 machines left but
there are some, especially in other countries.  The interesting thing is
that other portions of the application that use recordsets work without
error.  As far as I can tell it is just this one routine.

I'll keep researching.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gmail
Sent: Tuesday, September 11, 2007 12:57 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Jet version sql error Try 2

Hi Doug,
To me this code does not make sense to me, but I understand that is is not
the complete code, so my comments below may be meaningless.

1. Presumably GetUserLocaleInfo is a function that returns a string (and not
a null) 2. You haven't tested to see if if sCountrySettings has a value
before using it.
3. What does tFM.sCountry mean.  If it is a field in a table then it really
should use the pling (!).  If it is a property setting in a Class then where
was the class defined?  The "." is really for a property. (I do understand
that some people use them for both, but the pling makes it clear what the
interpretation is to be.
4. You have told the recordset to move to the first record, but you haven't
tested to see if the recordset has any content.  If it is empty, then that
would generate an error.  Ie, use "If not rs.eof then...."
5. Are you sure that the returned value is actually what you type in. If
there is an embedded single quote in the returned value then  you can get
this error.
6. Are your Reference correctly installed and/or have you recently installed
an update on your system.  Does the function work for other values other
than the one you quoted?  You could try removing references and then putting
them back in again - do any show as "missing".

Hope some of this may help.
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Tuesday, September 11, 2007 5:13 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Jet version sql error Try 2

 
OK I'll try again with a simpler (?) question.

Any one see a reason why the following sql string generates an error when
used to open a recordset?

"Select * From tblUserFormat Where fldCountry = 'United States';"

Generates an error 3075 when run in the following line:

"Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)"

The error message that gives the error number 3075 indicates that there is
some problem with fldCountry = 'United States'

I have modified the code slightly from my first post to use a dynaset type
recordset and added the semicolon at the end of the query string.  Same
error is generated with the updated code.  The complete procedure up to this
point is as follows.

Public Sub GetCountrySettings()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL As String
Dim sCountrySettings As String
Dim fld As Field
Dim LCID As Long

  LCID = 0  'GetSystemDefaultLCID()

   On Error GoTo GetCountrySettings_Error

      sCountrySettings = GetUserLocaleInfo(LCID, LOCALE_SCOUNTRY)
      tFM.sCountry = sCountrySettings
      
      If Not IsNull(sSQL) Then
            sSQL = "Select * From tblUserFormat Where fldCountry = '" &
sCountrySettings & "';"
            Set db = CurrentDb
            Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
            rs.MoveFirst

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com