Run Access create table query from Excel with msgbox displaying records created

[morsoe]

Hi,

I have an Excel spreadsheet where I can click on a button. When I click the button a table is created in an Access database. How can I in the following vba code where the msgbox is shown count the records that are created in Access?

Sub GenopfriskMDM()
 
Dim AppAcc, AppAccDatabase
Set AppAcc = CreateObject("Access.Application")

'Set Database Path & Name
AppAccDatabase = "P:\KoncernOkonomi\indkobsafdelingen\ANALYSEAFDELINGEN\ACCESS\MDM Dublet tjek\test_MDM_data_c.accdb"
With AppAcc
'Open Database
.OpenCurrentDatabase AppAccDatabase
'Run Query
.DoCmd.OpenQuery ("q_tblUdtræk2")
End With
  
  
MsgBox "VBA \ MDM data genopfrisket"
End Sub

[Aflatoon]

You could use ADO:

Sub ExecuteAccessActionQuery()
' Sample demonstrating how to execute an action query in an Access db
   Dim strQuery                    As String
   Dim strPathToDB                 As String
   Dim lngNumRecs                    As Long
   
   Const adCmdStoredProc As Long = 4
   ' Change path as necessary
   strPathToDB = "P:\KoncernOkonomi\indkobsafdelingen\ANALYSEAFDELINGEN\ACCESS\MDM Dublet tjek\test_MDM_data_c.accdb"
   
   ' change query name
   strQuery = "q_tblUdtræk2"

   With CreateObject("ADODB.Connection")
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .Execute strQuery, lngNumRecs, adCmdStoredProc
      .Close
   End With
   MsgBox lngNumRecs & " records added"

End Sub

for example.

[morsoe]

Thanks a lot.

I get this error: Run-time error '-2147217900 (80040e14)': Undefined function "removechars" in expression at the stage:

.Execute strQuery, lngNumRecs, adCmdStoredProc

It's because I have two functions in the Access database that strip the supplier item number for special characters. The two functions are: Trim_varenr: removeChars([tblMDM.Leverandørdelnummer]) and Trim_nul: StripZeros([Trim_varenr]).

[Aflatoon]

You cannot use ADO then. Perhaps:
[vba]
Sub GenopfriskMDM()

Dim AppAcc, AppAccDatabase, db
Set AppAcc = CreateObject("Access.Application")

'Set Database Path & Name
AppAccDatabase = "P:\KoncernOkonomi\indkobsafdelingen\ANALYSEAFDELINGEN\ACCESS\MDM Dublet tjek\test_MDM_data_c.accdb"
With AppAcc
'Open Database
set db = .OpenCurrentDatabase(AppAccDatabase)
'Run Query
db.execute "q_tblUdtræk2"
msgbox db.recordsaffected & " records added"
End With


MsgBox "VBA \ MDM data genopfrisket"
End Sub[/vba]