Solved

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

Posted on 2009-05-08
3
426 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

0
Comment
Question by:DataTrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 200 total points
ID: 24336981
In the Excel VB Editor - Tools menu / References.  Have you selected the appropriate libraries?
VBE-Reference-Library.jpg
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 24337027
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.
Regardds,
Rory
0
 

Author Closing Comment

by:DataTrain
ID: 31579446
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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