Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 18th, 2005, 06:16 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default using vbscript in asp to import excel into access

hi,
i am currently trying to setup a database that i can fully integrate with a web-based interface. i am using VBscript in ASP pages to accomplish my task. so far i have figured out how to use the ADO object to get info from an Access DB to my webpage. i am having trouble figuring out two things:

1. how to get info directly from an excel file

i've tried something like:
<%
dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\brian\GSTL Calc\Interface\test.xls;Extended Properties=Excel 8.0;Persist Security Info=False"

dim objRS,strSQL
set objRS = server.createobject("adodb.recordset")

'strSQL = "SELECT $A1:K68 FROM Sheet1"
objRS.open "Sheet1$A1:K68",strConnect,adOpenStatic,adLockOpti mistic,adCmdTable
'objRS.open "FROM [Sheet1$A1:K68]",strConnect,adOpenStatic,adLockOptimistic,adCmdTa ble

while not objRS.EOF
    Response.Write "<tr><td><center>" & objRS & "</center></td></tr>"
    objRS.MoveNext
wend
%>

...as one can see, i've tried many syntax variations...none of which work.


2. i know you can import or link an excel spreadsheet into access when the access db is opened. you can also do it using a macro that utilized the DoCmd.TransferSpreadsheet command as shown here:

Function excelimport()
On Error GoTo excelimport_Err

    DoCmd.TransferSpreadsheet acImport, 8, "data", "C:\brian\GSTL Calc\Proto Calcs\web front\testdata.xls", True, ""


excelimport_Exit:
    Exit Function

excelimport_Err:
    MsgBox Error$
    Resume excelimport_Exit

End Function


...this is all fine and dandy, but i can't seem to get it to work when i try to incorporate it into my ASP script. i think the problem is that i am using VBscript to call a VB function. so is there a way to either call the macro directly using VBscript or load some kind of object within VBscript that will allow the processing of VB commands?

i am currently using WROX's "Beginning ASP 3.0" book and have found it very useful so far.

this is driving me nuts and i'm sure i'm wasting far more time than i should on this. any help would be GREATLY appreciated!

thanks,
brian
biomedbz@hotmail.com
aim: bmichaelz1
yahoo: wpiass
Reply With Quote
  #2 (permalink)  
Old January 20th, 2005, 12:15 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 Posts
Default

Turns out the Jet OLE DB provider is smart enough to handle the whole exchange. I used it open an Excel and an Access connection, load two recordsets, synch them, and batch update the db on the server. The is well tested and doesn't seem to be giving me any problems. The db and the workbook are on the server with write permissions on the folder.

Code:
<% @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"

    ' Open Excel Connection
    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strExcelFile & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""
    Response.Write "Excel connection opened<BR>"

    ' Load ADO Recordset with Excel Data
    Set rstExcel = Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "Select * from [Sheet1$A1:B5]", cnnExcel, adOpenStatic 
    Response.Write "Excel Recordset loaded<BR>"

    ' Open Access Connection
    Set cnnAccess = Server.CreateObject("ADODB.Connection")
    cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strAccessFile & ";" & _
               "Persist Security Info=False"
    Response.Write "Access connection opened<BR>"

    ' Load ADO Recordset with Access Data
    Set rstAccess = Server.CreateObject("ADODB.Recordset")
    rstAccess.Open "tblAccessImportData", cnnAccess, adOpenKeyset, adLockOptimistic, adCmdTable
    Response.Write "Access Recordset loaded<BR>"

    'Synchronize Recordsets and Batch Update
    Do Until rstExcel.EOF
        With rstAccess
            .AddNew
            .Fields("ID_FIELD") = rstExcel.Fields(0).Value
            .Fields("DATA_FIELD") = rstExcel.Fields(1).Value
        End With
            rstExcel.MoveNext
    Loop
    rstAccess.UpdateBatch
    Response.Write "Excel data written to Access<BR>"


    'Clean up
    rstExcel.Close
    Set rstExcel = Nothing
    rstAccess.Close
    Set rstAccess = Nothing
    cnnExcel.Close
    Set cnnExcel = Nothing
    cnnAccess.Close
    Set cnnAccess = Nothing

    Response.Write "Import from Excel to Acess complete<BR>"

%>


</body>
</html>
HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old January 20th, 2005, 02:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 Posts
Default

The MetaData tag is simply a reference to the latest ADO Type Library DLL:

