Solved

MS Access 2013 Linking to SQL Tables - DAO No Longer Works

Posted on 2013-05-22
13
1,985 Views
Last Modified: 2013-05-22
Years ago I created an MS Access application that dynamically links to SQL tables for one of my clients.   The client has upgraded his computers to Windows 8 64 bit with MS Office 2013 64 bit.

The attached code used DAO to dynamically link the Access application to a list of SQL tables stored in an Access table.

I need to replace the DAO with something that will work with Access 2013.  Any code snippets would be appreciated.

Thanks,

Tim Sledge
DOAToConvert.txt
0
Comment
Question by:TimSledge
  • 8
  • 5
13 Comments
 
LVL 57
ID: 39188470
What doesn't work?  It should work fine as long as you referenced the DAO lib.

Jim.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188475
DAO is not supported by MS Access 2013.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39188555
Sure it is.   Are you talking about a web database?

If not, the default reference "Microsoft Office 15.0 Access database engine Object" contains the DAO model.

Jim.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188600
No, it is not a web database.  I changed the reference from DAO 3.6 to Microsoft Office 15.0 Access database engine Object, and now that I think about it the DAO lines do compile.

However, the SQL tables will not link using the DAO code.

This is MS Office 2013 64 bit.  I'm wondering if this could be about a driver.  I've heard there are problems using MS Access 2013 in the 64 bit version.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188613
I'm wondering if I need to change something in the line below.
 
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabaseName & ";UID=" & strUserName & ";PWD=" & strPWD
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188629
When I try to run the code to link the SQL tables I get the error message:  "Could not find installable ISAM.3170.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 57
ID: 39188650
<<This is MS Office 2013 64 bit.  I'm wondering if this could be about a driver.  I've heard there are problems using MS Access 2013 in the 64 bit version. >>

 Yes, there are problems in that because it is a 64 bit app, it is going to make 64 bit calls.  You you need 64 bit ODBC drivers, API calls need to be modified, etc.

  But 64 bit ODBC drivers should be part of the OS, so exactly what is the error/issue that your seeing?   And what version of SQL are you trying to connect to?

Jim.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188666
Disregard the "Could not find installable ISAM 3170" comment.  That happened only when I removed ODBC from the connection string.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188713
More info...

I can override the startup code and open a form I created to start the code that links the SQL tables (SQL 2005), and it works.

I think  I was using a startup macro to run the linking code, but I can't find Macos in the Access 2013 Object list.  Does Access 2013 still use Marcos?

I think the problem is that I had a macro set to run on startup that loaded the SQL tables, but the macro is not running in Access 2013.
0
 
LVL 57
ID: 39188728
<<Does Access 2013 still use Marcos?>>

 Yes, macro's are still available and work the same as before.  The navigation pane is a bit different to work with then the old database container if your coming from Access 2003 or earlier.

  My sure you click "All Objects" to see everything.

Jim.
0
 
LVL 4

Author Closing Comment

by:TimSledge
ID: 39188929
Learning that the "Microsoft Office 15.0 Access database engine Object" contains the DAO model reference was crucial to my finding the solution to my problem.  However, it was only the first step.

Something in Access 2013 was handing some of my code differently that in Access 2002.  I did a code workaround and got it working.

Thanks for your dialogue and help.

Tim Sledge
0
 
LVL 57
ID: 39188938
You'll find that Access 2007 and up want things done in a very specific way sometime over past versions.

As part of building out web databases and adding to macro capabilities, it seems Microsoft tightened up on the way VBA and Access interact.

Jim.
0
 
LVL 4

Author Comment

by:TimSledge
ID: 39188972
After Access 2007 was introduced I moved almost exclusively to developing ASP.Net/SQL Server applications so I have not kept up with my MS Access skills.

Thanks again for your help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

22 Experts available now in Live!

Get 1:1 Help Now