Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INCORRECT ADO CONNECTION STRING FROM VISUAL BASIC (MSACCESS)

Posted on 2004-09-01
5
Medium Priority
?
222 Views
Last Modified: 2013-11-25
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??
0
Comment
Question by:const71
  • 2
  • 2
5 Comments
 
LVL 55

Expert Comment

by:Ryan Chong
ID: 11959416
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
 

Author Comment

by:const71
ID: 11959829
this is MSAccess. The problem is with the SELECT statement
0
 
LVL 18

Accepted Solution

by:
Sethi earned 2000 total points
ID: 11960102
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
 

Author Comment

by:const71
ID: 11966698
Beautiful..Works like a charm!! Thanks
0
 
LVL 18

Expert Comment

by:Sethi
ID: 11969336
:-) Anytime
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question