ADO connection query fail when using UNC in FROM clause

[benatom]

I have an ADO connection string like this:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strPath & "\;Extended Properties=""text;HDR=Yes;FMT=Delimited
(,)"";Persist Security Info=False"

if strPath = "X:\FolderData"

the query

Select f1, f2, f3 From X:\FolderData\data.csv"

will open.

if strPath = "\\ServerName\FolderData\"

the query:

Select f1, f2, f3 From \\ServerName\FolderData\data.csv

will fail [syntax error in FROM clause]

I would like to be able to use the second method. Suggestions?

 

[hans.updyke]

I have an ADO connection string like this:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strPath & "\;Extended Properties=""text;HDR=Yes;FMT=Delimited
(,)"";Persist Security Info=False"

if strPath = "X:\FolderData"

the query

Select f1, f2, f3 From X:\FolderData\data.csv"

will open.

if strPath = "\\ServerName\FolderData\"

the query:

Select f1, f2, f3 From \\ServerName\FolderData\data.csv

will fail [syntax error in FROM clause]

I would like to be able to use the second method.  Suggestions?

Your connection string includes the path. Use only the file name
(without the path) after FROM in your SELECT statement.

This example works using a slightly different connection string

Public Sub ADO_with_UNC_path()
Dim cn As ADODB.Connection
Dim strConString As String
Dim strPath As String
Dim strSql As String
Dim rs As ADODB.Recordset

strPath = "\\tsclient\Local\home\hans\"

Set cn = New ADODB.Connection
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strPath & ";Extended Properties='text;HDR=Yes;FMT=Delimited';"
cn.Open strConString

strSql = "SELECT * FROM CalendarEntries.csv;"
Set rs = New ADODB.Recordset
rs.Open strSql, cn
'uncomment next line to examine recordset from Immediate Window (Ctrl
+G)
'Stop
rs.Close

Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[benatom]

Thanks! That works well. Now I can stop mucking with this and move on.