<!--METADATA
TYPE="TypeLib"
NAME="Microsoft ActiveX Data Objects 2.6 Library"
UUID="{00000206-0000-0010-8000-00AA006D2EA4}"
VERSION="2.6"
-->

I don't know if you were using the older syntax for the ADO include file or not:



Either will do, though the METADATA tag is generally preferred. Just wanted to be sure you knew what it was if you hadn't seen it before.

- Bob



Reply With Quote
  #4 (permalink)  
Old January 20th, 2005, 01:10 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default

Bob:

Thanks for the input. I am familiar w/ the METADATA info and I tried your code, but got the following error:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80040E09)
Cannot update. Database or object is read-only.
/asp/GSTL/exceltest2.asp, line 48


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
GET /asp/GSTL/exceltest2.asp



I am using a test Access DB that I just started so I did not set any security or read-only functions. Does it do this by default?

Regards,
Brian
Reply With Quote
  #5 (permalink)  
Old January 20th, 2005, 04:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 Posts
Default

Hi Brian,

Nope. Write permissions aren't granted by default.

I'm running IIS locally configured to allow anonymous access under the default IUSR_MachineName account. That means I have to grant write permission to the IUSR_MachineName account on the virtual directory storing the Access db on IIS.

If you'rr using IIS as your web server, you need to do the same. Here's how.

