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

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

Who is Participating?
Jerry PaladinoConnect With a Mentor Commented:
In the Excel VB Editor - Tools menu / References.  Have you selected the appropriate libraries?
Rory ArchibaldConnect With a Mentor Commented:
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.
DataTrainAuthor 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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.