Solved

INCORRECT ADO CONNECTION STRING FROM VISUAL BASIC (MSACCESS)

Posted on 2004-09-01
5
214 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 50

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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

840 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