- Open Windows Explorer and browse to your database folder (mine is C:\Inetpub\wwwroot\ASPTEST)
- Right click the folder, choose properties, then click the Seccurity tab. (If you are running an NTFS file system, like XP Pro, and don't see the Security tab, see below).
- In the "Group or user names" list box look for an account called: Internet Guest Account (YourMachineName\IUSR_YourMachineName).
- If you don't see this account, click the Add button, and type IUSR_YourMachineName in the "Enter the object names to select" text box. Click OK.
- You should now see the Internet Guest Account (IUSR_YourMachineName\IUSR_YourMachineName) in the "Group or user names" list. Select it.
- In the "Permissons for Administrators" list, be sure both the Read and Write check boxes are selected. Click OK.
- Take a deep breath.

Your anonymous acces account (IUSR_YourMachineName) now has permission to write to the Access db, and you should be able to perform the AddNew ADO commands.

What, you don't see your Security Tab????

- Open Internet Explorer again. (Are we having fun yet?) Select Tools -> Folder Options.
- In the Folder Options dialog, select the View tab.
- Scroll to the bottom of the "Advanced Settings" tree view control.
- Uncheck the "Use simple file sharing (Recommended)" check box. Click OK
- Now go back and repeat the steps listed above. When you right click your db folder, and select properties, the Security tab should be visible.
- Take a deep breath.

What? You don't know if you're a configured to use anonymous acess?!? And what is my machine name anyway?!?! (Yikes, we're almost there)

- Open IIS.
- Browse to your Default Web Site folder (This is typically the virtual directory that points to "C:\inetpub\wwwroot"
- Right click the Default Web Site folder, and select Properties.
- Click the Directory Security Tab, then click the Edit button next to "Anonymous Access and Authentication Control".
- In the Authentication Methods dialog, be sure the Anonymous Access check box is checked.
- The "Account used for anonymous access" default account text box should display IUSR_YourMachineName. This is the account name you add to the "Group or user names" list box as described above. The "Allow IIS to control password" check box should be checked.
- Repeat all the other stuff listed above as needed.

NOW you can write to your Access db.

So there are three places you need to go:

1. You're Default Web Site needs to be configured for Anonymous Access on IIS, and checking its properties dialog will give you the name of the default anonymous access account (IUSR_YourMachineName).

2. Your database folder needs to have write permissions granted to the IUSR_YourMachineName account, whihc you set through the db folder properties dialog.

3. Your db folders properties dialog needs to display the Security tab in order to perform step two, which is set through Internet Explorer on an NTFS operating system.

Post again if you run into problems. You can perform this process to grant write permission to the IUSR_MachineName account for any folder on your system if you choose not to put the db in a folder on the server.

HTH,

Bob

Reply With Quote
  #6 (permalink)  
Old January 21st, 2005, 08:50 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default

Bob:

It worked great! Thank you so much. The only problem I am having now is accessing a local Excel file from a script that is located on a server on the internet.

i.e. -

Microsoft JET Database Engine error '80004005'

'C:\brian\GSTL Calc\Interface\diablo\newdiablodata.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

/gstl/Interface/datacheck.asp, line 68


This asp file works on my local machine with IIS, but when I run it from an internet server, the server doesn't recognize my local file name. Is there a way to u/l the file to the server so it would be "local" to the server?

Regards,
Brian
Reply With Quote
  #7 (permalink)  
Old January 21st, 2005, 11:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 Posts
Default

Hi Brian,

Glad you got everything running locally. File uploading to/from a remote server with ASP is something I am utterly clueless about, though I understand it ain't easy. I'd suggest posting to the ASP Forum, or maybe seeing if the 4 Guys From Rolla can point you in the right direction:

http://www.4guysfromrolla.com/webtech/110499-1.shtml

HTH,

Bob

Reply With Quote
  #8 (permalink)  
Old October 12th, 2006, 08:36 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Bob,

I tried to run your example but in vain.

Can u make this work for me with my excel file?


http://rapidshare.de/files/36425129/..._info.zip.html


Thanx a ton in advance for your help

www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #9 (permalink)  
Old March 19th, 2007, 12:14 AM
Registered User
 
Join Date: Mar 2007
Location: Fresno, CA, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just wanted to say that the code works perfect for importing the data into the access database. just wanted to ask that if I can do some data checking wile its uploading to the fields, like the end of the file has a long default disclaimer message from the provider of the data, I do not want that to be submitted to the database, that is why I wanted to place a check that if the field value length is greater than a certain limit or if it contains certain words then exit out.

Thanks,
Ebaad.

Reply With Quote
  #10 (permalink)  
Old March 19th, 2007, 05:21 AM
Registered User
 
Join Date: Mar 2007
Location: Fresno, CA, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

javascript:insertsmilie(':)')
Smile :)
Finally I figured out how to get this done. This is for people like me pulling their hair and scratching their heads to find the solution to a problem like this.

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

<%
'DECLARE THE ACCESS DATABASE CONNECTION
Dim rsAdd
Dim rsAdd_numRows

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

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>IMPORT EXCEL FILE IN ACCESS DATABASE</title>
</head>
<body>
<%
Dim objConn, objRS, strSQL
Dim xIndex

'DECLARE THE EXCEL WORKSHEET CONNECTION
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_Acad_Comp.xls") & "; "

'SET QUERY TO SELECT THE DATA CELLS FROM THE EXCEL WORKSHEET 
'THIS CAN BE DONE IN VARIOUS DIFFERENT WAYS, BUT THIS WORKED
'FOR MY PURPOSE
strSQL = "SELECT * FROM A1:Q10000"

'EXECUTE THE QUERY TO THE EXCEL WORKSHEET
Set objRS=objConn.Execute(strSQL)
%>



<table border="0">
    <tr>
    <%
    'EXTRACT THE HEADER VALUES TO DISPLAY ON THE WEBPAGE
    For xIndex=0 To objRS.Fields.Count-1
       Response.Write("<th>" & objRS.Fields(xIndex).Name & "</th>")
    Next

    'ITERATE THROUGH THE EXCEL WORKSHEET AND UPDATE INTO THE ACCESS DATABASE
    'NOTE THE VALUE OF xIndex IS DIFFERENT FOR BOTH ACCESS AND EXCEL ARRAY
    'THIS IS TO TAKE IN ACCOUNT THE PRIMARY KEY VALUE FIELD IN ACCESS IF IT IS
    'SET TO AUTO NUMBER
    Do Until objRS.EOF
        If IsNull(objRS.Fields(0).Value) Then Exit Do
        rsAdd.AddNew
        Response.Write("<tr>")
        For xIndex=0 To objRS.Fields.Count-1
            curValue = objRS.Fields(xIndex).Value
            Response.Write("<td>" & objRS.Fields(xIndex).Value & "</td>")
            rsAdd.Fields(xIndex+1).Value = objRS.Fields(xIndex).Value
        Next
        Response.Write("</tr>")
        rsAdd.Update
        objRS.MoveNext
    Loop
    %>
    </tr>
</table>
</body>

</html>


<%
rsAdd.Close
Set rsAdd = Nothing
%>
:)

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
import excel to access Vision G Access 1 October 15th, 2008 03:12 PM
Fail to auto import excel to access table Ivanchan Access VBA 2 April 19th, 2007 10:34 PM
Import Excel File to access database Komila ASP.NET 1.0 and 1.1 Professional 7 October 7th, 2006 01:45 AM
Import Data from Open Excel Sheet to Access chintu4u Pro VB Databases 0 May 15th, 2006 02:24 AM
Import an excel file into access arjunvs Access VBA 1 September 15th, 2004 08:21 PM



All times are GMT -4. The time now is 05:27 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.