INCORRECT ADO CONNECTION STRING FROM VISUAL BASIC (MSACCESS)

I am trying to open an EXCEL file into an ADO Recordset from Access like so :

    Set db = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    db.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & CurrentProject.Path & "\" & mFilename & ".xls;" & _
           "DefaultDir=" & CurrentProject.Path
     
    Call rs.Open("SELECT * FROM " & mFilename, db, adOpenStatic, adLockReadOnly)

This doesn't work, any idea what the syntax is like??
const71Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SethiConnect With a Mentor Commented:
Use the following:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
 
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text

TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
0
 
Ryan ChongCommented:
See:
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForExcel

what's the CurrentProject.Path returned? I suspect your path returned may be wrong? how about using app.path ?
0
 
const71Author Commented:
this is MSAccess. The problem is with the SELECT statement
0
 
const71Author Commented:
Beautiful..Works like a charm!! Thanks
0
 
SethiCommented:
:-) Anytime
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.