SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member zeromonster's Avatar
    Join Date
    Apr 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    import Excel using ASP and MS Access

    i have used this code for importing Excel files to the database but always get this error: >> Syntax error in FROM Clause:

    this is my coding:


    <%
    Set ExcelConn = Server.CreateObject("ADODB.Connection")
    Set ExcelRS = Server.CreateObject("ADODB.Recordset")
    ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
    ExcelConn.Open "C:\Sample\Sample.xls"

    'get data from sheet
    sSQL = "SELECT * FROM Sample$"
    set ExcelRS = ExcelConn.Execute(sSQL)

    'loop through each record in Excel and write it to access
    'might be slow, but will work

    Do until ExcelRS.EOF
    myConn.Execute("INSERT INTO Sample_tbl(lname,fname,mi) VALUES ('" & ExcelRS("0") & "', '" & ExcelRS("1") & "','" & ExcelRS("2") & "')")

    ExcelRS.MoveNext
    Loop

    ExcelRS.Close
    set ExcelRS = NOTHING
    objExcelConn.Close
    set ExcelConn = NOTHING
    objConnAccess.close
    set objConnAccess = NOTHING
    %>



    please help me. regards!

  2. #2
    SitePoint Enthusiast MeLoDi's Avatar
    Join Date
    Sep 2003
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    erm.. when u do that the excel sheet has to have a sheet called Sample.. or maybe u could add []

    Code:
    set ExcelRS = ExcelConn.Execute ("SELECT * FROM [Sheet1$]")
    haha not sure if it would help but hope it does

  3. #3
    SitePoint Member zeromonster's Avatar
    Join Date
    Apr 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thnx a million! that saved my life!

  4. #4
    SitePoint Enthusiast MeLoDi's Avatar
    Join Date
    Sep 2003
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    glad it helped u


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •