|
| |
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
 |
Can an asp connection be made to a linked table? - 4/17/2008 13:07:47
Is it possible to link an Excel spreadsheet to an Access db, then open a connection to that linked table theough Access?
I've tried a few possible scenarios and it errors out because of the $ symbol in the link.
Dave
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 14:29:15
You can access the Excel sheet itself directly just like the db. If an Access DB connection looks like this:
myAccessDSN="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/myDB.mdb")
myExcelDSN = "DRIVER=Microsoft Excel Driver (*.xls); DBQ=" & Server.MapPath("/fpdb/mySS.xls")
That help get moving in the right direction?
EDIT.: And you'd treat worksheets like tables as in:
"Select * From [Book1]"
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 14:40:00
Follow-up
The main db table resides in Access. There is one table that contains the current inventory, one entry per item. The newest records reside in excel which we receive monthly from the supplier.
My thought was:
Have the excel file uploaded, run a series of asp queries to update the data in the Access table from excel, then append any new items and then delete the items that are no longer offered.
I see your post on connecting to excel. Is this the best approach for what I am trying to accomplis? I would have to open both tables (in access & excel) correct? Then run the 3 asp queries, correct?
Thanks
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 15:01:03
That's how I typically do it. I generally will read in all the Excel data into an array. Then open the Access Db and update / insert as necessary.
However, if you're using .NET, I have found it easiest to save the Excel file as a CSV and use something like this:
Dim filetoread As String
filetoread = Server.MapPath("/fpdb/filename.csv")
Dim filestream As StreamReader
filestream = File.OpenText(filetoread)
Dim readcontents As String
readcontents = filestream.ReadToEnd()
And remeber, there are a few things differnt with Excel files. One of the things that stumped me when I started with them is not realizing that the Excel driver determines what the field type is by the first 5 cells of a column. So be sure your first 5 cells have the proper data in there or reorganize the data so that it does.
And it's easier if you remove column titles and just reference the column by it's ordinal as in Column A becomes rs(0). (That right there might have confused you?)
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 15:21:26
Is there an easy way to tell if the server is a .net server? It is not mine.
The info you sent looks good about excel. Now I have to make it work :-)
Thanks
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 16:04:15
quote:
Is there an easy way to tell if the server is a .net server?
One simple way is to take a blank (or minimal) .htm page and put "Hello World" on it. Rename it with an .aspx extension and see if it loads without error. Generally that'll test about 98% of all installs.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 16:06:00
I am getting an error with this code:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Data'. Make sure the object exists and that you spell its name and the path name correctly.
with this code
'ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.Mappath("../fpdb/LMS.mdb")
ConnStr = "DRIVER=Microsoft Excel Driver (*.xls); DBQ=" & Server.MapPath("../fpdb/NewIB.xls")
set rs = Server.CreateObject("ADODB.Recordset")
mySQL="Select * From [Data]" ' Data is the sheet tab name
rs.open mysql, ConnStr
myData = rs(0)
Response.Write myData
Any ideas?
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 16:08:44
I saved this as hello.aspx and it opened, I guess that's a good thing?
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<p>Well</p>
</body>
</html>
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Can an asp connection be made to a linked table? - 4/17/2008 16:16:40
Got it!!
[Data] = [Data$]
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
