SQL String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got code to build a SQL string programatically. I'm getting a 3125: Not
a valid name error on this line:

strSQL = "UPDATE tblPAC_PEN_DETAIL SET FileName = '" & strFileName & "'
WHERE FileName Is Null;"

strFileName is built with this line: strFileName = Dir$(strPath &
"*_Detail.csv")

Does anyone spot a syntax problem here?
 
Kirk said:
I've got code to build a SQL string programatically. I'm getting a 3125: Not
a valid name error on this line:

strSQL = "UPDATE tblPAC_PEN_DETAIL SET FileName = '" & strFileName & "'
WHERE FileName Is Null;"

strFileName is built with this line: strFileName = Dir$(strPath &
"*_Detail.csv")
Click to expand...


Can't tell from that. Try adding:
Debug.Print strSQL
so you can see the completed SQL statement.
 
I think I've found the problem. It appears there is a 64 character limit on
file name length. I don't know if that's an Access limitation, or a
limitation of the DIR function. The data from the file is never imported,
therefore (obviously) the SQL doesn't work.

So the next question - is there any way around this 64 character limit on
file names (other than shorten the length of the file name, which is beyond
my control)?
 
I thought the limit was 255 and I have no trouble using an
80+ character file name.

Is there any chance that the file name contains an
apostrophe?
 
I'm using Access 2007. Here's a file name that won't import:

2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.csv

Even using the import wizard, it tells me it cannot find the object
2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail. (truncates
the last 3 characters csv). Using the wizard, I'm browsing my way to this
file. This leads me to believe there must be a limitation of 64 characters
I thought the limit was 255 and I have no trouble using an
80+ character file name.

Is there any chance that the file name contains an
apostrophe?
--
Marsh
MVP [MS Access]

I think I've found the problem. It appears there is a 64 character limit on
file name length. I don't know if that's an Access limitation, or a
limitation of the DIR function. The data from the file is never imported,
therefore (obviously) the SQL doesn't work.

So the next question - is there any way around this 64 character limit on
file names (other than shorten the length of the file name, which is beyond
my control)?
Click to expand...
Click to expand...
 
Kirk said:
I'm using Access 2007. Here's a file name that won't import:

2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.csv

Even using the import wizard, it tells me it cannot find the object
2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail. (truncates
the last 3 characters csv). Using the wizard, I'm browsing my way to this
file. This leads me to believe there must be a limitation of 64 characters
on file names - I don't know what else to conclude.
Click to expand...


I have not worked with A2007 yet, so, if it is different
from earlier versions, I am unaware of the issue.

Sorry, maybe someone else knowa what's going on with your
problem.
 
Kirk, there apparently is a limit. Choose any table in your database and
try to export it as a text file, and give it a name longer than 64
characters. Access will tell you to change the name to a length of 64
characters or less. I assume the same limit applies to the import side
also, as you suspect. My test also AC2007.

Sorry bout that, UpRider

Kirk P. said:
I'm using Access 2007. Here's a file name that won't import:

2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.csv

Even using the import wizard, it tells me it cannot find the object
2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.
(truncates
the last 3 characters csv). Using the wizard, I'm browsing my way to this
file. This leads me to believe there must be a limitation of 64
characters
I thought the limit was 255 and I have no trouble using an
80+ character file name.

Is there any chance that the file name contains an
apostrophe?
--
Marsh
MVP [MS Access]

I think I've found the problem. It appears there is a 64 character
limit on
file name length. I don't know if that's an Access limitation, or a
limitation of the DIR function. The data from the file is never
imported,
therefore (obviously) the SQL doesn't work.

So the next question - is there any way around this 64 character limit
on
file names (other than shorten the length of the file name, which is
beyond
my control)?

Kirk P. wrote:
I've got code to build a SQL string programatically. I'm getting a
3125: Not
a valid name error on this line:

strSQL = "UPDATE tblPAC_PEN_DETAIL SET FileName = '" & strFileName &
"'
WHERE FileName Is Null;"

strFileName is built with this line: strFileName = Dir$(strPath &
"*_Detail.csv")
Click to expand...
Click to expand...
Click to expand...
 
UpRider again.
Having established that an UPDATE query won't work, I know that opening and
updating a DAO recordset with .addnew and .update will work with long file
names in AC2007. You'll have to try that route.

UpRider
 
OK, thanks for the confirmation. I'll have to go to plan B.

UpRider said:
Kirk, there apparently is a limit. Choose any table in your database and
try to export it as a text file, and give it a name longer than 64
characters. Access will tell you to change the name to a length of 64
characters or less. I assume the same limit applies to the import side
also, as you suspect. My test also AC2007.

Sorry bout that, UpRider

Kirk P. said:
I'm using Access 2007. Here's a file name that won't import:

2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.csv

Even using the import wizard, it tells me it cannot find the object
2007_accelrys_ROW-REGION_AllSites_AllLocales_Monthly_Jul_Detail.
(truncates
the last 3 characters csv). Using the wizard, I'm browsing my way to this
file. This leads me to believe there must be a limitation of 64
characters
I thought the limit was 255 and I have no trouble using an
80+ character file name.

Is there any chance that the file name contains an
apostrophe?
--
Marsh
MVP [MS Access]


Kirk P. wrote:
I think I've found the problem. It appears there is a 64 character
limit on
file name length. I don't know if that's an Access limitation, or a
limitation of the DIR function. The data from the file is never
imported,
therefore (obviously) the SQL doesn't work.

So the next question - is there any way around this 64 character limit
on
file names (other than shorten the length of the file name, which is
beyond
my control)?

Kirk P. wrote:
I've got code to build a SQL string programatically. I'm getting a
3125: Not
a valid name error on this line:

strSQL = "UPDATE tblPAC_PEN_DETAIL SET FileName = '" & strFileName &
"'
WHERE FileName Is Null;"

strFileName is built with this line: strFileName = Dir$(strPath &
"*_Detail.csv")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top