Solved

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

Posted on 2004-10-13
12
1,100 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:zolis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 12305603
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
 
LVL 33

Expert Comment

by:hongjun
ID: 12305612
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
 

Author Comment

by:zolis
ID: 12305722
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 12305962
> 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
 
LVL 3

Expert Comment

by:StuartSanders
ID: 12317201
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 12317291
Hi Stuart

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

/gustav
0
 
LVL 3

Expert Comment

by:StuartSanders
ID: 12317374
Sorry for the message spam zolis.

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

Author Comment

by:zolis
ID: 12321278
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 12321754
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
 
LVL 3

Expert Comment

by:StuartSanders
ID: 12322915

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
 

Author Comment

by:zolis
ID: 12328101
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 12331472
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

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question