We help IT Professionals succeed at work.

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

560 Views
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
 
'DECLARATIONS
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.")
 
'CONNECT TO PLANNING CUSTOM TAB FIELDS
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")
 
tCommon.MoveFirst
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

Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.