Solved

ADO Connection from Excel?

Posted on 2011-03-23
6
423 Views
Last Modified: 2012-05-11
Good Afternoon

Is it possible to create an ADO Connection from Excel to an Access database? The attached code (a .asp) alows me to display the last record of the database on a web page, so is it possible to use the same logic as a connection within Excel?

Thanks
<html>
<head>
<title>Database</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 				'Holds the Database Connection Object
Dim rsdatabase			'Holds the recordset for the records in the database
Dim strSQL				'Holds the SQL query for the database



'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Comments.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=database"

'Create an ADO recordset object
Set rsdatabase = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT TOP 1 tblComments.S1, tblComments.S2, tblComments.S3, tblComments.Date, tblComments.EN, tblComments.CW, tblComments.Status, tblComments.SSO FROM tblComments ORDER BY ID_no DESC;"

'Open the recordset with the SQL query 
rsdatabase.Open strSQL, adoCon

'Loop through the recordset
Do While not rsdatabase.EOF
	
	'Write the HTML to display the current record in the recordset
	Response.Write ("<br>")
	Response.Write (rsdatabase("S1"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("S2"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("S3"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("Date"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("EN"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("CW"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("Status"))
	Response.Write ("<br>")
	Response.Write (rsdatabase("SSO"))
	Response.Write ("<br>")


	'Move to the next record in the recordset
	rsdatabase.MoveNext

Loop

'Reset server objects
rsdatabase.Close
Set rsdatabase = Nothing
Set adoCon = Nothing
%>
</body>
</html>

Open in new window

0
Comment
Question by:Washcare
[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
  • 3
6 Comments
 
LVL 5

Expert Comment

by:drilus
ID: 35199130
Yes. The key will be the connection string.

For Excel:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='Book1.xls';Extended Properties=Excel 8.0;HDR=YES;

Excel also uses the first row as headers for Columns.
0
 

Author Comment

by:Washcare
ID: 35199187
drilus

Would I use this within Excel to create a new connection or do I use  it within an .asp page?
0
 
LVL 5

Expert Comment

by:drilus
ID: 35199254
within an .asp page.

Try this example from Microsoft:
http://support.microsoft.com/kb/195951
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Washcare
ID: 35199291
drilus

The example looks at updating the .asp page from Excel, I would like Excel to be updated from the data within the .asp page?

0
 
LVL 5

Accepted Solution

by:
drilus earned 500 total points
ID: 35199592
Well, this is the best I can do. http://www.thescarms.com/dotnet/AspExport.aspx

I know it's possible but I don't know the asp syntax well enough to help you further.
0
 

Author Closing Comment

by:Washcare
ID: 35365372
Many thanks
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP and Extracting XML 7 46
Making exceptions for ValidationAttribute 2 43
auto play video on web page 4 59
change site header 8 31
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

734 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