Append Excel Spreadsheet to Access Database using ASP

[mgagliardi]

I have an excel spreadsheet that I use to map data that I recieve from various sites. Once the spreadsheet does its magic, I then use an Access VB routine to update the access database using a few SQL statements to identify and delete the old data, then a docmd.transferspreadheet function to import the spreadsheet into a table in access that has the exact same field names as the spreadsheet. Then finally I run an append query to update the appropriate table with acces itself. My problem is, I have to export the Access database to my local computer, run the routine described above and then reimport the database back to my web hosts server. I want to be able to do this all from an asp page, or a suggested better route than downloading and uploading my entire database. Here is a copy of the Access VB routine I run incase it helps:

Private Sub UpdateAZHHA_Click()
On Error GoTo Err_UpdateAZHHA_Click

Dim delAZHHAtbl As String
Dim delAZ As String
Dim appendAZ As String

delAZHHAtbl = "DELETE AZHHA.* FROM AZHHA"
delAZ = "DELETE tblOrders.*, tblWorksites.ClientID FROM tblWorksites INNER JOIN tblOrders ON tblWorksites.WorksiteID = tblOrders.WorksiteID WHERE (((tblWorksites.ClientID)=5))"
appendAZ = "INSERT INTO tblOrders ( WorksiteID, StatusID, SpecialtyID, ShiftID, DisciplineID, BonusPaidBy, Notes, StartDate, EndDate, CertIMPFLD )SELECT AZHHA.WorksiteID, AZHHA.StatusID, AZHHA.SpecialtyID, AZHHA.ShiftID, AZHHA.DisciplineID, AZHHA.BonusPaidby, AZHHA.Notes, AZHHA.StartDate, AZHHA.EndDate, AZHHA.CertIMPFLD FROM AZHHA"
DoCmd.SetWarnings False
DoCmd.RunSQL delAZHHAtbl
DoCmd.RunSQL delAZ

DoCmd.TransferSpreadsheet acImport, , "AZHHA", _
"C:\COMS\AZHHAimport.xls", _
True, "Import!"

DoCmd.RunSQL appendAZ

Exit_UpdateAZHHA_Click:
Exit Sub

Err_UpdateAZHHA_Click:
MsgBox Err.Description
Resume Exit_UpdateAZHHA_Click

End Sub

 

[DotNetGnat]

something like this:

<%
 set access = server.createobject("Access.Application")
 access.opencurrentdatabase "c:\somedatabase.mdb"
 access.docmd.runmacro "mymacro"
 access.quit
%>

-DNG

 

[mgagliardi]

Thank you for he reply, but I have a question. I went into my database and wrote a macro that calls up the spreadsheet processing function I mention above and tested it. It works off line. However in the Access Function the spreadsheet is located at c:\coms\azhhaimport.xls, but on line it is upload to ../fpdb/azhhaimport.xls.

Will I need to change the reference in the Access Function and how would it look? I have tried ../fpdb/azhhaimport.xls, but it does not work. I use a DSN-less connection to the database and server.mappath. Or is there something I can do to leave the spreadsheet on my local machine?

Thanks in advance

 

[DotNetGnat]

try using...something like this:

access.opencurrentdatabase Server.Mappath("/fpdb/azhhaimport.xls")

-DNG

 

[DotNetGnat]

also refer to the discussion on this link, might help you out a bit...

post back if you encounter any errors...

http://forums.aspfree.com/archive/t-45282/Run-access-report-in-asp

-DNG