• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

2 Solutions
Jerry PaladinoCommented:
In the Excel VB Editor - Tools menu / References.  Have you selected the appropriate libraries?
Rory ArchibaldCommented:
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!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now