Classic ASP, Visual Basic, and ADODB Recordsets

Question

I've recently inherited a classic ASP application written in Visual Basic and Java Script. I'm not a programmer but have written a few applications in VBA working with Excel and Access.

I'm looking to add in a script to copy an excel (.xlsx) worksheet to an access (.accdb) table using ADODB recordset. I've used this approach in VBA but the Visual Basic Code in asp classic is not recognizing the Locktype or Options properties of the recordset object. If i remove .Options and .Locktype the script will run but only in read only mode and I can't add new records.

adPath = Server.Mappath("OutageData/QHT.accdb")
   Set cnnAccess = Server.CreateObject("ADODB.Connection")
with cnnAccess
    .Provider="Microsoft.ACE.OLEDB.12.0"
    .Open adpath
end with
Set rstAccess = Server.CreateObject("ADODB.Recordset")
with rstAccess
      
    .ActiveConnection=cnnAccess
    .CursorType=adOpenDynamic
    .Options=adCmdTable 
    .LockType=adLockOptimistic
    .cursorlocation=aduseclient
    .Open "TST1UPDT"


  end with

Any suggestions?

Thx

Keith

Answer 1

ssqlinsertupdt ="INSERT INTO " & "TST1UPDT" & " SELECT * FROM " & "[Excel 8.0; HDR=YES; DATABASE=" & cnnAccess & "]." & "[TASK$]"
DatabaseConnection.Execute ssqlinsertUpdt

The sql statement above worked.