Solved

INCORRECT ADO CONNECTION STRING FROM VISUAL BASIC (MSACCESS)

Posted on 2004-09-01
5
211 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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameter in sql procedure 9 56
Please explain "Promise Pattern" and how it's used 2 54
Copy a row 12 57
can't find the executable in Simulator 1 75
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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

943 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now