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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
It sounds like Excel 2007 - which I don't use. So, I can't advise specifically on that.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top