I created an application in Delphi Professional 5.0 that was interfacing
with an MSAccess 97 database. The application uses alot of SQL
statements.This version of the application ran fine. My customer upgraded
his system to MS Office 2000 and asked that I convert the database to Access
2000.
"No problem"  I said..... {That's just the kind of person I am!}
Now I am finding several problems exist with this newer version. I am using
ODBC32 to connect to the tables in the database and everything seems to work
fine until a query has to run, which ran fine with Access 97. Now I get all
kinds of errors, but I'll start with just one for now:

'General SQL Error'
[Microsoft][ODBC Microsoft Access Driver] Invalid bracketing of Name
'[.]'.'. Process stopped. Use step or run to continue.
Here is an example of the query:

sql.add('SELECT');
sql.add('"Student Schedule"."City");
sql.add('+');
sql.add('",");
sql.add('+');
sql.add('"Student Schedule"."State");
sql.add('+');
sql.add('".");  {this is where the error is pointing to}
sql.add('+');
sql.add('"Student Schedule"."Zip Code");
sql.add('FROM "Student Schedule"');

What is wrong with this code?  Why is such a simple query causing problems
for me and probably hundreds of other programmers having to write
applications that access MS Access databases?

Thanks in advance for any help you can offer!

If you are using the BDE there is a problem with the Access 2000 ODBC driver and
the BDE.

This is from a July Tech Alert

"I.    Current Tech Alerts
=================================================
Product: Delphi/BDE
Version: 4.0/5.0
Description: Problems with Newest MS Access driver
Symptoms: "Record Key Deleted" or "Size Mismatch"    error.

Steps to Reproduce:
Installing the newest version of the MS Access
ODBC driver, version 4.00.3711.08, can cause these
errors. This new version comes with Windows 98
release 2, Access 2000, the Microsoft Data Access
Components (MDAC) and perhaps other sources.
The error is happening because Microsoft has made a
change in the driver regarding the length of fields,
and now the BDE is expecting the field length to be
twice as long as it actually is.  For example, a
field length may be 30 in the Access database, but
the BDE reports it to be 60.

Microsoft documentation specifies that the length,
in bytes, of data transferred (the buffer size) on
an SQLGetData, SQLFetch, or SQLFetchScroll operation
shall be the same as the COLUMN_SIZE for binary
and character data. This is not in fact the case
with the new driver; the buffer is actually larger
than COLUMN_SIZE.

The BDE does not look at COLUMN_SIZE because for
numerical data it may not be the same as the
buffer size. Instead the BDE looks at the buffer
size which now has the erroneous value.

Workarounds:
1. Use the Delphi native driver instead of ODBC.
2. Use an older version of the Access ODBC driver.
3. In Delphi 5: Use ADO components to access the
   MS JET database engine or to access ODBC drivers.
   Accessing the ODBC driver this way is not
   optimal, but does bypass the BDE which will
   avoid the problem."

An update to this, since this came out MS changed the ODBC3.5 spec so that the
A2K ODBC driver is now complaint with the spec instead of fixing the driver.
The BDE supports ODBC 2.5 at this time.  

"Glenn Martin (Chemist/System Support Analyst)" wrote:

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
The bond that links your true family is not one of {*word*76}, but of respect and joy
in each others life.  Rarely do members of one family grow up under the same
roof.   (R Bach)

I am surprised that this query works at all given the amount of quoting
errors. I suppose you are trying to concatenate the fields into one address
field. The actual query line should look something like:

    Select (City + ", " + State + " " + [Zip Code]) AS Address from [Student
Shedule]

I don't know why you want to put a period in between state and zip code but
I used a space. Most databases prefer bracketed table and field names to
quoted ones. Also, you didn't give the concatenated field a name that could
be used to access it. To me, it looks like Access is interpreting the period
you are trying to use as a field delimiter instead of a part of the string
you are building.

Sorry if I have misinterpreted your question or query, but I think that MS
Access 2000 was changed to only allow bracketed names but I don't have a
copy so I can't be sure. Maybe someone else know this information.

HTH

--

Woody

Glenn Martin (Chemist/System Support Analyst) <gemchem...@mindspring.com>
wrote in message news:86ev5n$le12@bornews.borland.com...

Queries are not simple when you use spaces in names.  Try it like this

sql.add('SELECT');
sql.add('[Student Schedule].[City], ');
sql.add('[Student Schedule].[State], ');
sql.add('[Student Schedule].[Zip Code]);
sql.add('FROM [Student Schedule]');

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Thanks for that response, but The code should have read:

Brian Bushay TeamB <BBus...@Nmpls.com> wrote in message
news:388bb5bb.33017295@floyd...

It makes no sense to put a period there

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

And shouldn't there be a single quote after the ." ???