Export data from Excel to MS Access

[priyammaheshwari]

Hi all,

I have a MS Access database in which requests made by people is stored,but a coulmn for cost is left empty which is suppose to be populated by the administrator.Now the administrator wants that there should be some functionality by which he would just have to upload an excel file containing the name and cost per person and the cost should be populated in access database against the respective name of people.
I am not able to figure out how i can use ASP to add this functionality to the website.I would be grateful if you could help.

Thanx

[priyammaheshwari]

Hi all,

I have a MS Access database in which requests made by people is stored,but a coulmn for cost is left empty which is suppose to be populated by the administrator.Now the administrator wants that there should be some functionality by which he would just have to upload an excel file containing the name and cost per person and the cost should be populated in access database against the respective name of people.
I am not able to figure out how i can use ASP to add this functionality to the website.I would be grateful if you could help.

Thanx

[jhardman]

That is a tricky one. It would be easier if he typed his data into the ASP app and that updated both his excel sheet and the access db.

However, if that isn't an option, do an upload and open the excel file using an excel db driver, get your info and put it in the access db. Let me know if you need any help with the actual code.

Jared

[priyammaheshwari]

Hi all,

I have a MS Access database in which requests made by people is stored,but a coulmn for cost is left empty which is suppose to be populated by the administrator.Now the administrator wants that there should be some functionality by which he would just have to upload an excel file containing the name and cost per person and the cost should be populated in access database against the respective name of people.
I have figured out how to pick up data from excel sheet and populate access's respective fields with it,but i am not able to upload a excel file on the server.

Please if any body could give me a simple code to upload a excel file onto the server i would be really grateful.

I was wondering if instead of uploading the file i could directly pick up data from excel file on client's computer .please suggest some changes in the following code to do the same.

Set conn=Server.CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("test.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM [Sheet1$]"
rs.open strSQL, conn, 3,3,1
rs.MoveFirst
 

Thanx

[Ugene]

hey priyammaheshwari.. are u also trying to read your figures from microsoft excel ? that code is unable to run or what ?because i am also trying to read my figure from excel...

[jhardman]

Hi all,

I have a MS Access database in which requests made by people is stored,but a coulmn for cost is left empty which is suppose to be populated by the administrator.Now the administrator wants that there should be some functionality by which he would just have to upload an excel file containing the name and cost per person and the cost should be populated in access database against the respective name of people.
I have figured out how to pick up data from excel sheet and populate access's respective fields with it,but i am not able to upload a excel file on the server.
Thanx

Priya,

ASP files are not given access to client's file system for security reasons, so no, you can't do that. But uploading a file is easy enough, this article in the howto section explains how. Why are you unable to upload files? Do you have permission to save files in a temp folder on the server?

Jared

BTW, this really is a continuation of the question you were asking earlier, so I merged them.