Selasa, 22 Maret 2016

[MS_AccessPros] Re: Working with recordset with SQL back end

 

John,


"Pivot" was a poor choice of term. What I mean is that the rst was opened as dbAppendOnly+dbSeeChanges. Don't I have to then reopen it in order to be able to edit it?


Adam



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

The LastModified property applies to the recordset you have open - the one owned by you.  100% guaranteed to be the record you just added.

What do you meant by "pivot"?  That's a term used for Crosstab queries in Access.  Note that when you do rst.BookMark = rst.LastModified, you are now positioned on that last record, so you can surely .Edit it.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 22, 2016, at 2:43 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Yes it worked. The explanation leads to some more questions.


Is it possible that the LastModified isn't the record I just added, that someone else modified in the interim? Would it be better to do something like:

Db.OpenRecordset("SELECT ... FROM tblParentListBE WHERE [ParentName] = '" & Me.ClientName & "'"


Also, how can I quickly pivot the rs so that I can edit that last modified record?


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Excellent explanation.  When adding record via code in an Access database, Access sets the AutoNumber value as soon as you "dirty" the record by setting any field value.  That's why you can fetch the value before you save the record.  In SQL Server, the database doesn't set the Identity value until you save the record, so you have to find it after the save and then grab the value.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 19, 2016, at 9:19 AM, yahoo@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Did anyone explain to Adam why John's code is necessary and why it (usually) works?

With an Access backend, the DBEngine knows what's going on in the database because it "owns" it, Access assigns the Autoincrement values when you start to dirty a new record.

With a SQL Server Database (or other Back-ends) the DBEngine has no idea what is going on on the Database in the background. Some other process might be trying to pump data into the table and, while the user is busy filling out the details of the record to be added, neither Access Front-End and the SQL Server back-end know what the other one is doing. Adding a record to a SQL Table does not do anything to the back-end until it is saved so the record cannot be assigned an autoincrement value until it is actually saved, that is why the Autoincrement field remains NULL until after you save it. John's code alllows Access to *find* the record you saved and to return the values actually saved, including the Autoincrement field.

Again, Access does not know what is going to be saved in the back-end SQL Server table until it is saved. There are default values on fields which mean that the record actually saved might have some different values to those you sent in the update. There are calculated fields. There are triggers on SQL Server tables which might also mean that what gets saved in the record is different to what you saved in the update. That's where the dbSeeChanges comes in, it tells the DBEngine to go look for changes in the data in the backend and present them to the front-end.

Once you start to think about how different the connection to a SQL Server backend is compared to an Access file backend, you start to see how wonderful the magic is that the Microsoft people built into Access (specifically the Jet Engine and it's siblings.)


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Did you try the DAO code in the article I sent you?  Basically, you should be able to use the LastModified property to move back to the newly added record, then grab the Identity value:

    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly+dbSeeChanges)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    rst.Update
    rst.BookMark = rst.LastModified
    tID = rst!ParentID
    rst.Close
    Set rst = Nothing

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 5:27 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bill,

Can you help? Your advice in the link didn't work.

Track Autonumber new record Addnew or append qry

 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

This article might help:



John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 4:04 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

That gives me RT error 3021, No current record.

Anyone?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Oh, I didn't notice that you were trying to capture the Identity value.  There's a way to do that, but I don't remember.  It *might* work if you move the tID = after the Update.

Anyone else want to jump in?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 10:37 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,


I think I need to put "+ dbSeeChanges" after "dbAppendOnly"


Then, I get an error Invalid use of Null on

    tID = rst![ParentID]


rst![ParentID] evaluates to null


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

I don't see anything you need to change if you're using linked tables to SQL Server.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 8:43 PM, runuphillracing@...[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

How do I change the following code to work with a SQL back end (as opposed to an accdb BE)?


    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    tID = rst![ParentID]
    rst.Update
    rst.Close
    Set rst = Nothing


Thanks

Adam















---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

The LastModified property applies to the recordset you have open - the one owned by you.  100% guaranteed to be the record you just added.

What do you meant by "pivot"?  That's a term used for Crosstab queries in Access.  Note that when you do rst.BookMark = rst.LastModified, you are now positioned on that last record, so you can surely .Edit it.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 22, 2016, at 2:43 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Yes it worked. The explanation leads to some more questions.


Is it possible that the LastModified isn't the record I just added, that someone else modified in the interim? Would it be better to do something like:

Db.OpenRecordset("SELECT ... FROM tblParentListBE WHERE [ParentName] = '" & Me.ClientName & "'"


Also, how can I quickly pivot the rs so that I can edit that last modified record?


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Excellent explanation.  When adding record via code in an Access database, Access sets the AutoNumber value as soon as you "dirty" the record by setting any field value.  That's why you can fetch the value before you save the record.  In SQL Server, the database doesn't set the Identity value until you save the record, so you have to find it after the save and then grab the value.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 19, 2016, at 9:19 AM, yahoo@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Did anyone explain to Adam why John's code is necessary and why it (usually) works?

With an Access backend, the DBEngine knows what's going on in the database because it "owns" it, Access assigns the Autoincrement values when you start to dirty a new record.

With a SQL Server Database (or other Back-ends) the DBEngine has no idea what is going on on the Database in the background. Some other process might be trying to pump data into the table and, while the user is busy filling out the details of the record to be added, neither Access Front-End and the SQL Server back-end know what the other one is doing. Adding a record to a SQL Table does not do anything to the back-end until it is saved so the record cannot be assigned an autoincrement value until it is actually saved, that is why the Autoincrement field remains NULL until after you save it. John's code alllows Access to *find* the record you saved and to return the values actually saved, including the Autoincrement field.

Again, Access does not know what is going to be saved in the back-end SQL Server table until it is saved. There are default values on fields which mean that the record actually saved might have some different values to those you sent in the update. There are calculated fields. There are triggers on SQL Server tables which might also mean that what gets saved in the record is different to what you saved in the update. That's where the dbSeeChanges comes in, it tells the DBEngine to go look for changes in the data in the backend and present them to the front-end.

Once you start to think about how different the connection to a SQL Server backend is compared to an Access file backend, you start to see how wonderful the magic is that the Microsoft people built into Access (specifically the Jet Engine and it's siblings.)


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Did you try the DAO code in the article I sent you?  Basically, you should be able to use the LastModified property to move back to the newly added record, then grab the Identity value:

    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly+dbSeeChanges)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    rst.Update
    rst.BookMark = rst.LastModified
    tID = rst!ParentID
    rst.Close
    Set rst = Nothing

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 5:27 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bill,

Can you help? Your advice in the link didn't work.

Track Autonumber new record Addnew or append qry

 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

This article might help:



John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 4:04 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

That gives me RT error 3021, No current record.

Anyone?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Oh, I didn't notice that you were trying to capture the Identity value.  There's a way to do that, but I don't remember.  It *might* work if you move the tID = after the Update.

Anyone else want to jump in?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 10:37 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,


I think I need to put "+ dbSeeChanges" after "dbAppendOnly"


Then, I get an error Invalid use of Null on

    tID = rst![ParentID]


rst![ParentID] evaluates to null


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

I don't see anything you need to change if you're using linked tables to SQL Server.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 8:43 PM, runuphillracing@...[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

How do I change the following code to work with a SQL back end (as opposed to an accdb BE)?


    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    tID = rst![ParentID]
    rst.Update
    rst.Close
    Set rst = Nothing


Thanks

Adam















---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

The LastModified property applies to the recordset you have open - the one owned by you.  100% guaranteed to be the record you just added.

What do you meant by "pivot"?  That's a term used for Crosstab queries in Access.  Note that when you do rst.BookMark = rst.LastModified, you are now positioned on that last record, so you can surely .Edit it.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 22, 2016, at 2:43 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Yes it worked. The explanation leads to some more questions.


Is it possible that the LastModified isn't the record I just added, that someone else modified in the interim? Would it be better to do something like:

Db.OpenRecordset("SELECT ... FROM tblParentListBE WHERE [ParentName] = '" & Me.ClientName & "'"


Also, how can I quickly pivot the rs so that I can edit that last modified record?


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Excellent explanation.  When adding record via code in an Access database, Access sets the AutoNumber value as soon as you "dirty" the record by setting any field value.  That's why you can fetch the value before you save the record.  In SQL Server, the database doesn't set the Identity value until you save the record, so you have to find it after the save and then grab the value.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 19, 2016, at 9:19 AM, yahoo@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Did anyone explain to Adam why John's code is necessary and why it (usually) works?

With an Access backend, the DBEngine knows what's going on in the database because it "owns" it, Access assigns the Autoincrement values when you start to dirty a new record.

With a SQL Server Database (or other Back-ends) the DBEngine has no idea what is going on on the Database in the background. Some other process might be trying to pump data into the table and, while the user is busy filling out the details of the record to be added, neither Access Front-End and the SQL Server back-end know what the other one is doing. Adding a record to a SQL Table does not do anything to the back-end until it is saved so the record cannot be assigned an autoincrement value until it is actually saved, that is why the Autoincrement field remains NULL until after you save it. John's code alllows Access to *find* the record you saved and to return the values actually saved, including the Autoincrement field.

Again, Access does not know what is going to be saved in the back-end SQL Server table until it is saved. There are default values on fields which mean that the record actually saved might have some different values to those you sent in the update. There are calculated fields. There are triggers on SQL Server tables which might also mean that what gets saved in the record is different to what you saved in the update. That's where the dbSeeChanges comes in, it tells the DBEngine to go look for changes in the data in the backend and present them to the front-end.

Once you start to think about how different the connection to a SQL Server backend is compared to an Access file backend, you start to see how wonderful the magic is that the Microsoft people built into Access (specifically the Jet Engine and it's siblings.)


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Did you try the DAO code in the article I sent you?  Basically, you should be able to use the LastModified property to move back to the newly added record, then grab the Identity value:

    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly+dbSeeChanges)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    rst.Update
    rst.BookMark = rst.LastModified
    tID = rst!ParentID
    rst.Close
    Set rst = Nothing

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 5:27 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bill,

Can you help? Your advice in the link didn't work.

Track Autonumber new record Addnew or append qry

 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

This article might help:



John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 4:04 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

That gives me RT error 3021, No current record.

Anyone?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Oh, I didn't notice that you were trying to capture the Identity value.  There's a way to do that, but I don't remember.  It *might* work if you move the tID = after the Update.

Anyone else want to jump in?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 10:37 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,


I think I need to put "+ dbSeeChanges" after "dbAppendOnly"


Then, I get an error Invalid use of Null on

    tID = rst![ParentID]


rst![ParentID] evaluates to null


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

I don't see anything you need to change if you're using linked tables to SQL Server.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 8:43 PM, runuphillracing@...[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

How do I change the following code to work with a SQL back end (as opposed to an accdb BE)?


    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    tID = rst![ParentID]
    rst.Update
    rst.Close
    Set rst = Nothing


Thanks

Adam













__._,_.___

Posted by: runuphillracing@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (14)

Save time and get your email on the go with the Yahoo Mail App
Get the beautifully designed, lighting fast, and easy-to-use, Yahoo Mail app today. 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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar