Syntax Error in FROM Clause 4

[Swi]

I am reading DBF Files and I keep getting the above error. I think I have narrowed the problem to the size of the DBF file name.

For example, when I feed a file name like 30844 C AHA AARP.dbf to ADO for the connection it bombs out with the error but when I feed in a smaller file name like Testit.dbf it works fine. Any ideas?

P.S. Here is my code:

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";Extended Properties=dBASE 5.0;User ID=;Password="
List1.Clear
List2.Clear
On Error GoTo errhandler
rs.Open FileName, conn, adOpenForwardOnly, adLockReadOnly, adCmdTable

Swi

 

[Sheco]

You mean if you use 2 identical copies of a DBF file where the only difference is the filename?

 

[Swi]

Yes. I also forgot to mention that the DBF file names that are throwing the error have spaces in the file names. Thanks for the response.

Swi

 

[zarkon4]

Have you tried .open chr$(34) & FileName & chr$(34)? Or
maybe a "'" & filename & "'

 

[Swi]

I will give that a shot tomorrow morning and let you know if it worked.

Swi

 

[LostInCode]

Maybe the spaces and not length? It works for me as you show.

try to use the short name

Private Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" (ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Public Function ShortPathName(LongPath As String) As String
    Dim ret         As Long
    Dim sPathName   As String
    
    sPathName = Space$(255)
    ret = GetShortPathName(LongPath, sPathName, Len(sPathName))
    If ret > 0 Then ShortPathName = Left$(sPathName, ret)
End Function

DBName =ShortPathName("30844 C AHA AARP.dbf")

 

[tsdragon]

Try enclosing the file name in square brackets:
[tt] [this is a long filename with spaces] [/tt]


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[Swi]

Thank you all for the tips. The square brackets worked.

Swi