SQL Syntax Problem

[Guest]

Below is an SQL statment I am trying to get to work to pull data into Access
from Excel. The problem is with the range (FROM [80%!A3200] ). The
current syntax is the closest I can get.
The error returned is 3125 - Is not a valid name. Be sure it doesn't
contain invalid characters or punctuation....
Every other way I have tried returns 3131 Invalid syntax in FROM clause.
I was suspicious about the %, but it workd with a transferspreadsheet. I
really don't what to have to link it, I would rather just get it in this way.

strSql = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, " _
& "Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005
April'" _
& " FROM [80%!A3200] IN '' [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;];"

 

[John Nurick]

I'm really suspicious of the % too. Non-alphanumeric characters in
object names always seem to bite one in the end.

The fact that it works with TransferSpreadsheet doesn't rule out % as
the cause - especially if you are using ADO to execute the query. If you
are, try DAO, where % is not a wildcard.

Below is an SQL statment I am trying to get to work to pull data into Access
from Excel. The problem is with the range (FROM [80%!A3200] ). The
current syntax is the closest I can get.
The error returned is 3125 - Is not a valid name. Be sure it doesn't
contain invalid characters or punctuation....
Every other way I have tried returns 3131 Invalid syntax in FROM clause.
I was suspicious about the %, but it workd with a transferspreadsheet. I
really don't what to have to link it, I would rather just get it in this way.

strSql = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, " _
& "Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005
April'" _
& " FROM [80%!A3200] IN '' [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;];"

 

[Guest]

Thanks for the response, John,
I tried changing the name of the work sheet to 'Eighty' to see if that was
the problem. It was not. If I name the range, it works fine. The issue is,
I can't depend on the person responsible for creating the sheet to put the
range name in it. That is why I was trying to use the Row/Column address.

I could try ADO, but in this case I am not sure how to tell Access to use
ADO, because the entire Sub consists of the strSQL = ... and the following:

CurrentDb.Execute strSql

How would I specifiy ADO or does that apply in this case?

 

[John Nurick]

Have you tried "$" instead of "!" ? This syntax usually works for me:

FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[sheetname$A1:E99]

Thanks for the response, John,
I tried changing the name of the work sheet to 'Eighty' to see if that was
the problem. It was not. If I name the range, it works fine. The issue is,
I can't depend on the person responsible for creating the sheet to put the
range name in it. That is why I was trying to use the Row/Column address.

I could try ADO, but in this case I am not sure how to tell Access to use
ADO, because the entire Sub consists of the strSQL = ... and the following:

CurrentDb.Execute strSql

How would I specifiy ADO or does that apply in this case?

Below is an SQL statment I am trying to get to work to pull data into Access
from Excel. The problem is with the range (FROM [80%!A3200] ). The
current syntax is the closest I can get.
The error returned is 3125 - Is not a valid name. Be sure it doesn't
contain invalid characters or punctuation....
Every other way I have tried returns 3131 Invalid syntax in FROM clause.
I was suspicious about the %, but it workd with a transferspreadsheet. I
really don't what to have to link it, I would rather just get it in this way.

strSql = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, " _
& "Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005
April'" _
& " FROM [80%!A3200] IN '' [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;];"

 

[Guest]

John,

No, I did not. I do remember now that you mention it that a $ will work.
In frustration, I changed my approach. I just do a link, then pull it from
the link to the table then delete the link. I was trying to avoid that
because I think that repeated linking and unlinking can bloat the FE mdb.
What are your thoughts on that?

Have you tried "$" instead of "!" ? This syntax usually works for me:

FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[sheetname$A1:E99]

Thanks for the response, John,
I tried changing the name of the work sheet to 'Eighty' to see if that was
the problem. It was not. If I name the range, it works fine. The issue is,
I can't depend on the person responsible for creating the sheet to put the
range name in it. That is why I was trying to use the Row/Column address.

I could try ADO, but in this case I am not sure how to tell Access to use
ADO, because the entire Sub consists of the strSQL = ... and the following:

CurrentDb.Execute strSql

How would I specifiy ADO or does that apply in this case?

Below is an SQL statment I am trying to get to work to pull data into Access
from Excel. The problem is with the range (FROM [80%!A3200] ). The
current syntax is the closest I can get.
The error returned is 3125 - Is not a valid name. Be sure it doesn't
contain invalid characters or punctuation....
Every other way I have tried returns 3131 Invalid syntax in FROM clause.
I was suspicious about the %, but it workd with a transferspreadsheet. I
really don't what to have to link it, I would rather just get it in this way.

strSql = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, " _
& "Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005
April'" _
& " FROM [80%!A3200] IN '' [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;];"

 

[John Nurick]

If the filename and sheet name don't change, AFAIK you don't need to
fiddle with the linked table, just make sure that there are no open
queries, recordsets etc. before you delete or move the file it is linked
to. But I wouldn't give up on Jet SQL; even though I can never remember
when to use $ and when ! , once I get a query working it seems to keep
working.

As for bloating front ends, I don't know. I tend to avoid the issue
entirely, by downloading the FE from the network at log-in and/or by
putting temporary tables in a temporary local MDB file that's deleted
after use.

John,

No, I did not. I do remember now that you mention it that a $ will work.
In frustration, I changed my approach. I just do a link, then pull it from
the link to the table then delete the link. I was trying to avoid that
because I think that repeated linking and unlinking can bloat the FE mdb.
What are your thoughts on that?

Have you tried "$" instead of "!" ? This syntax usually works for me:

FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[sheetname$A1:E99]

Thanks for the response, John,
I tried changing the name of the work sheet to 'Eighty' to see if that was
the problem. It was not. If I name the range, it works fine. The issue is,
I can't depend on the person responsible for creating the sheet to put the
range name in it. That is why I was trying to use the Row/Column address.

I could try ADO, but in this case I am not sure how to tell Access to use
ADO, because the entire Sub consists of the strSQL = ... and the following:

CurrentDb.Execute strSql

How would I specifiy ADO or does that apply in this case?

:

Below is an SQL statment I am trying to get to work to pull data into Access
from Excel. The problem is with the range (FROM [80%!A3200] ). The
current syntax is the closest I can get.
The error returned is 3125 - Is not a valid name. Be sure it doesn't
contain invalid characters or punctuation....
Every other way I have tried returns 3131 Invalid syntax in FROM clause.
I was suspicious about the %, but it workd with a transferspreadsheet. I
really don't what to have to link it, I would rather just get it in this way.

strSql = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, " _
& "Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005
April'" _
& " FROM [80%!A3200] IN '' [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;];"