Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 February 22nd, 2017, 11:35 AM
Registered User
 
Join Date: Feb 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADODB Insert query and Excel

Hi all. First post here. I've read through most of the similar posts on this topic but none are quite the same as mine. I get the error "Invalid SQL. Expected Insert, or delete" when I try to run an append query in access. All other queries work just fine.

Here is my connection string...
==================
Set con = New ADODB.Connection
Set cmd = New ADODB.Command

With con
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=n:\Service\ServiceStandards.accdb;Jet OLEDB:Database Password=service"
.Open
End With
====================

These 2 queries work just fine...
con.Execute "qrySSRenewalDelete"
con.Execute "qrySSRenewalDeleteCompletedRenewals"

This one produces the error
con.Execute "qrySSRenewalDErnie"

Any ideas?
 
Old February 23rd, 2017, 02:10 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

The only idea I have without seeing the SQL for the query qrySSRenewalDErnie is that the query is a SELECT query not an action query.

Sorry, my crystal ball in out of order. Please post the SQL for the query qrySSRenewalDErnie so we can take a peek.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old February 23rd, 2017, 08:07 AM
Registered User
 
Join Date: Feb 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It's an append query. I should also note that it runs fine in access.


INSERT INTO tblSSRenewalD ( [GO], rgName, Analyst, MnthComp, YearComp, ClientID, OverUnder, Completed, Segment )
SELECT DISTINCT qrySSRenewalC.goGroupOffice, qrySSRenewalC.rgname2, tlkpAssistant.asAssistant, qrySSRenewalC.MnthComp, qrySSRenewalC.YearComp, qrySSRenewalC.clMainPol, qrySSRenewalC.OverUnder, qrySSRenewalC.Completed, 'Major and Mid Market' AS Expr1
FROM qrySSRenewalC LEFT JOIN tlkpAssistant ON qrySSRenewalC.Analyst = tlkpAssistant.asUserID
WHERE (((qrySSRenewalC.CompletedDate)>=(#1/1/2017#) And (qrySSRenewalC.CompletedDate)<=(#12/31/2017#)))
ORDER BY tlkpAssistant.asAssistant, qrySSRenewalC.MnthComp;





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 VBA ADODB Append Query wendie1970 Excel VBA 0 May 14th, 2013 07:48 PM
Excel 2003 - Need Macro for Insert Column in excel chandransumesh Excel VBA 1 July 29th, 2012 10:08 PM
Need help with ADODB Insert sg48 SQL Language 0 March 21st, 2010 12:05 PM
excel file reading error (adodb) ccc_storage Classic ASP Professional 1 August 22nd, 2004 07:57 PM
Working with Excel Worksheet as ADODB.RecordSet Sebastiaan Classic ASP Basics 2 May 25th, 2004 03:10 AM





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