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
Excel 2007 VBA ADODB Append Query
