How can I connect to a database from Excel and use the commands I use in Access?

Posted on 2009-05-08
Last Modified: 2013-11-27
CodeSnippet 1 is the solution to a previous question and enables me to connect to a SQL Server 2005 database from an Excel 2007 macro but requires me to write SQL. In Access 2007 I am used to writing code like CodeSnippet 2. When I try to use similar code in Excel it crashes at "DoCmd.TransferDatabase" Runtime Error 2075 - The operation requires an open database OR Runtime error 2046 - The command or action 'TransferDatabase' isn't available now. Am I trying to do something in Excel that is only possible in Access? If not, can you see where I am going wrong? Thanks.
CodeSnippet 1:

Dim con As ADODB.Connection

Set con = New ADODB.Connection

con.Open "Driver={SQL Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;"

con.Execute "Your SQL HERE"

CodeSnippet 2:

Dim sConnect As String

Dim sLocalTable As String

Dim sVisionTable As String

Dim DB As DAO.Database

Dim tCust As DAO.Recordset

Dim tCommon As DAO.Recordset


Dim PlanID As String

Dim custProjectAccountant As String

Dim custContractType As String

Dim custPlanStage As String

Dim custPlanType As String

Dim custProposalStatus As String

'These are all NULL aprt from PlanID so no need to update them

MsgBox ("About to add a New record to PlanningCustomTabFields in 'TestSample' database on local machine.")


sConnect = "ODBC;Driver={SQL Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;"

sLocalTable = "PlanningCustomTabFields"

sVisionTable = "PlanningCustomTabFields"

DoCmd.TransferDatabase acLink, "ODBC Database", sConnect, acTable, sVisionTable, sLocalTable

Set DB = CurrentDb

Set tCust = DB.OpenRecordset("PlanningCustomTabFields")

Set tCommon = DB.OpenRecordset("T_Common")


PlanID = tCommon![PlanID]

tCust.AddNew               } I would prefer to

tCust![PlanID] = PlanID    } use these commands

tCust.Update               } rather than SQL statements

MsgBox ("Custom record has been added")

Open in new window

Question by:DataTrain
LVL 16

Accepted Solution

Jerry Paladino earned 200 total points
Comment Utility
In the Excel VB Editor - Tools menu / References.  Have you selected the appropriate libraries?
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
Comment Utility
DoCmd requires an Access Application instance to have been created.
Using the ADO code from snippet 1 you can open a table recordset using simply the tablename and then manipulate the records as you do in the second code snippet. You don't have to use SQL.

Author Closing Comment

Comment Utility
I did not have the correct selections in Tools/References, but your point, Rory, was also helpful. Thanks al ot. I will enjoy the weekend now!

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

6 Experts available now in Live!

Get 1:1 Help Now