From MS Excel to MS Access

[priyam]

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

[Bullschmidt]

Here is something similar I had put together about adding .CSV data to an Access database online.

You can connect to a csv file (which can be opened in Excel and which an Excel file can be converted into) in good form just as you can to a regular database.

The .csv file needs to be uploaded to the server.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForTextFiles

And based on the above link realize that the actual filename does NOT go in the connection string - rather it goes in the SQL statement (definitely a little tricky).

And since you want to edit the Access records well that would be a little more complicated but hopefully could be done by doing something like opening the Access record in question and writing to it within each loop iteration of looping through the .csv file.

[priyam]

If i create a connection using the following code it gives an error

Set conn=Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("test.xls") & ";" & _
"Persist Security Info=False;" &_
"Extended Properties=""Excel;HDR=Yes"
conn.Open(sConnection)

ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

The test.xls file is stored in the same folder on server in which the file containing the above code is stored

[priyam]

I have also tried without using a variable(sConnection) to create connection,it still gives the same error.

When i use he following code i get a different error

Set conn=Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("test.xls") & ";" & _
"Persist Security Info=False;" &_
"Extended Properties='Excel;HDR=Yes'"
conn.Open(sConnection)

Error i get is something like

Microsoft JET Database Engine error '80004005'

Could not find installable ISAM.


Please help!!

[priyam]

Hey finally i was able to figure out the code to export data from excel to access :)
Following is the code


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