Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel 2003 VBA Programmer's Reference
|
BOOK: Excel 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2003 VBA Programmer's Reference by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg, Brian Patterson; ISBN: 9780764556609
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 14th, 2009, 03:29 PM
Registered User
 
Join Date: May 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cannot Update. Database or Object is Read Only

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.
 
Old May 26th, 2009, 12:15 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Are you simply trying to return the data in a closed book to a new book without having to open the old book?
 
Old May 26th, 2009, 12:22 PM
Registered User
 
Join Date: May 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, although I am planning to cycle thru the recordset and perform some calculations before returning the results to Excel.
 
Old May 27th, 2009, 09:57 AM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Database or object is read-only ryanb Access ASP 25 January 17th, 2014 06:36 PM
Serializing needs File object on Read, not on Write jcotton BOOK: Beginning Java 2 0 May 7th, 2009 10:36 AM
Cannot update. Database or object is read-only. zedekiah Classic ASP Databases 1 May 2nd, 2008 09:30 AM
Update Fails - Database is Read-Only pete robinson Classic ASP Basics 3 December 19th, 2007 02:40 PM
How to read and update .INI files using Pure C ?? Poonam C++ Programming 1 March 24th, 2005 01:22 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.