converting excel to ms access - asp & adding fields

chris_angell

03-06-2007, 12:09 PM

Hi guys I am trying to upload an excel spread sheet, read the information from this and then convert all this information into an asp database.

I have the main concept on how to open read, add info in MS Access, but i am a little confused on how to read the excel info (ie how many fields and then create these in ms access).. if you know any links that will be good.. my knowledge on create new fields via SQL in minimal..

this is how i think i can do it..

upload my file, take the name and then

Set objExcel = Server.CreateObject("Excel.Application")
objExcel.Workbooks.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ= " & Server.MapPath('" & my excel file & "')

i am not to sure how to determine how many fields are in ?? any ideas on this...

once i have done this create these in asp ms access

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= " & Server.MapPath('" & my access bd & "')

HOW DO I CREATE FIELDS ???

Once i have created both, i take it i can loop through the excel and then add to the access... ie

While not objRecords.EOF
objExcel.ActiveSheet.Range("name" & i).Value = Recordset("ID")
i = i + 1
Wend

after this save
close connections and update..

but i am just stuck on filding the excel info and creating new fields in ms access??

any help would be great

many thaks chris

BarrMan

03-06-2007, 04:41 PM

Hey, You'll need to do the followings:
1. Open your excel file.
2. Select the fields you want to convert.
3. Click "Insert" on the menu --> Name --> Define.
4. Write down the name of the workspace (this name will be used later on the sql query).
5. Save.

After you've done this, you can simply access your excel file by using the following query:
SQL = "SELECT * FROM your_workspace_name"

Hope that helped.

chris_angell

03-06-2007, 05:32 PM

thanks for the post.. but i am trying to let a client upload an excel spread sheet, from local machine to the server, and then using asp to take the info from excell and put it into an ms access with maching fields, ?? and also if the ms access needs it extra fields,

i hope to create new fields using SQL.

i hope this make things a little more clear..

cheers chris

SSJ

03-07-2007, 07:15 AM

visit this url:
http://www.15seconds.com/howto/pg000950.htm

Hope this will help you..

Ebaad

03-19-2007, 01:28 AM

Hi,

Just wanted to ask for some help regarding the following piece of code, I'm trying to read from the excel file and update the data into and access database. Reading from excel file works fine and I can display it on the web in HTML but when I try to update the recordset in the access database it gives me an error.
Following is the error and the code, I would really appreciate if you can help me with this.
ERROR:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/mgma/loadxls.asp, line 58

CODE:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rsAdd
Dim rsAdd_numRows

Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tRDATA"
'rsAdd.CursorType = 2
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
'rsAdd.AddNew

rsAdd_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<%
Dim objConn, objRS, strSQL
Dim x, curValue, y

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("EXCEL_REPORTS\2003_04_05_Phy_Comp.xls") & "; "
strSQL = "SELECT * FROM A1:Q10000"
Set objRS=objConn.Execute(strSQL)
%>
<table border="0">
<tr>
<%
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Do Until objRS.EOF
'rsAdd.MoveFirst
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue=" "
End If
curValue = CStr(curValue)
y = y &","& curValue
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
arrMy = split(y, ",")
'+++++++++++++++++++
rsAdd.AddNew
For x=1 to rsAdd.Fields.Count-1
rsAdd.Fields(x).value=arrMy(x)
next
rsAdd.Update
'+++++++++++++++++++
objRS.MoveNext
y = " "
Loop
%>
</tr>
</table>
</body>

</html>
<%
rsAdd.Close
Set rsAdd = Nothing
%>

SSJ

03-19-2007, 10:57 AM

Go through following URLs:

http://www.issociate.de/board/post/233301/Multiple-step_OLE_DB_operation_generated_errors..html
http://bugs.mysql.com/bug.php?id=3855

Hope this helps...