Re: [MS_AccessPros] LInked tables using SQL

 

Hi Jim,

dirty: yes

>> But if I add a record in the subform or edit a record I get the error again <<

What is the RecordSource for the subform? One table? More? Are any tables also being used by other forms or subforms that are open?

respectfully,
crystal
 
~ have an awesome day ~

On 6/29/17 10:53 AM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] wrote:

So I have been on several Annual State mandated projects that needed to be taken care of. So I am back on them.

So I noticed something. If I add a new record, all works great. But if I add a record in the subform or edit a record I get the error again.

Is this what you meant about the dirty property?

Private Sub TimeSpent_Dirty(Cancel As Integer)
If Me.Dirty Then
    Me.Dirty = False
End If
End Sub

Jim Wagner

---

On Mon Apr 24 2017 06:55:51 GMT-0700 (US Mountain Standard Time), crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hi Jim,

adding on...

The SS Timestamp data type is not like Date/Time in Access and cannot be read directly by Access since it is binary. 
https://technet.microsoft.com/en-us/library/ms182776(v=sql.110).aspx

re: the error message you are getting:

If you have a form with the data and are also modifying data with SQL statements, be sure to save the record in the form before setting database object variable and running the SQL.

if me.dirty then me.dirty = false

Also, once you run SQL, don't make changes to the same record by setting form controls -- my experience is that it is better to use SQL again if you need to do that.

respectfully,
crystal

http://www.MsAccessGurus.com
connect to me, let's build it together
 
~ have an awesome day ~

On 4/12/2017 12:22 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] wrote:

John,

I will give that a shot. Thank you

Jim Wagner

---

On ‎Wednesday‎, ‎April‎ ‎12‎, ‎2017‎ ‎09‎:‎32‎:‎10‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim-

The table must have a Primary Key and *may* need a Timestamp field if you run into this issue.  Have you tried adding a Timestamp?  Do that by editing the table in Management Studio, then re-link the table in Access.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Apr 12, 2017, at 5:53 PM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello all,

I am trying to learn SQL. So I created a database in SQL and linked the tables to my Access database. I am having this error come up It is a write conflict error shown below in red. the Save Record button is disabled. I do not understand. I open the access database and try to edit a record. No one else has access to the database, so it cannot be someone else editing it. I can go into SSMS and edit the record and I am okay in both SQL and Access. But that is not how I imagined editing records. 

My research states to put a TimeStamp field in the SQL, and then another site said a timestamp field will cause the issue. All of this works in my current production database with the local tables. So it seems like the connection to SQL maybe messed up. But the edits in SQL show up immediately in Access. 

I have looked at something called dsn-less connections and that seems like it could work, but why does the write conflict even happen. why should I try that if a simple connection should work? 

Thank You for listening to my frustrated rattling. But if anyone has any thoughts, please let me know. 

Thank You

Jim Wagner

This record has been changed another user since you started editing it. If you save the record, you will overwrite the changes the other user made. 

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste you changes back in if you decide to make changes.

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___