Solved

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

Posted on 2004-10-13
12
1,052 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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> 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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
Hi Stuart

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

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Expert Comment

by:StuartSanders
Comment Utility
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
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
Comment Utility

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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now