Error 3001 Invalid Argument Trying to Write to SQL linked Table

Using the following code to write info to an Activity table(dbo_ActivityLog). dbo_Activitylog is linked to an SQL database. At the statement "Set rsActivityLog = db.OpenRecordset...." the error "Error 3001 (Invalid argument) in procedure RecordAndShutdown" occurs.  I have used this code before without issue. However not with a table linked to an SQL database. I presume this is possible. What is the best way to resolve this ? Thanks.

Public Function RecordAndShutdown()

Dim FileLock As String
Dim Name_Computer As String
Dim UserName As String


   On Error GoTo RecordAndShutdown_Error

Let UserName = ReturnUserName
Name_Computer = GetComputerName()


   
Set wks = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rsActivityLog = db.OpenRecordset("dbo_ActivityLog", dbOpenDynaset, dbSeeChanges)


      strActivity = "WCHubSQL End and Quit" & " " & "by" & " " & UserName & ", " & Name_Computer
      With rsActivityLog
        .AddNew
        !Activity = strActivity
        !Date_Activity = Date
        !Time_Activity = Time
        !Lock = FileLock
        .Update
      End With
     

   On Error GoTo 0
   Exit Function

RecordAndShutdown_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RecordAndShutdown of Module modUtilities"
            'DoCmd.Save
            DoCmd.Quit
           
End Function
StampITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Sounds like your table isn't properly linked - or needs to be deleted and relinked.
0
StampITAuthor Commented:
I have deleted and relinked. Same result. I can open the table in this database and add records manually.
0
Gustav BrockCIOCommented:
Then try using:

Set rsActivityLog = db.OpenRecordset("Select * From dbo_ActivityLog", dbOpenDynaset, dbSeeChanges)

Open in new window

and/or to exclude one or more fields from the new record.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

StampITAuthor Commented:
Tried using Select. Same result. What do you mean by  "and/or to exclude one or more fields from the new record". Thanks.
0
Gustav BrockCIOCommented:
For example time only:

     .AddNew
    '    !Activity = strActivity
        !Date_Activity = Date
        !Time_Activity = Time
    '    !Lock = FileLock
    .Update

Open in new window

0
StampITAuthor Commented:
Tried several combinations. Same error with every one. Anything else I can try to narrow the issue down ? Thanks.
0
PatHartmanCommented:
Can you add a row to the table if you open the linked table in DS view?
Have you tried to relink the table?
0
Gustav BrockCIOCommented:
Do declare these using DAO:

Dim db As DAO.Database
Dim rsActivityLog As DAO.Recordset

Open in new window

and insert in top of module:

Option Explicit

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StampITAuthor Commented:
The DAO reference and Option Explicit resolved the issue. Had to add the DAO 3.6 Object Library. Thank you. Do you have any idea why ? Is the default ADO ? Thought ADO was supposed to a better fit when the back end was SQL.
0
Gustav BrockCIOCommented:
Great!
If DAO is missing, ADO is used if present. I nearly always use DAO as it is native to Access.

However, 3.6 is an old version for mdb files. If using accdb database files, you should reference:

Microsoft Office 16.0 Access database engine Object Library
0
StampITAuthor Commented:
Thanks. Microsoft Office 16.0 Access Object Library is active also. However if I do not check DAO 3.6 Object Library I get the Compile error "User-defined type not defined". The Access database is accdb. Can you explain ? Thanks.
0
StampITAuthor Commented:
Never mind. I found Microsoft Office 16.0 Access database engine Object Library. Referenced it and the code works fine. Thanks.
0
Gustav BrockCIOCommented:
You are welcome!
0
PatHartmanCommented:
You may have other errors that have not yet surfaced.  The DAO and ADO object libraries have several objects in common.  you should go through your code looking for "database", "recordset", and possibly other key words.  Make sure you disambiguate the declarations.  The first library in the reference list takes precedence and so if ADO is first, the objects will be assumed to be ADO.  If DAO is first, they will be assumed to be DAO.  Rather than take the chance of an error, get ahead of the problem and disambiguate ALL definitions of either DAO or ADO objects, ALWAYS.

Dim db as DAO.Database


or

Dim db as ADODB.Database
0
StampITAuthor Commented:
Thanks for the heads up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.