Avatar of psychokraft
psychokraft asked on

Runtime Error 3074 Operation is not allowed when the object is closed.


Private Sub Authorized_User_Click()
Dim con1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim strSQL As String


strSQL = "INSERT INTO tblAuthorizedUser (authorizedUser, firstName, lastName, phoneNumber, email, Updater, UpdateDateTime ) values (9, 'hjgfhj', 'gfh', '5555558917', 'hjg', 'ghgh', 'gfhg'); "

Set con1 = CurrentProject.Connection

Set recset1 = New ADODB.Recordset
recset1.Open strSQL, con1
recset1.Close
con1.Close
Set con1 = Nothing
Set recset1 = Nothing
End Sub

This code creates the following error, but still updates the record in MS Access. It flags the recSet1.Close line as offending. I know this is simplistic but I'm a very new programmer. Also my first question on here so if my formatting is messed up please forgive and correct. Thanks. CDDVDWeek4-3.accdb
Microsoft ApplicationsMicrosoft OfficeMicrosoft Access

Avatar of undefined
Last Comment
kaufmed

8/22/2022 - Mon
kaufmed

RecordSets are typically used when you want to return rows FROM a DB. When you want to push data to a DB, you would be better suited using a Command object.
Private Sub Authorized_User_Click()
    Dim con1 As ADODB.Connection
    Dim cmd1 As ADODB.Command
    Dim strSQL As String
    
    
    strSQL = "INSERT INTO tblAuthorizedUser (authorizedUser, firstName, lastName, phoneNumber, email, Updater, UpdateDateTime ) values (9, 'hjgfhj', 'gfh', '5555558917', 'hjg', 'ghgh', 'gfhg'); "
    
    Set con1 = CurrentProject.Connection
    
    Set cmd1 = New ADODB.Command
    
    cmd1.ActiveConnection = con1
    cmd1.CommandText = strSQL
    con1.Open
    cmd1.Execute
    con1.Close
    Set con1 = Nothing
    Set recset1 = Nothing
End Sub

Open in new window

SOLUTION
kaufmed

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
psychokraft

This works. However when I try to run the code again with different values I get error 3705 that says the same thing but the complaint is the Operation is not allowed when the object is open.
 
Option Compare Database

Private Sub Authorized_User_Click()
    Dim con1 As ADODB.Connection
    Dim cmd1 As ADODB.Command
    Dim strSQL As String
    
    
    strSQL = "INSERT INTO tblAuthorizedUser (authorizedUser, firstName, lastName, phoneNumber, email, Updater, UpdateDateTime ) values (10, 'hjgfhj', 'gfh', '5555558917', 'hjg', 'ghgh', 'gfhg'); "
    
    Set con1 = CurrentProject.Connection
    
    Set cmd1 = New ADODB.Command
    
    cmd1.ActiveConnection = con1
    cmd1.CommandText = strSQL
    con1.Open
    cmd1.Execute
    con1.Close
    Set con1 = Nothing
    Set cmd1 = Nothing
End Sub

Open in new window

kaufmed

At which line?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
psychokraft

Line 17.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
psychokraft

Thank You so much! That cleared the problem up. Must say that this is the first time I've used it and this site has proved its usefulness. Thanks!
kaufmed

NP. Glad to help  :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.