PDA

View Full Version : Run Access create table query from Excel with msgbox displaying records created



morsoe
09-30-2013, 02:54 AM
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
09-30-2013, 03:15 AM
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
10-01-2013, 06:42 AM
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
10-01-2013, 06:54 AM
You cannot use ADO then. Perhaps:

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