does OpenRowset work in Access?

[CJ_London]

I'm trying to determine if OpenRowset works from within an Access (2010) query. I can find plenty of posts about using it in SQL server to access an MS Access db but not about using it in Access.

An example of trying to access a .csv:

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp\','select * from TmpData.csv')

generates a syntax error in from clause error.

Have I got the syntax wrong or does OpenRowset not work? Alternatively is there another way other than linking to the file (which is what I do at the moment)?

Many thanks in advance

 

[Guus2005]

openrowset is apperently a SQL server command. to make this work you could try to use a passthrough query.
acces and the odbc leave it alone then and the SQL statement is passed through directly to the SQL server database which is what you wanted i believe.

HTH

 

[CJ_London]

thought that was probably the case - but nice to have it confirmed!

 

[Pat Hartman]

If you link the .csv file, you can reference it as if it were a table. You don't need any specialized syntax.

 

[CJ_London]

Hi Pat,

thanks for the response- any suggestions for the syntax?

 

[ChrisO]

Something to try...

Sub TestIt()
    Dim strSQL As String
    
    [color=green]' With header information, use the header names:-[/color]
    strSQL = " SELECT *" & _
             " FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\Temp\;].[TmpData.csv]" & _
             " ORDER BY [Last Name], [First Name], [Phone]"

    [color=green]' Or:-[/color]
    strSQL = " SELECT" & _
             "     [First Name]," & _
             "     [Last Name]," & _
             "     [Phone]" & _
             " FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\Temp\;].[TmpData.csv]" & _
             " ORDER BY [Last Name], [First Name], [Phone]"
             
             
             
     [color=green]' Without header information, use an alias:-[/color]
    strSQL = " SELECT" & _
             "     F1 As [First Name]," & _
             "     F2 As [Last Name]," & _
             "     F3 As [Phone]" & _
             " FROM [Text;FMT=Delimited;HDR=No;DATABASE=C:\Temp\;].[TmpData.csv]" & _
             " ORDER BY F2, F1, F3"
             
    [color=green]' In each case:-[/color] 
    With CurrentDb.OpenRecordset(strSQL)
        Do Until .EOF
            Debug.Print ![Last Name], ![First Name], ![Phone]
        
            .MoveNext
        Loop
    End With

End Sub

Chris.

 

[CJ_London]

Thanks Chris,

Just what I was looking for - gets round the problem of having an import specification

 

[ChrisO]

No problems.

I should have mentioned that the version commented as "Without header information, use an alias:-" I got from site member LPurvis. The others were just a rehash of that idea.

Chris.