Can a form's query use dbSeeChanges to get new autoincremented key?

When a form was bound to a jet table I could get the new autoincrement 
pkey value in the before insert event. Is there a way to do with with a 
slq server 2005 odbc linked tables? Something like the dbSeeChanges 
param that one can use with recordset inserts?
0
sandal
12/8/2007 7:37:33 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1168 Views

Similar Articles

[PageSpeed] 51

No, you can't because of the case of an ODBC linked table to a SQL-Server, 
the new autoincremented value will be defined only after the creation of the 
new record.  Access/JET seeds the new value before creating the record - so 
you can access it even if the record doesn't exist yet in the table - but 
SQL-Server doesn't work the same way: the new value is seeded only when the 
new record is created in the table and never before.

Finally, you should post this kind of question in a newsgroup dedicated to 
ODBC linked tables such as m.p.access.odbcclientsvr as they are no ODBC 
linked table in an ADP project (the topic if this newsgroup).

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"sandal" <sandal@sandal.org> wrote in message 
news:MPG.21c3e99938acfa309896a8@msnews.microsoft.com...
> When a form was bound to a jet table I could get the new autoincrement
> pkey value in the before insert event. Is there a way to do with with a
> slq server 2005 odbc linked tables? Something like the dbSeeChanges
> param that one can use with recordset inserts? 


0
Sylvain
12/8/2007 7:58:31 AM
In a form, just *force* a disk write in your code.

old way:

somevalue = me!ID         ' grab autonumber id

New way:

if isnull(me!id) = true then
   if me.dirty = true then
      me.Dirty = false     '<-- this forces the disk write.
   end if
end if

somevalue = me!id        ' you can now grab/get record id

For general recorset code:

Dim rstRecords     As DAO.Recordset
   Dim lngNext         As Long

   Set rstRecords = CurrentDb.OpenRecordset("tblmain")
   rstRecords.AddNew

    ' code can go here to add data, or set values to the reocord...
    ' or, you could "grab" the record id at this time
    ' the id of the reocrd added..

For sql server, you MUST force the udpate of the reocordset.

Thus, you keep the above code and THEN go:

   rstRecords.Update
   rstRecords.Bookmark = rstRecords.LastModified
   lngNext = rstRecords!ID

Note how I use the LastModified to move back to the record id. DAO has a 
quirk in that when adding NEW records, an update looses the record position 
(ado does not do this). Regardless, using dao, or ado...simply force the 
update to occur, and your home free.


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
12/8/2007 7:14:33 PM
I'm just working on solving a similar problem today.  I have a 1-1 
relationship which worked fine when the be was Jet.  The form is bound to a 
query and as long as at least one field in each table is populated, Jet was 
able to handle the dual insert.  However, the code is now failing with a SQL 
server back end.  Luckily the "parent" record has only a couple of fields 
that are used to identify the class of record being inserted so it can be 
built with no user input.  Here's what I put in the Form's BeforeUpdate 
event.

    Dim rsADO As ADODB.Recordset
    Dim objconn As ADODB.Connection
    Dim strSQL As String
    Dim ConnectString As String

    If Me.NewRecord Then
       strSQL = "Set Nocount on "
       strSQL = strSQL + " Insert Producer (ProducerType, ChangedBy, 
ChangedDate)"
       strSQL = strSQL + " VALUES ('Agency', " + "'" + Environ("UserName") + 
"'" + ", GetDate()) "
       strSQL = strSQL + " select IdentityInsert = SCOPE_IDENTITY()"
       strSQL = strSQL + " set nocount off"
       ConnectString = DLookup("Connect", "qGetConnectionString")
       Set objconn = New ADODB.Connection
       objconn.ConnectionString = ConnectString
       objconn.Open
       Set rsADO = objconn.Execute(strSQL)
       Me.AgencyID = rsADO("IdentityInsert")
    End If

That leaves the form bound only to the Agency side of the relationship and 
it works fine now that I can populate the AgencyID with the value of the 
autonumber from the Producer table.  I have the same code, except for the 
ProducerType value in the Licensee form.

