Syntax error in FROM clause with ms access

oaishm

Board Regular
Joined
Jan 30, 2009
Messages
97
I'm trying to query data from MS access in excel 2003 and access 2003.
I go through the Query tool wizard and inevitably get the Syntax error in FROM clause. Here's my super simple SQL:

Code:
SELECT pivot.Company, pivot.Code, pivot.Caption, pivot.Compte, pivot.`Libellé compte`, pivot.SumOfNet
FROM `C:\Documents and Settings\HP_Owner\Desktop\France\2007`.pivot pivot
I can't imagine the "FROM" stuff is accurate from the wizard, but there is no sample syntax anywhere in the entire web (or at least the first five googled pages.)

If anyone knows how to help, the access file is:

C:\Documents and Settings\HP_Owner\Desktop\France\2007.mdb

The table is called pivot

and I want to make a pivot table in excel out of it

Thanks
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

oaishm

Board Regular
Joined
Jan 30, 2009
Messages
97
Here are a few more things that don't work, while I pray someone answers

1. Erase Sql and add new table called pivot. Then drag field to columns down below. To bad we can't add screen shot easily

2. Try various forms of sql:
select * from pivot
select * from 'C:\etc. etc.\2007.mdb`.pivot;

Using the ";" changes the error from Syntax error in from clause to can't add table pivot
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi. I don't know the approach you are using. It sounds like you want an Excel pivot table from data in MS Access. If so, are you starting with the pivot table wizard? (And then the external data option.) F
 

oaishm

Board Regular
Joined
Jan 30, 2009
Messages
97
Here are my steps:

1. Open new worksheet
2. Go to Import External Data>New Database Query
3. Select MS Access Database.*
4. In the browser box, select c:\desktop\france\2007.mdb (or something similar, I forget)
5. Select the pivot query
6. Hit next until complete.

Then the Syntax error in FROM clause occurs. When I hit cancel, I am given the option of editing the SQL syntax which provided the messed up syntax. Guess their wizard isn't so hot.

Does anyone know the right syntax to specify the access database. I can always analyze from access, but then I can't update by hitting an exclamation point.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

It sounds like Excel 2007 - which I don't use. So, I can't advise specifically on that.
 

oaishm

Board Regular
Joined
Jan 30, 2009
Messages
97
I agree, excel 2007 is a piece of crap. I'll keep a computer on life support just to avoid that terrible ribbon concept and give my grandchildren the opportunity to use excel 2003 in 3003. So, sadly, I wasn't referrign to 2007. I was referring to 2003, the best version of excel before it jumped the shark
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,761
Office Version
  1. 2019
Platform
  1. Windows
The syntax looks okay to me. I've never had good luck with MSQuery either. Lately a thought that came to mind was starting over and trying a different driver -- I've got two choices in the list for Access drivers and maybe one might work better than the other.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,569
Messages
5,572,979
Members
412,491
Latest member
tweetytoon
Top