Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel 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
View Poll Results: What should I do?
Suck it up & undo the primary key 0 0%
Use another method (DAO?) 0 0%
I have the same problem 0 0%
Voters: 0. You may not vote on this poll

 
Old May 14th, 2013, 07:48 PM
Registered User
 
Join Date: May 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2007 VBA ADODB Append Query

Hello All!

So - I have been beating my head against the wall with the following ADODB macro.

Here is what is what I am trying to do, and what is happening:

1) I have a Access 2007 DB sitting on a mapped drive. It is linked to a SharePoint list, and every day it needs to have the data pulled from the list into the "main" storage table in order to be available for other uses - namely for another query to run against the main table - via an append query.

2) The main table has a unique primary key index in it to prevent duplicates. When I run the append query in Access itself I can just click through the warnings, and Access will only insert records that are unique. This works great but I need to be able to help the end users of the spreadsheet be control when that query runs. But I cannot allow them to access the DB directly.

3) So - I created a macro with a ADODB connection that calls this stored query, via an onclick event. It runs - it just will not insert any of the records into to the Access table. At all. Gives me an error that no records were created due to key violations, ect.

Help!!! I will buy you dinner if you can help me figure out a work around that will not entail undoing the primary key (which would create literally THOUSANDS!! of duplicates).

Is there a way to tell Access (via the ADODB connection in Excel) to go ahead and just insert the records that do not violate the primary key - and ignore the ones that do?


Here is the macro:

Code:
Sub ADO_AppendQRY_Data()


Dim Cn As ADODB.Connection      ' Connection Object

Dim oCm As ADODB.Command        ' Command Object
Dim CnStr As String             ' Connection String
Dim param As ADODB.Parameter    ' Command Parameter

Dim iRecAffected As Integer

' Dim strSQL As String  ' This will set up the command string


On Error GoTo ADO_ERROR     ' Error Handler


Set Cn = New ADODB.Connection

' Setting up the connection string.  Provider must be Microsoft.ACE.OLEDB.12.0
' Data source string to the S: drive is okay
' Persist Security Info=False (there is no password)

CnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:mydata_DB.accdb;Persist Security Info=False"



' Connection timeout @ 30 seconds


Cn.ConnectionTimeout = 30

Cn.Open CnStr



Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

' set the param to run the Append procedure stored in the database


' Set param = New ADODB.Parameter

' With oCm
    oCm.CommandType = adCmdStoredProc
    oCm.CommandText = "appQRY_ImportSP_Data"
    ' .Parameters.Append param
' End With


oCm.Execute iRecAffected


If iRecAffected = 0 Then

MsgBox "No records inserted"

End If

' close connection and set to nothing to free system memory

If Cn.State <> adStateClosed Then

Cn.Close
Set Cn = Nothing

End If

Application.StatusBar = False

rs.Close
Cn.Close
Set oCm = Nothing
Set param = Nothing
Set rs = Nothing
Set Cn = Nothing



' Error Handler

ADO_ERROR:

If Err <> 0 Then

' Debug.Assert Err = 0

MsgBox Err.Description

Err.Clear

Resume Next

End If


End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 VBA UDF problem snowman.hk Excel VBA 1 October 24th, 2009 08:50 AM
#NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM
Help! Conversion Excel 2003 VBA codes to Excel 2007 sunny76 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 August 13th, 2009 05:38 AM
excel 2007 vba UDF DavidReese BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 February 24th, 2009 01:10 AM
VBA Print preview in Excel 2007 solnajeff Excel VBA 1 June 23rd, 2008 01:21 PM





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