[ODBC Microsoft Access Driver] Syntax error in FROM clause.

in response to the following query in access97:
SELECT zhaghevra.* FROM c:\my documents\projects\binaw\DATA\Binaw.mdb.zhaghevra WHERE zhaghevra.MisHevra = 1

i'm getting error number -2147217900
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
caused by Microsoft OLE DB Provider for ODBC Drivers
whe trying to open the recordset.

this used to work before upgrading to win2k and to vb6 sp6

can any one point me to what has changed due to the upgrdes that influences
the behavior of the driver?
zolisAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Hmm ...

1. This could be an MDAC issue. You may try to reinstall - or update using the newest from MS.

2. Didn't my alternative syntax work? It should.

3. Try wrapping you original syntax in brackets:

  SELECT
    zhaghevra.*
  FROM
    [c:\my documents\projects\binaw\DATA\Binaw.mdb].[zhaghevra]
  WHERE
    zhaghevra.MisHevra = 1;

/gustav
0
 
hongjunCommented:
you need to open a connection based on the connection string.
then you need to execute the sql select statement and store the recordset.

hongjun
0
 
hongjunCommented:
dim conn as new adodb.conection
dim rs as adodb.recordset
dim strsql as string

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\projects\binaw\DATA\Binaw.mdb;User Id=admin;Password=;"

strsql = "SELECT * FROM zhaghevra WHERE MisHevra = 1"
set rs = conn.execute(strsql)

'''''


conn.close
set rs = nothing
set conn = nothing



hongjun
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
zolisAuthor Commented:
hongjun

the connection is open with:
Provider=MSDASQL.1;Extended Properties="DBQ=c:\my documents\projects\binaw\DATA\BinaW.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

the recordset is defined and the error fires at  the   rs.open  command

i am using "Binaw.mdb.zhaghevra"  because later on in the programme i am
working with two databases that contain the same tables.

as i  mentioned in the question this format used to work previous to upgrading windows
and vb6

zolis
0
 
Gustav BrockCIOCommented:
> this used to work before upgrading to win2k and to vb6 sp6

I doubt that.

You miss the quotes and to clean up the syntax and filename to get the table and mdb extension right:

  SELECT * FROM zhaghevra IN "c:\my documents\projects\binaw\DATA\Binaw.mdb" WHERE MisHevra = 1

/gustav
0
 
StuartSandersCommented:
Gustav's reply can be used in a query, the record source for a report, form or recordset, etc.  This can be used any external access database.

Hi Gustav btw.

Stuart
0
 
Gustav BrockCIOCommented:
Hi Stuart

Nice to meet a well known name among the bunch of weird aliases!

/gustav
0
 
StuartSandersCommented:
Sorry for the message spam zolis.

to Gustav.  Only joined 2 days ago.  I noticed you've been around for a while.
0
 
zolisAuthor Commented:
hi Gustav

>I doubt that.

Take my word for it. It works like that for some two, three years on customers
computers. I have changed something on my computer during the latest upgrades
and I think that's the reason for the error. Some time ago I also had a problem with UNION and it still persists.
Don't look for syntax errors or table names from reserved words or things like such.
I didn't specify it before but I have a complete set of debugged routines which prepare the data for the query, execute it and so on...

hi Stuart ,

How do I write a SELECT query from two tables in different databases ?
0
 
StuartSandersCommented:

Simple example I just tested:

SELECT tbldetails.DetailId, tbldetails.Details
FROM tbldetails in "D:\Documents and Settings\Stuart\My Documents\db2.mdb"
UNION SELECT tbldetails.DetailId, tbldetails.Details
FROM tbldetails in "D:\Documents and Settings\Stuart\My Documents\db3.mdb"

Stuart
0
 
zolisAuthor Commented:
hi Gustav

YES, it works fine with square brackets. My big mistake was that I didn't copy
and paste the query string from the programme into Access and try running it from
there, because the real problem is a syntax error after all, and Access points exactly
to the place where the error occurs.

The space in "\My Documents\"  in the path to the database file causes the syntax error.
And to think that I was almost ready to reinstall Windows and everything else !!

hi Stuart ,

I am not using the IN clause because I think its Jet specific only, and I try to be as close to ANSI SQL as possible. But now I don't need it anyway.

Guys, this disscution helped a lot in finding the problem.

If you have any other comments before I close the question I would like to see them.

Thanks,
Zoli

0
 
Gustav BrockCIOCommented:
Good Zoli, that brought some kind of relief.

Stuart can talk for himself ... but as far as I know, IN is true ANSI SQL.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.