SQL String

[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?

 

[Marshall Barton]

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")

Can't tell from that. Try adding:
Debug.Print strSQL
so you can see the completed SQL statement.

 

[Guest]

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)?

 

[Marshall Barton]

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?

 

[Guest]

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)?

 

[Marshall Barton]

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.

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.

 

[UpRider]

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

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")

 

[UpRider]

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

 

[Guest]

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

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

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")