append not working in linked table

Posted on 2013-01-29
Medium Priority
Last Modified: 2013-01-30
After testing with a local mdb table I imported this table to sql server 2005 and linked it in access.

Now it doesn't seem to want to do the insert. What is wrong with the code below?

  Dim db As DAO.Database
    Dim RcdSet As DAO.Recordset
    Dim sqlErrors As String
    Set db = CurrentDb()
    sqlErrors = "Select * From tblErrorLog"
    Set RcdSet = db.OpenRecordset(sqlErrors, dbOpenDynaset)

    With RcdSet
            !ErrorNumber = ErrEx.Number
            !ErrorDescription = ErrEx.Description
            !HostUserID = gintHostUserID
                !CallStackInfo = !CallStackInfo & _
                                    ErrEx.Callstack.ProjectName & "." & _
                                    ErrEx.Callstack.ModuleName & "." & _
                                    ErrEx.Callstack.ProcedureName & ", " & _
                                    "#" & ErrEx.Callstack.LineNumber & ", " & _
                                    ErrEx.Callstack.LineCode & vbCrLf & _
                                    ErrEx.Callstack.VariablesInspector.DumpAll & vbCrLf
            Loop While ErrEx.Callstack.NextLevel
    End With
    Set RcdSet = Nothing

Open in new window

Question by:Shawn
LVL 77

Expert Comment

ID: 38833441
What is going wrong ?
Do you get an error message or what?

And what does this mean?

it's not any vba I've ever seen.

Author Comment

ID: 38833451
>> Do you get an error message or what?

no message at all. just no insert


it's from a product called vbWatchDog. amazing
LVL 50

Expert Comment

by:Dale Fye
ID: 38833485
Does tblErrorLog have a Primary Key?  It might also need a timestamp field.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 38833491
yes, it has a primary key.

I'm thinking there is a typo somewhere in the code but have been staring at it too long.
LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 38834363
You may need this:

Set RcdSet = db.OpenRecordset(sqlErrors, dbOpenDynaset, dbSeeChanges)

and, as noted, a Timestamp field.

Also, do open the linked table manually and try to add a record with data as the code would do.


Author Closing Comment

ID: 38837249
thanks Gustav. Was missing the dbSeeChanges.
LVL 53

Expert Comment

by:Gustav Brock
ID: 38838496
You are welcome!


Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question