Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-05-08
3
Medium Priority
?
445 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 800 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 200 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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