?
Solved

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

Posted on 2004-10-13
12
Medium Priority
?
1,128 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 51

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 51

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 51

Accepted Solution

by:
Gustav Brock earned 1500 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 51

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 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