Solved

VB6 DAO Opendatabase Connect for Excel 2007

Posted on 2013-11-13
9
3,482 Views
Last Modified: 2013-11-13
Hello,

I am in trouble !!!!

I have the following code:

Dim toDB as DAO.Database
Set toDB = DBEngine.OpenDatabase("C:\mydb.xls", False, False, "Excel 8.0;")

Open in new window


it works fine my Excel used to be Excel 2003 and my program that is ran in a company for over 5 years works with no headache !!!

We upgraded all over to Excel 2007 and obviously C:\mydb.xls is now C:\mydb.xlsm
I am not able to get the above instruction to work no matter what I did. I change the connect string to "Excel 12.0;" and it is not working I am getting

Error 3170 Could not Find Installable ISAMS

How to get DAO to access Excel 2007 .xlsm

Rgds/gowflow
0
Comment
Question by:gowflow
  • 3
  • 3
  • 3
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39645914
I don't have VB6 here, not sure if this would work:
Data Source=c:\data\myfile.xlsm;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0 Macro;HDR=YES";

Open in new window

0
 
LVL 29

Author Comment

by:gowflow
ID: 39645945
No this is ADO !!! I need DAO

gowflow
0
 
LVL 2

Expert Comment

by:corvetteguru
ID: 39645946
I think you need to upgrade a bit... DAO is a dead... it was dying in 2000, and ADO put it to rest for good.

You can reach into Excel 2007 using ADO and "Provider=Microsoft.ACE.OLEDB.12.0", but I suspect DAO is a dead end.

Also, at this stage, you may want to upgrade from VB6 to VB2013. It's a lot more powerful and efficient.

As much as I enjoyed using VB6, I find VB2010 much more satisfying. It does so much more so much easier. And 2013 is even more refined.

Just food for thought... unless there are other restrictions, of course.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
LVL 29

Author Comment

by:gowflow
ID: 39646094
@corvetteguru
wow wow not that fast !!!!

tks your Upgrade suggestions but not that easy !!!!

I have a full fledged company that is working on this software not just a tinny 2 line code !!!

I would appreciate a suggestion on DAO as to start not just DAO is dead end. I simply need a confirmation dead solid that DAO WILL NOT OPEN Excel 2007 point blank. then and only then can think of alternatives.

Appreciate all the comments and suggestions that are there.
gowflow
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39646107
sorry, the good old DAO ... I overlooked that, you are not using ADO.

well, I have to confirm that there is no ISAM connector for the .xlsm (or .xlsx) files for DAO.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39646115
the above posted, I did look up this page:
http://support.microsoft.com/kb/209805
0
 
LVL 2

Assisted Solution

by:corvetteguru
corvetteguru earned 250 total points
ID: 39646426
DAO was killed off by ADO... It's that simple, really. Sorry to be the bearer of bad news...

DAO is very limited. ADO is still in use!

I found this:

http://www.excelguru.ca/forums/showthread.php?427-DAO-recorset-with-Excel-2007

but I would still use ADO...
0
 
LVL 29

Author Closing Comment

by:gowflow
ID: 39646527
Tks to both of you angelIII and corvetteguru for your replies and help on this issue.

I found the solution 'thank god' !!!!! it took me some 35 changes and my program is back on its feet running  great with DAO and Excel 2007 and this thanks to this link

http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

For whoever is having similar problem here is a summary:

If you are needing to retrieve the data from an Excel 2007 spreadsheet, instead of the reference being to the “Microsoft DAO 3.51 (or 3.6) Object Library, it needs to be to the Microsoft Office 12.0 Access database engine Object Library and the code that follows in this article needs to be modified as indicated.

and for sure the whole article talks about mixed data type and the way to go around it. It goes without saying that the syntax is not 100% the same and need some finetuning you need to add  DAO.Database in all your database declarations and DAO.Recordset for all the recordset as well when formerly you could simply declare them as Database and Recordset.

In conclusion for sure ADO is the next generation but when you have a large code 5 modules 25 forms and couple of thousand lines of code it is not just simple to migrate overnite.

Tks to both your help and appreciate all the comments there.
Rgds/gowflow
0
 
LVL 2

Expert Comment

by:corvetteguru
ID: 39646567
glad I could help!

As for migrating, there might be some tools to convert VB6 to the later .NET versions.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 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