The pass through query sets no count on so that the procedure doesn't see 
the messages.  It inserts the row in the producer table and then retrieves 
the identity column using SCOPE_IDENTITY() rather than @@IDENTITY which will 
return an invalid result if the table has a trigger that causes an insert in 
another table.  IMHO, it is better to be safe than sorry and so even though 
I do not have such a trigger and do not foresee adding one in the future, 
someone else may and so why take the chance?

I use a query (qGetConnectionString) to look up the current connection 
string for the linked tables from the MSysObjects table.  This will allow 
the code to work regardless of whether the fe is linked to the test or 
production back end.


"sandal" <sandal@sandal.org> wrote in message 
news:MPG.21c3e99938acfa309896a8@msnews.microsoft.com...
> When a form was bound to a jet table I could get the new autoincrement
> pkey value in the before insert event. Is there a way to do with with a
> slq server 2005 odbc linked tables? Something like the dbSeeChanges
> param that one can use with recordset inserts? 


0
Pat
1/10/2008 7:01:55 PM
Reply:

Similar Artilces:

POST or GET?
(apologies if this is OT but I couldn't find an HTTP newsgroup) I am writing an MFC client application which speaks to an HTTP server. The server will send text data to the client, the client will modify and return that data to the server along with a small amount of new data. Should I use GET or POST to get the data from the server? "Richard" <richard@hello.com> wrote in message news:zWudf.14758$Cq4.8824@newsfe7-gui.ntli.net... > I am writing an MFC client application which speaks to an HTTP server. The > server will send text data to the client, the client...

Attaching Contacts to new email
Creating a new email. When contacts folder has "shared" contacts and "personal" contacts how can you set your personal contacts as the default? Example: creating a new email having never addressed the "send to" contact before, you hit the "To" button. Currently my "shared" contacts opens up but I would like my "personal" contacts page to open instead of having to drop down to "contacts" to bring up that list. Is there a solution to this? Thank you!!! On 2/26/2010 10:21 AM, assistantneedshelp wrote: >...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Print Screen Key disabled
I am trying to use the print screen key to paste and then edit some pictures, but the print screen key is disabled. Is there another way to do this or a way around this? Thanks in advance, Jill Are you trying to paste into Publisher? Can you paste into Paint? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "Jill" <anonymous@discussions.microsoft.com> wrote in message news:27dee01c4635e$6d53f170$a301280a@phx.gbl... > I am trying to use the print screen key to paste and then > edit some pictures, but the print...

New Implementation
I have following queries regarding new implementation: 1) If I ask that I have 8 outlets at remote locations & these are all garments stores, which will communicate with HQ Server. Each store is having around 1500 items. Now the question is how much time/days will it required to implement? Can anyone tell me an approximate time period? Just have an idea. 2) Which connection/line is fast/speedy for communication with HQ server from remote stores? Like DSL, Dial ap. IS VPN necessary?If NOT Y and if YES Y?? 3) Initial Steps ======= •Will start from Store Operation Template Database. •Af...

nested OR in queries
I have a table that has 20 date fields for each record. I need to select any record that has a matching date in at least one of those fields so it becomes a huge OR in a query and I have noticed that I am limited to 9 in a query. Is there a way to do this efficiently or will I need multiple queries to test all 20 date fields? If your table uses multiple date fields, the first thing you'll want to consider is coming up with a new table! In a spreadsheet, adding 20 date fields may be the only way to handle a situation, but you won't get the best use of Access' features and funct...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

Is anyone an expert with outlook that I can call on the phone?
How do I share calenders between outlook and my MSN Premier account? Assuming you are using the outlook connector, you need to set the msn account to be the default message store then outlook will use the msn calendar as the default. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this...

Certain Keys/Characters not recognised when creating a task
I've just attempted to create a task and the edit control for the subject of the task refused to accept the characters c h s t u and v. I was able to switch to other applications such as a command prompt and internet explorer and type the characters quite happily (so there is nothing wrong with the keyboard) but when I switch back to Outlook it will not recognise them. I'm using Outlook2003 as installed with Office 2003 Professional (SP1 and all other updates applied). As a last resort I closed down Outlook and restarted the program which is now accepting the keys/characters. A...

