Solved

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

Posted on 2004-10-13
12
1,066 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

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 49

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 49

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 49

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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