using vbscript in asp to import excel into access

kaya · March 28th, 2007, 09:13 AM

Hi guys!

I was trying the asp code inserting into my project. And I had problemes with "adOpenKeyset, adLockOptimistic, adCmdTable"

I even don't know what it is those adxxxx.
The error message he gives is "name adOpenKeyset is not declared.

But if I watch the code there is no place where he gets declared.

Could someone please helpe me with this? It's quiet urgent cause I have a deadline on this for tomorrow so i'm kindly screwd

indiacam · April 26th, 2007, 04:35 PM

Quote:

quote:Originally posted by Bob Bedell
Turns out the Jet OLE DB provider is smart enough to handle the whole exchange.
<% @Language = VBScript %>
<% Option Explicit %>

<html>
<head>
    <title>Import Data From Excel into Access</title>
</head>
<body>
<%
    Dim cnnExcel, rstExcel
    Dim cnnAccess, rstAccess
    Dim strExcelFile
    Dim strAccessFile

    strExcelFile = Server.MapPath(".") & "\ExcelExportData.xls"
    strAccessFile = Server.MapPath(".") & "\AccessImportData.mdb"
......................................

THANKS A LOT BOB!
This code worked without any hassle. Also I could import data in SQL Table also.

I kept COLUMN HEADINGS (in my Excel table) and used "HDR=YES" in EXCEL connection STRING. Without a single error the code worked with following code....

Code:

Do Until rstExcel.EOF
    With objRS
        .AddNew
        .Fields("DHSID") = rstExcel.Fields("dhsid").Value
        .Fields("EMPID") = rstExcel.Fields("empid").Value
        .Fields("Name") = rstExcel.Fields("name").Value
    End With
    rstExcel.MoveNext
Loop
objRS.UpdateBatch
Response.Write "Records written from Excel to SQL<p>"

BOB! I LOVE YOU BROTHER >:d< Gimme a hug. Your code was for excel to Access, but it was soooo simple that I could easily use this code for SQL Table also without any error. Thanks again.

Mahendra C,
Mumbai, India.