Can an asp connection be made to a linked table? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

DaveKstl -> 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 -> 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]"



DaveKstl -> 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 -> 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?



DaveKstl -> 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 -> 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.



DaveKstl -> 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 -> 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 -> RE: Can an asp connection be made to a linked table? (4/17/2008 16:16:40)

Got it!!

[Data] = [Data$]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.234375