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

ADO Connection from Excel?

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
Washcare
Asked:
Washcare
  • 3
  • 3
1 Solution
 
drilusCommented:
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
 
WashcareAuthor Commented:
drilus

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

Try this example from Microsoft:
http://support.microsoft.com/kb/195951
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WashcareAuthor Commented:
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
 
drilusCommented:
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
 
WashcareAuthor Commented:
Many thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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