New Record
I'm using the following in the On Click of a command button. DoCmd.GoToRecord acDataForm, "frmMyForm", acNewRec Anyway I can do this without referenceing the Object (Form) name?? James James, Yes... DoCmd.GoToRecord , , acNewRec God Bless, Mark A. Sam "JamesJ" <jjy@adelphia_darwin.net> wrote in message news:er2loxDPIHA.1208@TK2MSFTNGP03.phx.gbl... > I'm using the following in the On Click of a command button. > > DoCmd.GoToRecord acDataForm, "frmMyForm", acNewRec > > Anyway I can do this without referenceing the Object (Form)...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

get a result of an sql into a field
Hi there I would like to get a result of an sql execution (ms sql server) into aq filed. example i A1 I have a ID number in A2 I would like to get the result of something like this 'select name from address where id=A1' Does this exist in Excel ? Thanks in advance Ralf Here is the sub i have written for loading an Sql Query into th worksheet. Parameters: Server Name DataBase Name SQL Command Target Sheet name Column to begin from Row to begin from ex: CALL LoadData("MyServer","MyDataBase","Select UserName fro TblNames", "QueryData"...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

How do I see when new messages without outlook running?
Without Outlook 2003 constantly running, how do I send mail or know when I have new mail? two possible answers... 1) you don't or 2) you acquired a 3rd party app to occasionally poll your pop3/imap account "Leslie Adams" <Leslie Adams@discussions.microsoft.com> wrote in message news:D37C11C7-722C-4E91-9393-735A49C11701@microsoft.com... > Without Outlook 2003 constantly running, how do I send mail or know when I > have new mail? ...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

is PHStat available and if so how do I get to it
I need help asap Tamika - > I need help asap < PHStat is Prentice Hall's statistical add-in for Excel. It's usually on the CD that's included with the textbook. For more info, try www.prenhall.com/phstat - Mike www.mikemiddleton.com ...

How do I get Excell to sort dates that range from 1800 to 1900's
I have posted a cemetery survey on Excell spreadsheet. When I try to sort the birth dates or death dates in ascending or descending order the range of 1900 dates will sort but the 1800 dates fall after the 1900 and will not sort. Why? Is this inherit with this program? The WinXL default dates start with 31 December 1899, so earlier "dates" are interpreted as Text. You can use helper columns and text functions (LEFT(),RIGHT(),MID()) to put the Year, Month and Day in separate columns, then sort. Take a look here for additional workarounds: http://j-walk.com/ss/excel/files...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Can I share entities in migration process?
The entities in CRM have only one owning user. If I want=20 who a user see a entity of another user, the entity must=20 be shared with this user.=20 Well, I want migrate entities who must be shared with some=20 users. Exists a way to do it? Thank you for pay attention and sorry my bad english. []'s Vin=EDcius Pitta Lima de Ara=FAjo ...

Can't do adjustment in analytical accounting
I am trying to change an analytical assignmnet through the Edit Analysis. When I try and change it, I receive an error stating "The Code of this Transaction Dimension cannot be adjusted". Has anyone seen this before? I have done similar changes many times, but I don't know why I can't now. Any insight would be helpful. Thanks, KJ Hi KJ What version of Gp are you running? I haveseen this happen in 8 but one of the service packs fixed it. (I think it was SP 4 or 5) Fliehigh "KJ" wrote: > I am trying to change an analytical assignmnet through the Edit >...

I cannot get Outlook to send/receive. Error says object not found
I just installed Outlook2007. I cannot get the send/receive to work. That is when the error says Object not found. If I go to accounts and properties, I run the two tests and they complete fine. What should I try next? Outlook 2007 is the only new program, all the rest are from Office 2003 IMK <IMK@discussions.microsoft.com> wrote: > I just installed Outlook2007. I cannot get the send/receive to work. > That is when the error says Object not found. If I go to accounts and > properties, I run the two tests and they complete fine. What should I > try next? Outlook 2007 is ...