Cannot Update. Database or Object is Read Only

randy768 · May 14th, 2009, 03:29 PM

I am trying to read a closed Excel file and followed the example in Chapter 11 for "Querying Microsoft Excel Workbooks". When I run the code I get an error at the RecordSet.Open method stating that "Cannot Update. Database or Object is Read Only". The only thing I could find on the web regarding this error is that it has a wrong file extension but it is just a straight forward Excel file (Excel 2003). Any help would be greatly appreciated.

SheriV · May 26th, 2009, 12:15 PM

Are you simply trying to return the data in a closed book to a new book without having to open the old book?

randy768 · May 26th, 2009, 12:22 PM

Yes, although I am planning to cycle thru the recordset and perform some calculations before returning the results to Excel.

SheriV · May 27th, 2009, 09:57 AM

Sorry but I'm not real sure how to help. I import data regularly from download files. Some of txt files and others are excel files into my access database. I am importing into tables that I have already created or create on the fly. The code I use (in access) to perform the import is as follows:

From a .xls file

Code:

    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "MRPurge", acNormal, acEdit (I delete the existing data)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Items Meth & Rate", ":X\database\helpers\MRHelper.xls", True, "Items!A1:E30000" (Import data from closed file)
    'DoCmd.DeleteObject acTable, "E30000_ImportErrors" (get rid of any error file that occurred)
    DoCmd.SetWarnings True

from txt file

Code:

    DoCmd.OpenQuery "Vendor Master Purge", acNormal, acEdit (get rid of old data)
    DoCmd.TransferText acImportDelim, "ImportVendors", "Vendor Master", _
        "X:\Downloads\Ven Mstr.txt", False, ""

if I want a new table:

Code:

Function Import_NuItmMst_txt()
On Error GoTo Import_NuItmMst_txt_Err
    ' Import new item master records
    SendKeys "X:\DownLoads\Item Master.txt~~~{down}AS400 Item Master~~", False
    DoCmd.RunCommand acCmdImport
Import_NuItmMst_txt_Exit:
(Here is where you can add additional code to loop through and perform calculations and then export data from table to excel file)
    Exit Function
Import_NuItmMst_txt_Err:
    MsgBox Error$
    Resume Import_NuItmMst_txt_Exit
End Function

Code I use in Excel:
if I am using excel vba to import data from a file to access database I normally do it one of two ways depending on what I'm doing.

1. I import the data from the closed file directly to access (I already have a table and a import spec set up and a series of query's that perform the actions against the data that I want it to perform. Then I return the results to my excel workbook.

Code:

    Set dba = CreateObject("Access.application")
 
    dba.OpenCurrentDatabase "X:\Database\RtrDB.mdb"        dba.Visible = True
    vIPth = "X:\Downloads\"
    dba.DoCmd.TransferText acImportDelim, "MfgImport", "Mfg Master", vIPth & "Mfg Master.Txt", False, "" (This for txt)
    dba.DoCmd.TransferSpreadsheet acImport, , "CusMaster", vIPth & "AS400 CusMstrX.xls", True (this for xls)
 
    dba.DoCmd.OpenQuery "SetDFreq", acViewNormal, acEdit (performs calculations against the data I imported)
    dba.DoCmd.OpenQuery "SetDelRec", acViewNormal, acEdit (performs calculations against the data I imported)

2. I bring the data in & modify the way I want it and then I export it to my database table

Code:

    
    vtbl = ActiveSheet.Name
    vDrv = Range("wDrv").Value
    vFile = Range("vFile").Value
    vPath = Range("wDrv").Value & vFile
    Set dbs = CreateObject("Access.Application")
    dbs.OpenCurrentDatabase vPath
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    vSrc = ThisWorkbook.Path
    vWkBk = ThisWorkbook.Name
    With dbs
        dbs.DoCmd.OpenQuery "VenMstrDel", acNormal, acEdit
        dbs.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "VenMstr", _
            vSrc & "\" & vWkBk, True, vtbl & "db"
        dbs.CloseCurrentDatabase
    End With
    dbs.Quit

Hope some of this may help you. If not now maybe it will help in the future.

Good luck,
S.