CODEWITHSUNDEEP
sqlvbams-accesssql-insertms-access-2007
user2292941
user2292941

Reputation: 89

Use VBA to INSERT a new record into an Access 2007 database

I am still trying to learn, so please be gentle. My goal is to track users who've completed viewing a standalone MS PowerPoint by having them click a button on the last slide.

When the completeButton is clicked, it captures the Windows username and date. How can I have the event also insert both the variables as a new record into an existing MS Access 2007 database (behind the scenes)?

'Click button
Private Sub completeButton_Click()
'Get Windows user
Dim empUserName As String
empUserName = Environ("UserName")
'Get Date
Dim Completed As Date
Completed = Date
'Set Path to database
Dim Path As String
Path = "C:\TestDatabase.accdb"

'Open connection to database
'Insert empUserName and Completed variables as new record
'Close connection to database
'Display message
Msgbox("Thank you for viewing the material")
'Close presentation
With Application.Presentations
For i = .Count To 1 Step -1
    .Item(i).Close
Next
End With

Upvotes: 2

Views: 125

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

It can be done by one command:

DoCmd.RunSQL "INSERT INTO C:\TestDatabase.accdb.Table1 ( Field1, Field2 ) VALUES (""" & empUserName & """, """ & Completed & """);"

You can add

DoCmd.SetWarnings False

before inserting in order to suppress warning.

Upvotes: 0

ZygD
ZygD

Reputation: 24478

I tested this on Office 2013, hope it would work for you too. These lines open connection, insert new entries and close the connection.

Dim Path As String
Dim strCon As String
Dim con As Object

Path = "C:\TestDatabase.accdb"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path
Set con = CreateObject("ADODB.connection")

con.Open strCon
con.Execute "INSERT INTO Table1 (Field1, Field2) VALUES (""" & empUserName & """, """ & Completed & """)"

con.Close
Set con = Nothing

Table1 - your table name
Field1 - name of the column where you want to put empUserName
Field2 - name of the column where you want to put Completed

Upvotes: 2

Related Questions