Solved

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

912 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

24 Experts available now in Live!

Get 1:1 Help Now