Results 1 to 2 of 2

Use QueryTables to write Excel data back to Access

Threaded View

vfxd Use QueryTables to write... 08-09-2012, 08:08 PM
vfxd Re: Use QueryTables to write... 08-13-2012, 10:38 AM
  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Use QueryTables to write Excel data back to Access

    I am using QueryTables to display information in an Excel 2010 worksheet. I want users to be able to make changes in the Excel table, and for those changes to be written back to the Access tables. I thought it was as easy as specifying mode=ReadWrite in the connection string, but that doesn't seem to do the trick.

    Right now if changes are made in Excel, the same changes are not being made in Access, and if the user presses the "Refresh" button, the changes are over-written with the original data. I've pasted my connection string below. Is there another setting I need to make in addition to the mode=ReadWrite setting in the connection (or a separate QueryTable setting) in order to make the Excel table sync with / write data back to Access? Thanks!

    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\DEV\TestDB.accdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

    PS -- if there is another best practice for doing this besides QueryTables, let me know. I have used ADODB elsewhere for more complex problems, but this is simply trying to sync a few tables from Access to Excel & back to Access. I figured QueryTables was the best choice for this, but if there's a better way let me know. Thanks!
    Last edited by vfxd; 08-09-2012 at 08:32 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1