-1

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
kfrazie1
  • 11
  • 1
  • 3
  • 2
    Does this answer your question? [Using SQL parameters returns "Arguments are of the wrong type"](https://stackoverflow.com/questions/51476283/using-sql-parameters-returns-arguments-are-of-the-wrong-type) – Flakes Jul 05 '20 at 06:16
  • Answer relevant to using [Named Constants in Classic ASP](https://stackoverflow.com/a/26776169/692942). – user692942 Jul 05 '20 at 11:41
  • I've always found it much easier to use standard SQL insert queries than to mess around with editable recordsets, eg `cnnAccess.Execute("insert into YourTableName(Field1, Field2) values('"&value1&"','"&value2&"')")` – John Jul 05 '20 at 15:55
  • I take it you know you can import an Excel spreadsheet directly into an Access database. – John Jul 05 '20 at 15:57

1 Answers1

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

The sql statement above worked.

kfrazie1
  • 11
  • 1
  • 3