I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel:
INSERT INTO [Excel 8.0;Database=C:\Test.xls;].[Sheet1$]
SELECT *
FROM Test;
This isn't the real table name or workbook name, but the logic is the same. This method is preferable to me because it's efficient and there's much less chance for user error. But when I use the query I get the dreaded Error 3073, "Operation must use an updateable query". I've tried it on both a network drive and my C drive with no success.
The strange thing is that I can use a Make Table query (Select...Into) to create an Excel workbook with the query copied to it. Both queries also work on my home PC.
I've checked with our system administrators at work and I have modified access to the drives in question, which means that I should be able to read, write, and delete with no problem. So despite the prevailing wisdom on other sites, this doesn't appear to be a permissions problem.
Jamie Collins, if you're still out there...Help!
2 3007
Hi. The INSERT INTO syntax you are using does not match what the current help file states for external data, which is INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression
The externaldatabase part is the one where the path is provided. Unfortunately in trying to test this out I can't come up with a working version (not because of the external database syntax, which is similar to what you show, but because of a syntax error on the target table name - which is not optional).
Why not use Get External Data, Link Tables to link sheet 1 to Access as a linked table instead? You will then be able to run an append in just the same way as you can for any other Access table.
-Stewart
Thanks, Stewart. You raise a good point. I only pursued this option because I could make it work at home. The workbook in question has 26 worksheets and I didn't like the idea of linking them all.
I did some more reseach and it appears another reason using Append Queries to write to Excel doesn't work has to do with which version of Access or Excel you're using and which SP you've upgraded to.
I decided to use some code I found that employs the CopyFromRecordset function in Excel and the xlCellTypeLastCell property. That solved my problem of inserting the query results at the end of the used range in Excel.
Therefore, I'm closing this post. If anyone is interested I'll post the code.
Hi. The INSERT INTO syntax you are using does not match what the current help file states for external data, which is INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression
The externaldatabase part is the one where the path is provided. Unfortunately in trying to test this out I can't come up with a working version (not because of the external database syntax, which is similar to what you show, but because of a syntax error on the target table name - which is not optional).
Why not use Get External Data, Link Tables to link sheet 1 to Access as a linked table instead? You will then be able to run an append in just the same way as you can for any other Access table.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: deko |
last post by:
I'm trying to update the address record of an existing record in my mdb with
values from another existing record in the same table. In pseudo code it
might look like this:
UPDATE tblAddress SET...
|
by: jallegue |
last post by:
I am working with MS-Access 2002. The two tables that I am working with
are:
dbo_IDX_FRS_account_bal_by_month ==> this is a linked table to SQL
== local table
The query that is executed is...
|
by: David Bear |
last post by:
I have a dictionary that contains a row of data intended for a data base.
The dictionary keys are the field names. The values are the values to be
inserted.
I am looking for a good pythonic...
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am...
|
by: Mike D |
last post by:
OS: Windows XP Professional
Microsoft Access 2003
I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp...
| |
by: SydBlack |
last post by:
Hi
I'm creating a simple inventory system with the option to print reports for individual accounts or grand total for all accounts. The individual reports works just fine, but for the grand...
|
by: goslincm |
last post by:
Good morning, I'll try to describe this in 3 parts:
Part 1: I have 2 linked tables that are my source for a query that I am creating for the purpose of being able to create a new field name (by...
|
by: rickmedlin |
last post by:
I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel:
INSERT INTO .
SELECT *
FROM Test;
This isn't the real table...
|
by: plsHelpMe |
last post by:
Hi All,
I have a query something like as follows. Its working fine while query the oracle using java. Can anyone please let me know if there is any limit on the size of query. i just want to be...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |